UNIVERSIDAD NACIONAL

PEDRO RUIZ GALLO

FACULTAD DE CIENCIAS FÍSICAS Y MATEMÁTICAS

“Escuela Profesional de Ingeniería en

Computación e Informática”

Niveles de Aislamiento

SQL Server

DOCENTE:LEIVA CASTILLO, Martín Manuel

CURSO :Conectividad

ALUMNA :Cristian Yamil Molina López (071722-J)

“UNIVERSIDAD NACIONAL PEDRO RUIZ GALLO”Ingeniería en Computación e Informática

Mayo– 2013

Nivel de aislamiento SQL Server

El nivel de aislamiento de una transacción (transaction isolation level) define el grado

en que se aísla una transacción de las modificaciones de recursos o datos realizadas

por otras transacciones. Esto dicho así, quizás no permita entender fácilmente su

significado (excepto para DBAs o programadores muy avanzados). El nivel de

aislamiento de una transacción es una característica de vital importancia en el

desarrollo de aplicaciones de base de datos, ya que afecta a los tipos y duración de

bloqueos que se producen en nuestra infraestructura de base de datos, lo cual, tiene

efectos directos en el rendimiento y tiempo de respuesta de nuestras consultas y

transacciones. Además, el nivel de aislamiento nos permite indicar cómo deseamos que

se comporte nuestro motor de base de datos frente a situaciones como las lecturas sucias

(dirty reads), las lecturas no repetibles o las lecturas fantasma, comportamientos que

pueden impactar consistentemente en la eficacia de nuestros desarrollos en entornos de

base de datos con concurrencia. Evidentemente, la elección del modo de aislamiento es

más importante cuanto mayor es la concurrencia de la base de datos (sin concurrencia,

¿a quién le interesan los modos de aislamiento?).

Para empezar es importante entender perfectamente el comportamiento por defecto de

SQL Server en las operaciones de lectura y de escritura:

En operaciones de escritura. Siempre se obtiene un bloqueo exclusivo que se

mantiene hasta que se completa la transacción.

En operaciones de lectura. El comportamiento dependerá del nivel de

aislamiento de la transacción. Por defecto, SQL Server utiliza el modo de

aislamiento basado en bloqueos READ COMMITTED, que se explica un poco

más adelante.

SQL Server 2005 ofrece seis niveles de aislamiento diferentes, que podemos agrupar en:

Niveles de Aislamiento basados en bloqueos. Se trata del comportamiento

tradicional de SQL Server (existente desde versiones anteriores de SQL Server),

Conectividad – Mayo - 2013

“UNIVERSIDAD NACIONAL PEDRO RUIZ GALLO”Ingeniería en Computación e Informática

como se ha descrito en el párrafo anterior. El funcionamiento de SQL Server se

basa en el establecimiento y gestión de los bloqueos.

Existen cuatro modos de aislamiento diferentes basados en bloqueos cubiertos

en la norma SQL-99 : 

READ UNCOMMITTED.

READ COMMITTED.

REPEATABLE READ.

SERIALIZABLE.

Niveles de Aislamiento basados en versionado de filas (row versioning -

similar a ORACLE). Desde SQL Server 2005 están disponibles dos nuevos

niveles de aislamiento: READ COMMITTED SNAPSHOT y SNAPSHOT.

Estos dos nuevos niveles de aislamiento se basan en el versionado de filas (row

versioning). Una de las principales razones de utilizar el versionado de filas (row

versioning) es minimizar los bloqueos ocurridos en la base de datos, con el fin

de obtener un mejor rendimiento (las operaciones de lecturas, sólo establecerán

bloqueos compartidos de esquema a nivel de tabla, y NO bloquearán filas ni

páginas de datos). Siempre que una transacción modifica un fila en uno de

los modos de aislamiento de versionado de filas, SQL Server 2005 utiliza

TEMPDB para almacenar una copia de la fila con el valor original y el

número de secuencia de la transacción. En caso de que múltiples

transacciones modifiquen la misma fila, se almacena en TEMPDB una cadena

de valores de la fila. Las operaciones de lectura podrán acceder a la versión

apropiada de cada fila (accediendo a TEMPDB si es necesario) para así obtener

datos congruentes sin necesidad de generar bloqueos. Para que encontremos más

familiar el uso del versionado filas (row versioning), resulta de interés saber que

