Hizkuntza

Aislamiento de instantáneas en SQL Server

El aislamiento de instantáneas mejora la concurrencia de las aplicaciones OLTP.

Descripción del aislamiento de instantáneas y la versión de fila

Una vez habilitado el aislamiento de instantáneas, se deben mantener las versiones de fila actualizadas para cada transacción. Antes de SQL Server 2019, estas versiones se almacenaban en tempdb. SQL Server 2019 presenta una nueva característica, Accelerated Database Recovery (ADR), que requiere su propio conjunto de versiones de fila. Por lo tanto, a partir de SQL Server 2019, si ADR no está habilitado, las versiones de fila se mantienen en tempdb como siempre. Si ADR está habilitado, todas las versiones de fila, relacionadas con el aislamiento de instantáneas y ADR, se mantienen en el Almacén de versiones persistentes (PVS) de ADR, que se encuentra en la base de datos de usuario de un grupo de archivos que el usuario especifica. Un número de secuencia de transacción único identifica cada transacción, y estos números únicos se registran para cada versión de fila. La transacción funciona con las versiones de fila más recientes que tienen un número de secuencia antes del número de secuencia de transacción. Las versiones de fila más recientes creadas después del inicio de la transacción son ignoradas por esta.

El término "instantánea" refleja el hecho de que todas las consultas de la transacción ven la misma versión, o instantánea, de la base de datos, en función del estado de la base de datos en el momento en que se inicia la transacción. No se adquieren bloqueos en las filas de datos subyacentes ni en las páginas de datos de una transacción de instantánea, lo que permite que otras transacciones se ejecuten sin que se bloquee una transacción anterior no completada. Las transacciones que modifican datos no bloquean las transacciones que leen datos y las transacciones que leen datos no bloquean las transacciones que escriben datos, como ocurriría normalmente en el nivel de aislamiento predeterminado READ COMMITTED de SQL Server. Este comportamiento de no bloqueo también reduce notablemente la probabilidad de que se produzcan interbloqueos en las transacciones complejas.

El aislamiento de instantánea utiliza un modelo de simultaneidad optimista. Si una transacción de instantánea intenta realizar modificaciones en los datos que han cambiado desde que se inició, la transacción se revertirá y se producirá un error. Puede evitarlo mediante UPDLOCK pistas para SELECT instrucciones que acceden a los datos que se van a modificar. Para obtener más información, consulta Sugerencias (Transact-SQL).

El aislamiento de instantáneas debe habilitarse configurando la opción ALLOW_SNAPSHOT_ISOLATION ON de la base de datos antes de usarse en transacciones. Esto activa el mecanismo para almacenar versiones de fila en la base de datos temporal (tempdb). Debe habilitar el aislamiento de instantáneas en cada base de datos que la use con la instrucción Transact-SQL ALTER DATABASE . En este sentido, el aislamiento de instantánea difiere de los niveles de aislamiento tradicionales de READ COMMITTED, REPEATABLE READ, SERIALIZABLEy READ UNCOMMITTED, que no requieren ninguna configuración. Las sentencias siguientes permiten SNAPSHOT transacciones y lecturas con control de versiones de fila READ COMMITTED:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Establecer la READ_COMMITTED_SNAPSHOT ON opción permite el acceso a filas con versiones en el nivel de aislamiento predeterminado READ COMMITTED . Si la READ_COMMITTED_SNAPSHOT opción está establecida en OFF, debe establecer explícitamente el nivel de aislamiento de instantáneas para cada sesión con el fin de acceder a las filas con versiones.

Administración de simultaneidad con niveles de aislamiento

El nivel de aislamiento en el que se ejecuta una instrucción Transact-SQL determina su bloqueo y el comportamiento de las versiones de fila. Un nivel de aislamiento tiene ámbito para toda la conexión y, una vez establecido para una conexión con la SET TRANSACTION ISOLATION LEVEL instrucción , permanece en vigor hasta que se cierra la conexión u se establece otro nivel de aislamiento. Cuando se cierra una conexión y se devuelve al pool, se conserva el nivel de aislamiento de la última instrucción SET TRANSACTION ISOLATION LEVEL. Las conexiones posteriores que reutilizan una conexión agrupada usan el nivel de aislamiento que estaba activo en el momento en que se agrupó la conexión.

