Twitter Facebook Google + RSS Feed

Transacciones y modos de aislamiento en SQL Server y .NET

7
.NETSQL Server

Viernes, 03 de abril de 2009 a las 12:20hs por Dario Krapp

Comencemos por definir que es una transacción dentro del contexto de SQL Server, una transacción es un conjunto de operaciones Transact SQL que se ejecutan como un único bloque, si alguna operación falla, el bloque completo falla, si todas las modificaciones de los datos realizadas en la transacción tienen éxito, la misma se confirma y el conjunto de modificaciones se hacen permanentes en la base de datos.
Existen tres tipos de transacciones, las transacciones por procedimiento almacenado, las transacciones iniciadas por el cliente y las transacciones COM+ (de las cuales no hablaremos en este artículo).
Las transacciones por procedimiento almacenado tienen lugar en el motor de base de datos mientras que las iniciadas por el cliente tienen lugar en el código, para nuestro caso código .NET. En ambas circunstancias se emplea una estructura similar para el manejo de transacciones:

-- INICIO DE TRANSACCION
-- Comandos
-- ..
-- ...
-- ......
-- SI HUBO ALGUN ERROR CANCELACION DE TRANSACCION
-- SI NO HUBO ERRORES, CONFIRMACION DE TRANSACCION


En el motor de base de datos SQLServer 2005 o 2008, este esquema tomará la siguiente forma

BEGIN TRAN
BEGIN TRY
 Comandos
 ..
 ...
 ......
 COMMIT TRAN
END TRY
BEGIN CATCH
 ROLLBACK TRAN
END CATCH



En versiones previas de SQL Server los comandos de capturas de excepciones (BEGIN TRY, END CATCH, etc.) no se encuentran disponibles, pero existe en cambio la variable @@ERROR (también disponible en versiones posteriores) que puede consultarse luego de cada instrucción para saber si el comando se ejecutó exitosamente.
Desde .NET, empleando clases ADO.NET, el esquema sería el siguiente:

SqlConnection objMyConnection = new SqlConnection(strCnString);
SqlCommand objMyCommand = new SqlCommand(".....");
SqlTransaction objMyTransaction = null;
objMyCommand.CommandType = CommandType.Text;
objMyCommand.Connection = objMyConnection;
objMyConnection.Open();
objMyTransaction = objMyConnection.BeginTransaction(IsolationLevel.ReadUncommitted);
objMyCommand.Transaction = objMyTransaction;
try
{
 //comandos através de objMyCommand
 objMyTransaction.Commit();
}
catch
{
 objMyTransaction.Rollback();
}
finally
{
 objMyConnection.close();
}



Ambos esquemas son semánticamente idénticos. Un punto a considerar es que siempre es preferible que las transacciones se manejen desde el propio motor, ya que se evita el overhead adicional de la transferencia de datos, aunque esta posibilidad no es siempre factible.
Lo más interesante de las transacciones es la concurrencia, y una pregunta frecuente, al menos me parece que lo es, es ¿qué pasa con los datos dentro de una transacción cuando hay concurrencia sobre esos datos?, ¿qué sucede si una transacción modifica datos que aún no ha confirmado y otra transacción lee esos mismos datos en ese momento? ¿Existe una forma de aislar las transacciones para que no haya interferencia entre ellas?, ¿ese aislamiento podrá traer otras consecuencias?. Esos son los puntos que intentaremos recorrer en este articulo, contestando inmediatamente a las primeras preguntas que ya hemos formulado, y la respuesta es (como era de suponerse), si, existe una forma de aislar las transacciones, en realidad existe todo un abanico de posibilidades, conocido como “modos de aislamiento” o “isolation modes”.

Quizás la idea más intuitiva es esperar que no exista ningún tipo de interferencia de datos entre las transacciones, o sea, que cada una sea totalmente aislada de las otras, esto sería esperar que los datos modificados, (inserts, updates, deletes) sean vistos luego de la confirmación, por ejemplo si dentro de una transacción se borran mil registros, y otra transacción lee esos registros en el intervalo de tiempo existente luego del borrado, pero antes de la confirmación, la segunda transacción no debería ver esos registros, ya que si los viera la información sería inconsistente.
Para que esto funcione de esta forma, la segunda transacción debería esperar a que la primera termine de ejecutarse (obviamente si es que la segunda va a leer datos que la primera modificó) ya que es la única manera de asegurarse que los datos estuvisen siendo leídos luego del commit.