SQL Server 2005 saca provecho del versionado de filas (row versioning) en el

funcionamiento de las tablas virtuales inserted y deleted de los triggers, en las

operaciones de índice con la cláusula ONLINE, etc.

Tanto para comprender bien los modos de aislamiento basados en bloqueos, como para

comprender los modos de aislamiento basados en versionado de filas, es muy

importante también comprender bien los conceptos de lecturas sucias (dirty reads),

Conectividad – Mayo - 2013

“UNIVERSIDAD NACIONAL PEDRO RUIZ GALLO”Ingeniería en Computación e Informática

lecturas no repetibles (non repeatable reads) y lecturas fantasma (phantom reads).

Estos conceptos se explican un poco más adelante.

A continuación se describen los cuatro posibles niveles de aislamiento basados en

bloqueos.

READ UNCOMMITTED puede recuperar datos modificados pero no

confirmados por otras transacciones (lecturas sucias - dirty reads). En este

nivel se pueden producir todos los efectos secundarios de simultaneidad

(lecturas sucias, lecturas no repetibles y lecturas fantasma - ej: entre dos lecturas

de un mismo registro en una transacción A, otra transacción B puede modificar

dicho registro), pero no hay bloqueos ni versiones de lectura, por lo que se

minimiza la sobrecarga. Una operación de lectura (SELECT) no establecerá

bloqueos compartidos (shared locks) sobre los datos que está leyendo, por lo

que no será bloqueada por otra transacción que tenga establecido un bloqueo

exclusivo por motivo de una operación de escritura. Este nivel de aislamiento

ofrece grandes beneficios de rendimiento, pero sólo deberemos utilizarlo en

aquellos casos en que la ocurrencia de lecturas sucias (dirty reads) no sea un

problema.

READ COMMITTED permite que entre dos lecturas de un mismo registro en

una transacción A, otra transacción B pueda modificar dicho registro,

obteniendose diferentes resultados de la misma lectura. Evita las lecturas sucias

(dirty reads), pero por el contrario, permite lecturas no repetibles. Es la opción

por defecto en SQL Server 2000 y SQL Server 2005. Con este nivel de

aislamiento, una operación de lectura (SELECT) establecerá bloqueos

compartidos (shared locks) sobre los datos que está leyendo. Sin embargo,

dichos bloqueos compartidos finalizarán junto con la propia operación de

lectura, de tal modo que entre dos lecturas cabe la posibilidad de que otra

transacción realice una operación de escritura (ej: UPDATE), en cuyo caso, la

segunda lectura obtendrá datos distintos a la primera lectura (lecturas no

repetibles).

REPEATABLE READ evita que entre dos lecturas de un mismo registro en

una transacción A, otra transacción B pueda modificar dicho registro, con el

efecto de que en la segunda lectura de la transacción A se obtuviera un dato

Conectividad – Mayo - 2013

“UNIVERSIDAD NACIONAL PEDRO RUIZ GALLO”Ingeniería en Computación e Informática

diferente. De este modo, ambas lecturas serían iguales (lecturas repetidas). Para

ello, una operación de lectura (SELECT) establecerá bloqueos compartidos

(shared locks) sobre los datos que está leyendo, y los mantendrá hasta el

final de la transacción, garantizando así que no se producelecturas no

repetibles (non repeatable reads). Mayor consistencia en la transacción,

mediante mayores recursos y bloqueos (se evitan los problemas de las lecturas

sucias y de las lecturas no repetibles, pagando el precio de necesidad de

mayores recursos). Sin embargo, este modo de aislamiento no evita las lecturas

fantasma, es decir, una transacción podría ejecutar una consulta sobre un rango

de filas (ej: 100 filas) y de forma simultánea otra transacción podría realizar un

inserción de una o varias filas sobre el mismo rango.

SERIALIZABLE garantiza que una transacción recuperará exactamente los

mismos datos cada vez que repita una operación de lectura (es decir, la misma

sentencia SELECT con la misma cláusula WHERE devolverá el mismo número

de filas, luego no se podrán insertar filas nuevas en el rango cubierto por la

WHERE, etc. - se evitarán las lecturas fantasma), aunque para ello aplicará un

nivel de bloqueo que puede afectar a los demás usuarios en los sistemas

multiusuario (realizará un bloqueo de un rango de índice - conforme a la

cláusula WHERE - y si no es posible bloqueará toda la tabla). Evita los