Las consultas individuales emitidas en una conexión pueden contener sugerencias de bloqueo que modifiquen el aislamiento de una única instrucción o transacción, pero no afectan al nivel de aislamiento de la conexión. Los niveles de aislamiento o las sugerencias de bloqueo que se establecen en procedimientos almacenados o funciones no cambian el nivel de aislamiento de la conexión que los llama y solo están activos mientras dure el procedimiento almacenado o la llamada a la función.

En las versiones anteriores de SQL Server se admitían cuatro niveles de aislamiento definidos en el estándar SQL-92:

  • READ UNCOMMITTED es el nivel de aislamiento menos restrictivo porque ignora los bloqueos colocados por otras transacciones. Las transacciones que se ejecutan en READ UNCOMMITTED pueden leer valores de datos modificados que aún no han sido confirmados por otras transacciones; se denominan lecturas "sucias".

  • READ COMMITTEDes el nivel de aislamiento predeterminado para SQL Server. Impide las lecturas de datos sucios especificando que las instrucciones no pueden leer valores de datos modificados, pero aún no confirmados por otras transacciones. Otras transacciones pueden seguir modificando, insertando o eliminando datos entre ejecuciones de instrucciones específicas dentro de la transacción actual, lo que da lugar a lecturas no repetibles o datos "fantasma".

  • REPEATABLE READ es un nivel de aislamiento más restrictivo que READ COMMITTED. Abarca READ COMMITTED y especifica además que ninguna otra transacción puede modificar o eliminar datos leídos por la transacción actual hasta que la transacción actual se confirme. La simultaneidad es menor que para READ COMMITTED porque los bloqueos compartidos de los datos de lectura se mantienen durante la duración de la transacción en lugar de liberarse al final de cada instrucción.

  • SERIALIZABLE es el nivel de aislamiento más restrictivo, ya que bloquea todos los intervalos de claves y mantiene los bloqueos hasta que se completa la transacción. Abarca REPEATABLE READ y agrega la restricción de que otras transacciones no pueden insertar nuevas filas en intervalos leídos por la transacción hasta que se complete la transacción.

Para obtener más información, consulte la Guía de bloqueo de transacciones y versiones de fila.

Extensiones del nivel de aislamiento de instantáneas

SQL Server introdujo extensiones a los niveles de aislamiento de SQL-92 con la introducción del SNAPSHOT nivel de aislamiento y una implementación adicional de READ COMMITTED.

  • SNAPSHOT isolation especifica que los datos leídos dentro de una transacción nunca reflejarán los cambios realizados por otras transacciones simultáneas. La transacción utiliza las versiones de fila de datos que existen cuando se inicia la transacción. No se colocan bloqueos en los datos cuando se leen, por lo que SNAPSHOT las transacciones no impiden que otras transacciones escriban datos. Las transacciones que escriben datos no bloquean la lectura de datos de las transacciones de tipo SNAPSHOT. Debe habilitar el aislamiento de instantánea estableciendo la opción de base de datos ALLOW_SNAPSHOT_ISOLATION para poder usarlo.

  • La READ_COMMITTED_SNAPSHOT opción de base de datos controla cómo READ COMMITTED lee los datos. Con esta opciónOFF, el valor predeterminado de SQL Server, el Motor de base de datos usa bloqueos compartidos para proteger las lecturas. Con esta opción ON, READ COMMITTED usa versiones de fila de nivel de instrucción en lugar de bloqueos compartidos para proteger las lecturas.

Cómo funcionan el aislamiento de instantáneas y la versión de fila