La idea no es nada nueva y es conocida como Read Commited y aunque funciona parece que el hecho de que las transacciones deban esperarse entre sí cuando hay cambios en los datos puede ocasionar problemas a medida que la concurrencia vaya incrementándose, no es difícil imaginarse que algunas transacciones recibirán un timeout cuando la concurrencia se incremente.

Una forma rápida de probar como funciona este esquema es crear desde el Visual Studio un método que abra una transacción y ejecute algún comando de modificación sobre una tabla (insert, update o delete), teniendo el cuidado de dejar un break en la línea de código del commit, al ejecutarse el código se abrirá una primera transacción de modificación de datos no confirmada en el servidor SQL Server. Puede entonces desde el Microsoft SQL Server Management Studio abrirse una segunda transacción y ejecutarse una sentencia de consulta de datos sobre la misma tabla (claro está que debe leer datos que hayan sido modificados por la primer transacción) . Veremos entonces que nuestra consulta en el Microsoft SQL Server Management Studio se queda en espera hasta que la línea commit en nuestro proyecto de Visual Studio es ejecutada, si no ocurre un timeout antes.

Transaccion Read Commited VS-MSSMS

un detalle a considerar es que, si una transacción update no modifica los valores de un registro (ya que los valores originales y los nuevos son iguales), el registro no se bloqueará.

Existe otro esquema de trabajo totalmente opuesto, su nombre es inevitablemente Read Uncommited, en este modo de aislamiento pasa todo lo contrario, las transacciones pueden leer los cambios de otras transacciones aunque las mismas aún no se hayan confirmado, pero estarán leyendo datos no confirmados, en este esquema no se producen los bloqueos que se daban en el caso anterior, si hacemos la misma prueba que en el caso previo, pero abriendo la transacción en el Microsoft SQL Server Management Studio como Read Uncommited (Reemplazar SET TRANSACTION ISOLATION LEVEL READ COMMITTED por SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED) veremos que no hay bloqueos y los datos son devueltos inmediatamente.
Quizás ya no se generen problemas de concurrencia, debido a que no hay bloqueos, pero es de esperarse que algo malo pueda suceder, y la frase anterior “pero estará leyendo datos no confirmados” es un indicio, las transacciones Read Uncommited leen los datos que aún no han sido confirmados, pero entonces que pasará si esos datos se cancelan con un Rollback, lo que sucede es que la transacción Read Uncommited leerá datos inválidos, este comportamiento de las transacciones Read Uncommited es conocido como Dirty Read literalmente “Lectura Sucia”, Los bloqueos en las transacciones Read Commited evitan justamente este problema.

Por todo lo visto hasta ahora no faltará quien pueda deducir que entonces las transacciones Read Commited son una mejor opción, ya que, si bien pueden bloquearse entre sí, no hay problemas en la lectura de datos. Quizás este modo de aislamiento sea una mejor opción en la mayoría de los casos, y de hecho es el modo de funcionamiento que traen las transacciones por defecto en SQL Server, pero las transacciones Read Commited pueden ocultar una sorpresa, lo cual no suele ser nada grato en ningún caso imaginable, al menos para mí, y las transacciones en SQL Server no son la exepción, solo debemos preguntarnos ¿Qué sucederá si mientras una transacción Read Commited está leyendo datos, una segunda transacción, supongamos también Read Commited modifica datos, digamos que hace updates, deletes e inserts y los confirma?, claro está que si bien nos aseguramos de no leer datos basura gracias a la espera del commit, de todas formas tendremos problemas, ya que algunos de los datos leídos podrían haberse eliminado, podrían también haberse agregado y modificado otros nuevos y la primera transacción ni se enteraría.
A las filas eliminadas, que aunque la primera transacción ve ya no existen, se las conoce como filas fantasma (phantom rows). Un efecto del escenario planteado es que si la primer transacción vuelve a leer los datos (luego que la segunda ya haya confirmado la transacción) aunque la consulta sea idéntica se obtendrán distintos resultados.
Debido a las filas fantasma (eliminadas) y a las filas actualizadas, no es posible garantizar que dos lecturas consecutivas devuelvan el mismo resultado, esto se lo conoce como “lecturas irrepetibles”, claro está que este problema surge porque las transacciones Read Commited le permiten a otras transacciones modificar datos que ellas ya han leído, recordemos que las transacciones Read Commited a lo sumo se bloquean ellas mismas cuando intentan leer datos modificados, pero no bloquean otras transacciones.

Para resolver estos problemas, existen otros modos de aislamientos aún más restrictivos que Read Commited. El modo de aislamiento Repeteable Read (Lecturas repetibles) permite, como su nombre da a imaginar, resolver el problema de las lecturas irrepetibles, ya que no le permite a otras transacciones eliminar o modificar filas leídas por la transacción actual hasta que la misma haya confirmado los cambios, otras transacciones si podrán agregar nuevos datos, se puede entender al Modo Repeteable Read como el modo Read Commited más la restricción a otras transacciones de eliminar o modificar los datos leídos por la primera, en este caso las transacciones Repeteable Read bloquean además otras transacciones para no permitirles modificar datos que ellas han leído.

Finalmente existe un nivel aislamiento aún más restrictivo que los anteriores conocido como Serializable, este nivel de aislamiento se comporta como el modo Repeteable Read, pero además tampoco le permite a otras transacciones agregar datos, en consecuencia cada transacción se ejecuta en serie una tras otra sin ninguna posibilidad de realización de tareas en paralelo.
Hasta el momento es claro que a un mayor nivel de aislamiento le corresponderá un mayor bloqueo de registros, en consecuencia, una menor escalabilidad.

SQL Server 2005, trajo un nuevo modo de aislamiento conocido como Snapshot, la idea de este modo de aislamiento es que una transacción pueda leer datos sin la posibilidad que se produzcan dirty reads, pero sin establecer bloqueos, o sea, una solución con la estabilidad de un Read Commited pero con los bloqueos de un Read Uncommited, para poder utilizar este modo de aislamiento, a diferencia de los anteriores hay que establecer en la base de datos lo siguiente:

ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON

Desde ese momento todas las transacciones establecidas como snapshot emplearán este nivel de aislamiento, si se desea que este modo sea el modo por defecto y ya no ReadCommited, debe ejecutarse el comando:

ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON

El modo de aislamiento Snapshot mantiene las versiones de los cambios en la base de datos tempdb para cada transacción, cuando una transacción toma datos, los mismos poseen asociado un numero de versión, para que la confirmación tenga éxito es requisito que los datos a modificar no posean un número de versión mayor al que posee la transacción. Si una transacción intenta confirmar datos con una versión de cambios anterior a la actual, la operación fallará.

Un último comentario sobre este modo de aislamiento es que emplea un esquema optimista. En un esquema optimista se espera que la concurrencia a los mismos datos desde varias transacciones sea poco frecuente, por eso no es muy problemático devolver un error cuando se dá dicha situación. En cambio en el enfoque pesimista se espera que haya mucha concurrencia a los mismos datos y por tal motivo se emplean mecanismos de bloqueo para poder evitar el problema. En las aplicaciones donde se espera que una gran cantidad de usuarios concurrentes, por ejemplo las aplicaciones web tan en auje actualmente, la idea de bloquear registros parece cada vez menos atrayente, por eso el enfoque optimista parece haber tomado popularidad, En LINQ para SQL, como tecnología de punta en acceso a datos, no solo se considera el esquema optimista, sino que además de emplearlo como modo de concurrencia por defecto, posee ya incorporadas metodologías reajustar los datos en el caso de fallas por acceso a datos concurrentes, quien esté interesado y aún no haya podido ver nada del tema, puede esperar a que escribamos algún artículo o adelantarse a buscar info de esta tecnología, un tip para los segundos : RefreshMode.KeepChanges y espero como siempre, que este artículo haya sido de utilidad.


7 comentarios »

  1. lrocker dice:

    Muy buen documento, muchas gracias por la publicación.
    Saludos,

  2. Alex_TS dice:

    Buen artículo!, lo has explicado con bastante calidad.

    Lo único que te recomendaría es que pusieras un fondo claro bajo el texto, pero el contenido mola.

    Al respecto del tema que has tratado en este artículo, tengo ciertas dudas, son las siguientes:

    1. Sin hacer uso de transacciones, considerando las operaciones sobre la base de datos a nivel de instrucción; con ADO.NET, ¿como implementarías el bloqueo pesimista?.

    2. Si por ejemplo empleo un DataAdapter (o TableAdapter) para realizar operaciones sobre la base de datos, ¿qué tipo de bloqueo se aplica por defecto?, ¿como lo puedo modificar sin emplear transacciones?.

    3. Si por el contrario realizo las operaciones con un objeto de tipo OleDbCommand, en un escenario conectado, ¿que tipo de bloqueo se aplica por defecto?, ¿como lo puedo modificar sin emplear transacciones?.

    4. ¿Como puedo implementar el bloqueo de tipo “el último gana”?

    5. Cuando se emplean transacciones, ¿los niveles de aislamiento definen el tipo de bloqueo?

    La verdad esq tengo un poco de “empanada mental” con estos estos conceptos. A ver si me lo puedes aclarar un poco.

    Gracias y saludos!

    • Dario Krapp dice:

      Muchas gracias por consultar y perdón por la demora, voy a intentar ir contestando por punto.

      1. Sin hacer uso de transacciones, considerando las operaciones sobre la base de datos a nivel de instrucción; con ADO.NET, ¿como implementarías el bloqueo pesimista?.

      Alguna vez y hace unos cuantos años tuve la oportunidad de conocer un proyecto hecho en VB6.0 con SQLServer 7.0 donde los bloqueos eran manejados completamente a mano y eran pesimistas, esto se lograba de la siguiente manera;
      Las tablas poseian un registro extra denominado UserId (Id de usuario) donde cuando un usuario tomaba un registro su Id se grababa en ese campo y se pasaba a NULL cuando el mismo era liberado. Este esquema de
      funcionamiento permitía manejar los bloqueos ya que cuando un usuario quería acceder a un registro, la aplicación podía saber si el mismo se encontraba bloqueado o no leyendo el campo UserId. Este modo de funcionamiento
      añadía a los problemas clásicos de escalabilidad que poseen los bloqueos pesimistas nuevos problemas propios de la implementación, ya que si un usuario abandonaba la aplicación inesperadamente el registro quedaba bloquedo y aunque es
      posible encontrar métodos para evitar este problema, mi punto de vista es que no es para nada recomendable utilizar bloqueos.

      2. Si por ejemplo empleo un DataAdapter (o TableAdapter) para realizar operaciones sobre la base de datos, ¿qué tipo de bloqueo se aplica por defecto?, ¿como lo puedo modificar sin emplear transacciones?.

      Un DataAdapter es un objeto que te va a permitir leer datos de un source y moverlos a memoria en un DataSet y efectuar también el paso contrario, o sea moverlos nuevamente al source luego de haberlos modificado en memoria, el método
      Fill va a transferir los datos del source a memoria y el método Update va a leer los datos de memoria y los va a transferir nuevamente al source, por lo que el funcionamiento por defecto es el comportamiento “el último gana”,
      para probarlo podés crear una tabla simple en SQL Server, te dejo un ejemplo:

      CREATE TABLE [dbo].[Datos1](
      [ID] [int] NOT NULL,
      [Numero] [int] NOT NULL,
      [Descripcion] [nvarchar](50) NOT NULL
      ) ON [PRIMARY]

      y utilizar el siguiente código

      SqlConnection SqlCn = new SqlConnection(WebConfigurationManager.ConnectionStrings[“Cn1”].ConnectionString);
      SqlCn.Open();

      //DataAdapter
      SqlDataAdapter SqlDa = new SqlDataAdapter(“SELECT * FROM Datos1”, SqlCn);
      SqlDa.AcceptChangesDuringUpdate = true;

      //Comando para actualización
      SqlCommand command = new SqlCommand(“UPDATE Datos1 SET Descripcion = @Descripcion WHERE ID = @ID”, SqlCn);

      //Parámetro Descripcion
      SqlParameter parameterDesc = command.Parameters.Add(“@Descripcion”, SqlDbType.NVarChar, 50);
      parameterDesc.SourceVersion = DataRowVersion.Current;
      parameterDesc.SourceColumn = “Descripcion”;

      //Parámetro ID
      SqlParameter parameterID = command.Parameters.Add(“@ID”, SqlDbType.Int);
      parameterID.SourceVersion = DataRowVersion.Original;
      parameterID.SourceColumn = “ID”;
      SqlDa.UpdateCommand = command;

      //Lectura de datos
      DataSet DataSetD1 = new DataSet();
      SqlDa.Fill(DataSetD1, “TableDatos1”);

      //Actualización en memoria
      foreach (DataRow DtRow in DataSetD1.Tables[0].Rows)
      DtRow[“Descripcion”] = DtRow[“Descripcion”] + “Z”;

      //Actualización en la base de datos
      SqlDa.Update(DataSetD1.Tables[“TableDatos1”]);

      Donde básicamente se modifica el valor del campo Descripcion para todos los registros, si ponés un break point en la última sentencia “SqlDa.Update(DataSetD1.Tables[“TableDatos1″]);” y antes que se ejecute vas a un query analizer
      y ejecutás lo siguiente:

      update Datos1 set Descripcion = Descripcion + ‘i’

      antes de ejecutar la sentencia SqlDa.Update(DataSetD1.Tables[“TableDatos1”]); desde VS vas a ver que todos los registros poseen una “i” al final del campo Descripción, pero si luego presionas F5 en VS y ejecutás la sentencia
      SqlDa.Update(DataSetD1.Tables[“TableDatos1”]); vas a notar que el cambio hecho desde el query analizer se ha perdido, ya que el update ha tomado los valores de memoria del DataSet (que aún no tenian el caracter i agregado) y los ha transferido al SQL,
      si haciendo la misma prueba borrás un registro desde el Query Analizer, vas a notar que se produce un error en la sentencia SqlDa.Update(DataSetD1.Tables[“TableDatos1”]); ya que no podrá actualizar registros que han sido eliminados externamente, en este caso te quedarán
      algunos registros modificados (previos al error) o otros no (los sucesivos al error).

      3. Si por el contrario realizo las operaciones con un objeto de tipo OleDbCommand, en un escenario conectado, ¿que tipo de bloqueo se aplica por defecto?, ¿como lo puedo modificar sin emplear transacciones?.

      De esto podrás pasarme un poco de código para entender como sería el caso.

      4. ¿Como puedo implementar el bloqueo de tipo “el último gana”?

      Este tipo de funcionamiento es el más sencillo de utilizar ya que podés leer la información necesaria de la base de datos, cortar la conexión, modificar la informacíon en forma desconectada y
      reenviar las modificaciones al servidor de base de datos en una nueva conexion (en una transaccion si hay varias tablas involucradas). En este esquema, si dos usuarios leen la misma información el último en grabar es quien
      preservará sus cambios.
      Fuera de los puntos anteriores, para una aplicación actual y sin utilizar frameworks de entidades y esas cosas que estan tan de moda actualmente, yo particularmente pensaría en dos enfoques posibles, el enfoque “el último gana” y el enfoque “optimista”,
      dejando las transacciones para los casos donde haya varias tablas involucradas en una misma entidad. El enfoque “el último gana” es como te comentaba previamente mientras que para el enfoque optimista, podés incluir una columna extra con un numero de versión,
      al momento de actualizar los datos leidos previamente deberías verificar que el numero de versíon actual sea el mismo que leistes previamente, en caso afirmativo podrás modificar los datos incrementando el numero de version de la columna, en caso negativo deberás tomar alguna acción ya que la información ha sido
      modificada externamente. Otra opción es en lugar de utilizar una columna version, emplear una columna datestamp para comparar contra fechas.

      5. Cuando se emplean transacciones, ¿los niveles de aislamiento definen el tipo de bloqueo?

      Si, para que el motor de base de datos pueda asegurarte el nivel de aislamiento que le estás pidiendo, el mismo deberá efectuar un tipo de bloqueo determinado, por lo que el tipo de bloqueo dependerá del nivel de aislamiento solicitado.

      La verdad esq tengo un poco de “empanada mental” con estos estos conceptos. A ver si me lo puedes aclarar un poco.

      Yo creo que hay dos cosas distintas;
      La primera es que cuando hay varias tablas involucradas a modificar en una misma operación de base de datos deben utilizarse transacciones y el nivel de aislamiento definirá que bloqueos realizará el motor de base de datos en dicha operación y eso es lo que intenta comentar el articulo.
      La segunda es qué tipo de bloqueo se debe utilizar en una aplicación para que un usuario no modifique los datos que otro esta modificando paralelamente, creo si no entendí mal que es sobre este tipo de bloqueos sobre los cuales estás interesado, y según mi punto de vista, las opciones son “el último gana” o el enfoque “optimista”.

      Espero que estas repuestas te hayan servido

      Saludos!

  3. Ctezo dice:

    muy buen documento, esta muy interesante

    Saludos

  4. Toño dice:

    Disculpa tengo unas preguntas, espero me puedas ayudar:

    1. existe algun posibilidad de que teniendo un nivel de aislamiento read uncommited o read commited no se pueda realizar un insert mientras se realiza un select a la misma tabla.

    2. puede pasar que teniendo algun nivel de aislamiento un select no termine debido a que en una transaccion de tipo insert,update o delete entre antes que terminara el select y no hace un commit.

    3. como puedo saber que nivek de aislamiento posee actualmente mi base de datos.

    Muchas gracias, ojala me puedas ayudar.

  5. Keylin dice:

    Me parece excelente tu artículo, llevo mucho tiempo tratando de averiguar sobre este tema pero resulta un poco difícil…

    Gracias por esta publicación tan buena. 😀

  6. Roger dice:

    Muy claro, muchas gracias. Así tendría que hacer Microsoft su documentación de MSDN. Me ha aclarado el tema, porque estaba mirando una aplicación en ASP.NET en la que tenía un problema de bloqueos, y veo que el nivel de isolation está bien aplicado.

Deja un comentario

Buscar