problemas de las lecturas sucias (dirty reads), de las lecturas no repetibles (non

repeatable reads), y de las lecturas fantasma (phantom reads).

En caso de trabajar con modos de aislamiento basados en bloqueo, es muy importante

recordar que el tiempo de espera por un bloqueo por defecto en SQL Server es

infinito. Es decir, si tenemos una transacción que está manteniendo un bloqueo sobre un

recurso (ej: una fila particular de una tabla), y mientras una transacción B intenta

acceder a dicho recurso (ej: a la misma fila), la transacción B se quedará en espera por

un tiempo indefinido (o hasta que la transacción A libere dicho recurso). ¿A que ahora

entendemos mejor porqué es modo de aislamiento READ UNCOMMITTED resulta tan

atractivo, eh? Es posible establecer un tiempo de espera de bloqueo máximo, con el fin

de evitar esperas largas en las transacciones. Para ello, es posible utilizar la

sentencia SET LOCK_TIMEOUT para especificar el tiempo máximo de espera por

bloqueo (en milisegundos). En caso se que una transacción alcance el tiempo máximo

de espera por bloqueo, se producirá un mensaje de error 1222 (Msg 1222, Level 16,

Conectividad – Mayo - 2013

“UNIVERSIDAD NACIONAL PEDRO RUIZ GALLO”Ingeniería en Computación e Informática

State 51, Line 1. Lock request time out period exceeded.). También es posible conocer

el actual tiempo de espera por bloqueo, consultando el valor de la función del

sistema @@LOCK_TIMEOUT.

A continuación se describen los dos posibles niveles de aislamiento basados en

versionado de filas (row versioning).

SNAPSHOT. Evita los problemas de las lecturas sucias (dirty reads), de las

lecturas no repetibles (non repeatable reads), y de las lecturas fantasma

(phantom reads). Sin embargo, en vez de realizar un uso intensivo de bloqueos,

su funcionamiento se basa en el almacenamiento de versiones de filas en

TEMPDB. Si una transacción empieza en un momento del tiempo determinado,

sólo será capaz de acceder a la información de la base de datos que era real en

aquel momento del tiempo. Por ello, es posible que existan múltiples versiones

de la misma fila en TEMPDB, con el fin de servir a múltiples transacciones.

Debido al impacto que tiene este modo de aislamiento sobre la base de datos del

sistema TEMPDB, sólo es recomendable en bases de datos intensivas en lecturas

o en bases de datos con grandes problemas de bloqueos. Una situación de

concurrencia particular de éste modo de aislamiento es el caso de los conflictos

de actualización (update conflicts): Si una fila es leída en una transacción, y

posteriormente como parte de la misma transacción es modificada, PERO entre

la lectura inicial y la posterior modificación ha sido alterada por otra

transacción, se produce un conflicto de actualización (update conflict), que

implica un mensaje de error 3960 (Msg 3960, Level 16, State 4 Line 1. Snapshot

isolation transaction aborted due to update conflict. You cannot use snapshot

isolation to access table 'myTable' directly or indirectly in database 'myDB' to

update, delete or insert the row that has been modified or deleted by another

transaction. Retry the transaction or change de isolation level for the

update/delete statement), y además también implica que la transacción se dehace

(Rollback).

Para activar la opción ALLOW_SNAPSHOT_ISOLATION con la siguiente instrucción:

 ALTER DATABASE MyDatabase

Conectividad – Mayo - 2013

“UNIVERSIDAD NACIONAL PEDRO RUIZ GALLO”Ingeniería en Computación e Informática

 SET ALLOW_SNAPSHOT_ISOLATION ON

READ COMMITTED SNAPSHOT. Se trata de una mezcla entre los modos

de aislamiento READ COMMITTED y SNAPSHOT. Tiene la ventaja de utilizar

el versionado de filas (row versioning), de tal modo, que las lecturas no son

bloqueadas por las escrituras (y viceversa). Sin embargo, pueden producirse

lecturas no repetibles (non repeatable reads) y lecturas fantasma (phantom

reads). Es también importante tener en cuenta que en este modo de

aislamiento, no se produce error en caso de conflictos de actualización

(update conflicts).

Ahora que ya conocemos qué son los niveles de aislamiento, ¿CÓMO SE PUEDE

ESTABLECER EL NIVEL DE AISLAMIENTO EN UNA TRANSACCIÓN?

En SQL Server 2000, y para los cuatro modos de aislamiento basados en bloqueo (Read

Uncommitted, Read Committed, Repeateable Read, Serializable), es suficiente con

utilizar la sentencia SET TRANSACTION ISOLATION LEVEL {READ

UNCOMMITTED | READ COMMITTED | REPEATEABLE READ |

SERIALIZABLE}.

En SQL Server 2005 se incluyen dos nuevas propiedades de base de datos

(configurables a través del comando ALTER DATABASE). Se trata de las opciones de

base de datos READ_COMMITTED_SNAPSHOT y

ALLOW_SNAPSHOT_ISOLATION (ambas desactivadas por defecto - revisar el

comando ALTER DATABASE de SQL Server 2005), de tal modo, que tenemos las

siguientes opciones:

Activar READ_COMMITTED_SNAPSHOT (poner ON) y utilizar

aislamiento READ COMMITED. La principal ventaja de éste método, es que

al utilizar el nivel de aislamiento READ COMMITED (que es justo el modo de

aislamiento por defecto en SQL Server), estaremos aprovechando las ventajas

del versionado de filas (row versioning). Por ello, en muchos casos,

simplemente será necesario activar la opción de base de datos

READ_COMMITTED_SNAPSHOT (poner a ON) y disfrutar del versionado de

filas (row versioning), sin necesidad de tocar el código de las transacciones.

Conectividad – Mayo - 2013

“UNIVERSIDAD NACIONAL PEDRO RUIZ GALLO”Ingeniería en Computación e Informática

Activar ALLOW_SNAPSHOT_ISOLATION (poner ON) y utilizar

aislamiento SNAPSHOT. Requiere establecer de forma explícita el nivel de

aislamiento SNAPSHOT, es decir, ejecutar SET TRANSACTION ISOLATION

LEVEL SNAPSHOT. En caso del desarrollo de una aplicación nueva, es

suficiente con tenerlo en cuenta, pero en caso de una aplicación existente, puede

implicar tener que modificar el código fuente (algo que podría llegar a ser poco

viable, según el caso).

Del mismo modo, también nos resultará muy útil ejecutar el comando DBCC

USEROPTIONS y consultar sys.databases para poder conocer en qué modo de

aislamiento estamos funcionando, y si las opciones de versionado están activadas en una

u otra base de datos.

En la mayoría de los casos, será preferible utilizar el modo de aislamiento de

lecturas confirmadas (READ COMMITED) junto con la opción de base de datos

READ_COMMITTED_SNAPSHOT, debido a que:

Utiliza menos espacio en TEMPDB, que la utilización explícita del nivel de

aislamiento SNAPSHOT (SET TRANSACTION ISOLATION LEVEL

SNAPSHOT).

Soporta el funcionamiento de transacciones distribuidas, que no es soportado

con la utilización explícita del nivel de aislamiento SNAPSHOT (SET

TRANSACTION ISOLATION LEVEL SNAPSHOT).

Es fácil de implantar, ya que al activar la opción de base de datos

READ_COMMITTED_SNAPSHOT, todas las transacciones que utilicen el

nivel de aislamiento de lecturas confirmadas (READ COMMITTED - el nivel de

aislamiento por defecto en SQL Server) se beneficiarán del aislamiento por

versionado de filas (row versioning).

No es vulnerable ante conflictos de actualizaciones, mientras que por el

contrario, con la utilización explícita del nivel de aislamiento SNAPSHOT (SET

TRANSACTION ISOLATION LEVEL SNAPSHOT) si es posible que ocurran

conflictos de actualizaciones. Es decir, si una transacción A lee una fila,

seguidamente una transacción B actualiza dicha fila, y a continuación la

transacción A actualiza la misma fila, la diferencia entre el aislamiento

READ_COMMITTED_SNAPSHOT y el SNAPSHOT, está en si se produce un

Conectividad – Mayo - 2013

“UNIVERSIDAD NACIONAL PEDRO RUIZ GALLO”Ingeniería en Computación e Informática

conflicto de actualización en la transacción A (finalizándose la transacción y

realizándose ROLLBACK) o si ambas actualizaciones (de la transacción A y de

la transacción B) se completan y confirman correctamente.

Conectividad – Mayo - 2013