Cuando se habilita el SNAPSHOT nivel de aislamiento, cada vez que se actualiza una fila, el Motor de base de datos de SQL Server almacena una copia de la fila original en tempdby agrega un número de secuencia de transacciones a la fila. A continuación se presenta la secuencia de eventos que se produce:

  • Se inicia una nueva transacción y se le asigna un número de secuencia de transacción.

  • El motor de base de datos lee una fila dentro de la transacción y recupera la versión de fila de tempdb, cuyo número de secuencia es el más cercano y menor que el número de secuencia de la transacción.

  • El Motor de base de datos comprueba si el número de secuencia de transacción no está en la lista de números de secuencia de transacción de las transacciones no confirmadas activas cuando se inició la transacción de instantánea.

  • La transacción lee la versión de la fila de tempdb que era actual en el momento de inicio de la transacción. Esta no verá las nuevas filas insertadas una vez iniciada la transacción porque esos valores de número de secuencia serán mayores que el valor del número de secuencia de transacción.

  • La transacción actual verá las filas que se eliminaron después de comenzar la transacción, ya que habrá una versión de fila en tempdb con un valor de número de secuencia inferior.

El efecto neto del aislamiento de instantánea es que la transacción ve todos los datos tal como se encontraban en el inicio de la transacción, sin respetar ni colocar bloqueos en las tablas subyacentes. Esto puede mejorar el rendimiento en situaciones en las que hay contención.

Una transacción de instantánea siempre utiliza el control de concurrencia optimista, evitando cualquier bloqueo que impediría a otras transacciones actualizar las filas. Si una transacción de instantánea intenta confirmar una actualización en una fila que se cambió una vez iniciada la transacción, la transacción se revierte y se produce un error.

Trabajo con aislamiento de instantáneas en ADO.NET

Para iniciar una transacción con aislamiento de instantáneas, habilite ALLOW_SNAPSHOT_ISOLATION para la base de datos y pase Snapshot a BeginTransaction. La READ_COMMITTED_SNAPSHOT opción de base de datos controla las lecturas con versiones de fila en el nivel de aislamiento de lectura confirmada; no selecciona el aislamiento de instantáneas para una transacción. En el fragmento siguiente se supone que connection es una instancia abierta de la clase SqlConnection.

Dim sqlTran As SqlTransaction = _
  connection.BeginTransaction(IsolationLevel.Snapshot)

SqlTransaction sqlTran =
  connection.BeginTransaction(IsolationLevel.Snapshot);

Ejemplo

En el ejemplo siguiente se muestra cómo se comportan los distintos niveles de aislamiento intentando acceder a los datos bloqueados; no está diseñado para usarse en el código de producción.

El código se conecta a la AdventureWorks base de datos de ejemplo en SQL Server y crea una tabla denominada TestSnapshot e inserta una fila de datos. El código usa la ALTER DATABASE instrucción Transact-SQL para activar el aislamiento de instantáneas para la base de datos, pero no establece la READ_COMMITTED_SNAPSHOT opción, dejando el comportamiento predeterminado READ COMMITTED de nivel de aislamiento en vigor. El código realiza las siguientes acciones:

  • La operación comienza, pero no se completa, sqlTransaction1, que usa el nivel de aislamiento SERIALIZABLE para iniciar una transacción de actualización. Esto tiene el efecto de bloquear la tabla.

  • Abre una segunda conexión e inicia una segunda transacción mediante el SNAPSHOT nivel de aislamiento para leer los datos de la TestSnapshot tabla. Dado que el aislamiento de instantáneas está habilitado, esta transacción puede leer los datos que existían antes de que se iniciara sqlTransaction1.

  • Abre una tercera conexión e inicia una transacción mediante el READ COMMITTED nivel de aislamiento para intentar leer los datos de la tabla. En este caso, el código no puede leer los datos porque no puede leer más allá de los bloqueos colocados en la tabla en la primera transacción y se agota el tiempo de espera. El mismo resultado se produciría si se usaran los niveles de aislamiento REPEATABLE READ y SERIALIZABLE porque estos niveles de aislamiento tampoco pueden leer más allá de los bloqueos colocados en la primera transacción.

  • Abre una cuarta conexión e inicia una transacción mediante el READ UNCOMMITTED nivel de aislamiento, que realiza una lectura sucia del valor no confirmado en sqlTransaction1. Este valor nunca puede existir realmente en la base de datos si la primera transacción no se confirma.

  • Revierte la primera transacción y limpia eliminando la tabla y desactivando el aislamiento de instantáneas en la base de datos.

Nota:

En los ejemplos siguientes se usa la misma cadena de conexión con la agrupación de conexiones desactivada. Si una conexión se agrupa, al restablecer su nivel de aislamiento no se restablece el nivel de aislamiento en el servidor. Como resultado, las conexiones posteriores que usan la misma conexión interna agrupada se inician con sus niveles de aislamiento establecidos en el de la conexión agrupada. Una alternativa a desactivar la agrupación de conexiones es establecer el nivel de aislamiento explícitamente en cada conexión.

// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
    // Drop the TestSnapshot table if it exists
    connection1.Open();
    SqlCommand command1 = connection1.CreateCommand();
    command1.CommandText = "IF EXISTS "
        + "(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') "
        + "DROP TABLE TestSnapshot";
    try
    {
        command1.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    // Enable Snapshot isolation
    command1.CommandText =
        "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
    command1.ExecuteNonQuery();

    // Create a table named TestSnapshot and insert one row of data
    command1.CommandText =
        "CREATE TABLE TestSnapshot (ID int primary key, valueCol int)";
    command1.ExecuteNonQuery();
    command1.CommandText =
        "INSERT INTO TestSnapshot VALUES (1,1)";
    command1.ExecuteNonQuery();

    // Begin, but do not complete, a transaction to update the data
    // with the Serializable isolation level, which locks the table
    // pending the commit or rollback of the update. The original
    // value in valueCol was 1, the proposed new value is 22.
    SqlTransaction transaction1 =
        connection1.BeginTransaction(IsolationLevel.Serializable);
    command1.Transaction = transaction1;
    command1.CommandText =
        "UPDATE TestSnapshot SET valueCol=22 WHERE ID=1";
    command1.ExecuteNonQuery();

    // Open a second connection to AdventureWorks
    using (SqlConnection connection2 = new(connectionString))
    {
        connection2.Open();
        // Initiate a second transaction to read from TestSnapshot
        // using Snapshot isolation. This will read the original
        // value of 1 since transaction1 has not yet committed.
        SqlCommand command2 = connection2.CreateCommand();
        SqlTransaction transaction2 =
            connection2.BeginTransaction(IsolationLevel.Snapshot);
        command2.Transaction = transaction2;
        command2.CommandText =
            "SELECT ID, valueCol FROM TestSnapshot";
        using (SqlDataReader reader2 = command2.ExecuteReader())
        {
            while (reader2.Read())
            {
                Console.WriteLine("Expected 1,1 Actual "
                    + reader2.GetValue(0)
                    + "," + reader2.GetValue(1));
            }
        }
        transaction2.Commit();
    }

    // Open a third connection to AdventureWorks and
    // initiate a third transaction to read from TestSnapshot
    // using ReadCommitted isolation level. This transaction
    // will not be able to view the data because of
    // the locks placed on the table in transaction1
    // and will time out after 4 seconds.
    // You would see the same behavior with the
    // RepeatableRead or Serializable isolation levels.
    using (SqlConnection connection3 = new(connectionString))
    {
        connection3.Open();
        SqlCommand command3 = connection3.CreateCommand();
        SqlTransaction transaction3 =
            connection3.BeginTransaction(IsolationLevel.ReadCommitted);
        command3.Transaction = transaction3;
        command3.CommandText =
            "SELECT ID, valueCol FROM TestSnapshot";
        command3.CommandTimeout = 4;
        try
        {
            using (SqlDataReader sqldatareader3 = command3.ExecuteReader())
            {
                while (sqldatareader3.Read())
                {
                    Console.WriteLine("You should never hit this.");
                }
            }
            transaction3.Commit();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Expected timeout expired exception: "
                + ex.Message);
            transaction3.Rollback();
        }
    }

    // Open a fourth connection to AdventureWorks and
    // initiate a fourth transaction to read from TestSnapshot
    // using the ReadUncommitted isolation level. ReadUncommitted
    // will not hit the table lock, and will allow a dirty read
    // of the proposed new value 22 for valueCol. If the first
    // transaction rolls back, this value will never actually have
    // existed in the database.
    using (SqlConnection connection4 = new(connectionString))
    {
        connection4.Open();
        SqlCommand command4 = connection4.CreateCommand();
        SqlTransaction transaction4 =
            connection4.BeginTransaction(IsolationLevel.ReadUncommitted);
        command4.Transaction = transaction4;
        command4.CommandText =
            "SELECT ID, valueCol FROM TestSnapshot";
        using (SqlDataReader reader4 = command4.ExecuteReader())
        {
            while (reader4.Read())
            {
                Console.WriteLine("Expected 1,22 Actual "
                    + reader4.GetValue(0)
                    + "," + reader4.GetValue(1));
            }
        }

        transaction4.Commit();
    }

    // Roll back the first transaction
    transaction1.Rollback();
}

// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new(connectionString))
{
    connection5.Open();
    SqlCommand command5 = connection5.CreateCommand();
    command5.CommandText = "DROP TABLE TestSnapshot";
    SqlCommand command6 = connection5.CreateCommand();
    command6.CommandText =
        "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
    try
    {
        command5.ExecuteNonQuery();
        command6.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}
Console.WriteLine("Done!");

' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()

Using connection1 As New SqlConnection(connectionString)
    ' Drop the TestSnapshot table if it exists
    connection1.Open()
    Dim command1 As SqlCommand = connection1.CreateCommand
    command1.CommandText = "IF EXISTS " & _
    "(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') " _
      & "DROP TABLE TestSnapshot"
    Try
        command1.ExecuteNonQuery()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

    ' Enable SNAPSHOT isolation
    command1.CommandText = _
    "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON"
    command1.ExecuteNonQuery()

    ' Create a table named TestSnapshot and insert one row of data
    command1.CommandText = _
    "CREATE TABLE TestSnapshot (ID int primary key, valueCol int)"
    command1.ExecuteNonQuery()
    command1.CommandText = _
      "INSERT INTO TestSnapshot VALUES (1,1)"
    command1.ExecuteNonQuery()

    ' Begin, but do not complete, a transaction to update the data
    ' with the Serializable isolation level, which locks the table
    ' pending the commit or rollback of the update. The original
    ' value in valueCol was 1, the proposed new value is 22.
    Dim transaction1 As SqlTransaction = _
      connection1.BeginTransaction(IsolationLevel.Serializable)
    command1.Transaction = transaction1
    command1.CommandText = _
     "UPDATE TestSnapshot SET valueCol=22 WHERE ID=1"
    command1.ExecuteNonQuery()

    ' Open a second connection to AdventureWorks
    Dim connection2 As SqlConnection = New SqlConnection(connectionString)
    Using connection2
        connection2.Open()

        ' Initiate a second transaction to read from TestSnapshot
        ' using Snapshot isolation. This will read the original
        ' value of 1 since transaction1 has not yet committed.
        Dim command2 As SqlCommand = connection2.CreateCommand()
        Dim transaction2 As SqlTransaction = _
          connection2.BeginTransaction(IsolationLevel.Snapshot)
        command2.Transaction = transaction2
        command2.CommandText = _
            "SELECT ID, valueCol FROM TestSnapshot"
        Using reader2 As SqlDataReader = command2.ExecuteReader()
            While reader2.Read()
                Console.WriteLine("Expected 1,1 Actual " _
                  & reader2.GetValue(0).ToString() + "," _
                  & reader2.GetValue(1).ToString())
            End While
        End Using
        transaction2.Commit()
    End Using

    ' Open a third connection to AdventureWorks and
    ' initiate a third transaction to read from TestSnapshot
    ' using the ReadCommitted isolation level. This transaction
    ' will not be able to view the data because of
    ' the locks placed on the table in transaction1
    ' and will time out after 4 seconds.
    ' You would see the same behavior with the
    ' RepeatableRead or Serializable isolation levels.
    Dim connection3 As SqlConnection = New SqlConnection(connectionString)
    Using connection3
        connection3.Open()
        Dim command3 As SqlCommand = connection3.CreateCommand()
        Dim transaction3 As SqlTransaction = _
            connection3.BeginTransaction(IsolationLevel.ReadCommitted)
        command3.Transaction = transaction3
        command3.CommandText = _
            "SELECT ID, valueCol FROM TestSnapshot"
        command3.CommandTimeout = 4

        Try
            Using reader3 As SqlDataReader = command3.ExecuteReader()
                While reader3.Read()
                    Console.WriteLine("You should never hit this.")
                End While
            End Using
            transaction3.Commit()
        Catch ex As Exception
            Console.WriteLine("Expected timeout expired exception: " _
              & ex.Message)
            transaction3.Rollback()
        End Try
    End Using

    ' Open a fourth connection to AdventureWorks and
    ' initiate a fourth transaction to read from TestSnapshot
    ' using the ReadUncommitted isolation level. ReadUncommitted
    ' will not hit the table lock, and will allow a dirty read
    ' of the proposed new value 22. If the first transaction
    ' transaction rolls back, this value will never actually have
    ' existed in the database.
    Dim connection4 As SqlConnection = New SqlConnection(connectionString)
    Using connection4
        connection4.Open()
        Dim command4 As SqlCommand = connection4.CreateCommand()
        Dim transaction4 As SqlTransaction = _
          connection4.BeginTransaction(IsolationLevel.ReadUncommitted)
        command4.Transaction = transaction4
        command4.CommandText = _
            "SELECT ID, valueCol FROM TestSnapshot"
        Using reader4 As SqlDataReader = command4.ExecuteReader()
            While reader4.Read()
                Console.WriteLine("Expected 1,22 Actual " _
                  & reader4.GetValue(0).ToString() _
                  & "," + reader4.GetValue(1).ToString())
            End While
        End Using
        transaction4.Commit()

        ' Rollback transaction1
        transaction1.Rollback()
    End Using
End Using

' CLEANUP
' Drop TestSnapshot table and set
' ALLOW_SNAPSHOT_ISOLATION OFF for AdventureWorks
Dim connection5 As New SqlConnection(connectionString)
Using connection5
    connection5.Open()
    Dim command5 As SqlCommand = connection5.CreateCommand()
    command5.CommandText = "DROP TABLE TestSnapshot"
    Dim command6 As SqlCommand = connection5.CreateCommand()
    command6.CommandText = _
   "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
    Try
        command5.ExecuteNonQuery()
        command6.ExecuteNonQuery()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try
End Using

Ejemplo

En el ejemplo siguiente se muestra el comportamiento del aislamiento de instantáneas cuando los datos están siendo modificados. El código realiza las siguientes acciones:

  • Se conecta a la SNAPSHOT base de datos de ejemplo y habilita el AdventureWorks aislamiento.

  • Crea una tabla denominada TestSnapshotUpdate e inserta tres filas de datos de ejemplo.

  • Comienza, pero no completa, sqlTransaction1 con aislamiento SNAPSHOT. En la transacción se seleccionan tres filas de datos.

  • Crea un segundo SqlConnection a AdventureWorks y crea una segunda transacción mediante el nivel de aislamiento READ COMMITTED que actualiza un valor en una de las filas seleccionadas en sqlTransaction1.

  • Confirmaciones sqlTransaction2.

  • Vuelve a sqlTransaction1 e intenta actualizar la misma fila que sqlTransaction1 ya confirmó. Se genera el error 3960 y sqlTransaction1 se revierte automáticamente. Los SqlException.Number y SqlException.Message se muestran en la ventana Consola.

  • Ejecuta código de limpieza para desactivar el aislamiento de instantáneas en AdventureWorks y eliminar la tabla TestSnapshotUpdate.

// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
    connection1.Open();
    SqlCommand command1 = connection1.CreateCommand();

    // Enable Snapshot isolation in AdventureWorks
    command1.CommandText =
        "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
    try
    {
        command1.ExecuteNonQuery();
        Console.WriteLine(
            "Snapshot Isolation turned on in AdventureWorks.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"ALLOW_SNAPSHOT_ISOLATION ON failed: {ex.Message}");
    }
    // Create a table
    command1.CommandText =
        "IF EXISTS "
        + "(SELECT * FROM sys.tables "
        + "WHERE name=N'TestSnapshotUpdate')"
        + " DROP TABLE TestSnapshotUpdate";
    command1.ExecuteNonQuery();
    command1.CommandText =
        "CREATE TABLE TestSnapshotUpdate "
        + "(ID int primary key, CharCol nvarchar(100));";
    try
    {
        command1.ExecuteNonQuery();
        Console.WriteLine("TestSnapshotUpdate table created.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"CREATE TABLE failed: {ex.Message}");
    }
    // Insert some data
    command1.CommandText =
        "INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');"
        + "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');"
        + "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');";
    try
    {
        command1.ExecuteNonQuery();
        Console.WriteLine("Data inserted TestSnapshotUpdate table.");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

    // Begin, but do not complete, a transaction
    // using the Snapshot isolation level.
    SqlTransaction transaction1 = default!;
    try
    {
        transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot);
        command1.CommandText =
            "SELECT * FROM TestSnapshotUpdate WHERE ID BETWEEN 1 AND 3";
        command1.Transaction = transaction1;
        command1.ExecuteNonQuery();
        Console.WriteLine("Snapshot transaction1 started.");

        // Open a second Connection/Transaction to update data
        // using ReadCommitted. This transaction should succeed.
        using (SqlConnection connection2 = new(connectionString))
        {
            connection2.Open();
            SqlCommand command2 = connection2.CreateCommand();
            command2.CommandText = "UPDATE TestSnapshotUpdate SET CharCol="
                + "N'New value from Connection2' WHERE ID=1";
            SqlTransaction transaction2 =
                connection2.BeginTransaction(IsolationLevel.ReadCommitted);
            command2.Transaction = transaction2;
            try
            {
                command2.ExecuteNonQuery();
                transaction2.Commit();
                Console.WriteLine(
                    "transaction2 has modified data and committed.");
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                transaction2.Rollback();
            }
            finally
            {
                transaction2.Dispose();
            }
        }

        // Now try to update a row in Connection1/Transaction1.
        // This transaction should fail because Transaction2
        // succeeded in modifying the data.
        command1.CommandText =
            "UPDATE TestSnapshotUpdate SET CharCol="
            + "N'New value from Connection1' WHERE ID=1";
        command1.Transaction = transaction1;
        command1.ExecuteNonQuery();
        transaction1.Commit();
        Console.WriteLine("You should never see this.");
    }
    catch (SqlException ex)
    {
        Console.WriteLine("Expected failure for transaction1:");
        Console.WriteLine($"  {ex.Number}: {ex.Message}");
    }
    finally
    {
        transaction1.Dispose();
    }
}

// CLEANUP:
// Turn off Snapshot isolation and delete the table
using (SqlConnection connection3 = new(connectionString))
{
    connection3.Open();
    SqlCommand command3 = connection3.CreateCommand();
    command3.CommandText =
        "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
    try
    {
        command3.ExecuteNonQuery();
        Console.WriteLine(
            "CLEANUP: Snapshot isolation turned off in AdventureWorks.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
    }
    command3.CommandText = "DROP TABLE TestSnapshotUpdate";
    try
    {
        command3.ExecuteNonQuery();
        Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
    }
}

' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()

Using connection1 As New SqlConnection(connectionString)
    ' Enable Snapshot isolation in AdventureWorks
    connection1.Open()
    Dim command1 As SqlCommand = connection1.CreateCommand
    command1.CommandText = _
   "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;"
    Try
        command1.ExecuteNonQuery()
        Console.WriteLine( _
         "Snapshot Isolation turned on in AdventureWorks.")
    Catch ex As Exception
        Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION failed: {0}", ex.Message)
    End Try

    ' Create a table
    command1.CommandText = _
      "IF EXISTS (SELECT * FROM sys.databases " _
      & "WHERE name=N'TestSnapshotUpdate') " _
      & "DROP TABLE TestSnapshotUpdate"
    command1.ExecuteNonQuery()
    command1.CommandText = _
      "CREATE TABLE TestSnapshotUpdate (ID int primary key, " _
      & "CharCol nvarchar(100));"
    Try
        command1.ExecuteNonQuery()
        Console.WriteLine("TestSnapshotUpdate table created.")
    Catch ex As Exception
        Console.WriteLine("CREATE TABLE failed: {0}", ex.Message)
    End Try

    ' Insert some data
    command1.CommandText = _
      "INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');" _
      & "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');" _
      & "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');"
    Try
        command1.ExecuteNonQuery()
        Console.WriteLine("Data inserted TestSnapshotUpdate table.")
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

    ' Begin, but do not complete, a transaction
    ' using the Snapshot isolation level
    Dim transaction1 As SqlTransaction = Nothing
    Try
        transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot)
        command1.CommandText = _
          "SELECT * FROM TestSnapshotUpdate WHERE ID " _
          & "BETWEEN 1 AND 3"
        command1.Transaction = transaction1
        command1.ExecuteNonQuery()
        Console.WriteLine("Snapshot transaction1 started.")

        ' Open a second Connection/Transaction to update data
        ' using ReadCommitted. This transaction should succeed.
        Dim connection2 As SqlConnection = New SqlConnection(connectionString)
        Using connection2
            connection2.Open()
            Dim command2 As SqlCommand = connection2.CreateCommand()
            command2.CommandText = "UPDATE TestSnapshotUpdate SET " _
              & "CharCol=N'New value from Connection2' WHERE ID=1"
            Dim transaction2 As SqlTransaction = _
              connection2.BeginTransaction(IsolationLevel.ReadCommitted)
            command2.Transaction = transaction2
            Try
                command2.ExecuteNonQuery()
                transaction2.Commit()
                Console.WriteLine( _
                  "transaction2 has modified data and committed.")
            Catch ex As SqlException
                Console.WriteLine(ex.Message)
                transaction2.Rollback()
            Finally
                transaction2.Dispose()
            End Try
        End Using

        ' Now try to update a row in Connection1/Transaction1.
        ' This transaction should fail because Transaction2
        ' succeeded in modifying the data.
        command1.CommandText = _
          "UPDATE TestSnapshotUpdate SET CharCol=" _
            & "N'New value from Connection1' WHERE ID=1"
        command1.Transaction = transaction1
        command1.ExecuteNonQuery()
        transaction1.Commit()
        Console.WriteLine("You should never see this.")

    Catch ex As SqlException
        Console.WriteLine("Expected failure for transaction1:")
        Console.WriteLine("  {0}: {1}", ex.Number, ex.Message)
    Finally
        transaction1.Dispose()
    End Try
End Using

' CLEANUP:
' Turn off Snapshot isolation and delete the table
Dim connection3 As New SqlConnection(connectionString)
Using connection3
    connection3.Open()
    Dim command3 As SqlCommand = connection3.CreateCommand()
    command3.CommandText = _
  "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
    Try
        command3.ExecuteNonQuery()
        Console.WriteLine( _
         "Snapshot isolation turned off in AdventureWorks.")
    Catch ex As Exception
        Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
    End Try

    command3.CommandText = "DROP TABLE TestSnapshotUpdate"
    Try
        command3.ExecuteNonQuery()
        Console.WriteLine("TestSnapshotUpdate table deleted.")
    Catch ex As Exception
        Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
    End Try
End Using

Uso de sugerencias de bloqueo con aislamiento de instantáneas

En el ejemplo anterior, la primera transacción selecciona los datos, y una segunda transacción actualiza los datos antes de que se pueda completar la primera, lo que provoca un conflicto de actualización cuando la primera transacción intenta actualizar la misma fila. Puede reducir la posibilidad de conflictos de actualización en transacciones de instantánea de ejecución prolongada proporcionando indicaciones de bloqueo al inicio de la transacción. La siguiente SELECT instrucción usa la UPDLOCK sugerencia para bloquear las filas seleccionadas:

SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
  WHERE PriKey BETWEEN 1 AND 3

El uso de la UPDLOCK sugerencia de bloqueo bloquea las filas que intentan actualizarlas antes de que se complete la primera transacción. Esto garantiza que las filas seleccionadas no tengan conflictos cuando se actualicen posteriormente en la transacción. Para obtener más información, consulta Sugerencias (Transact-SQL).

Si tu aplicación tiene muchos conflictos, es posible que el aislamiento de instantáneas no sea la mejor opción. Las sugerencias solo deben usarse cuando realmente se necesite. Su aplicación no debe estar diseñada para que dependa constantemente de las indicaciones de bloqueo.

Consulte también

  • Azken eguneratzearen ordua: