Guía de versiones de fila y bloqueo de transaccionesTransaction Locking and Row Versioning Guide

Se aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database SíInstancia administrada de Azure SQLAzure SQL Managed InstanceYesInstancia administrada de Azure SQLAzure SQL Managed Instance síAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics síAlmacenamiento de datos paralelosParallel Data WarehouseyesAlmacenamiento de datos paralelosParallel Data WarehouseSe aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database SíInstancia administrada de Azure SQLAzure SQL Managed InstanceYesInstancia administrada de Azure SQLAzure SQL Managed Instance síAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics síAlmacenamiento de datos paralelosParallel Data WarehouseyesAlmacenamiento de datos paralelosParallel Data Warehouse

En cualquier base de datos, la falta de administración de las transacciones a menudo produce problemas de contención y de rendimiento en sistemas con muchos usuarios.In any database, mismanagement of transactions often leads to contention and performance problems in systems that have many users. A medida que aumenta el número de usuarios que obtienen acceso a los datos, adquiere importancia el que las aplicaciones utilicen las transacciones eficazmente.As the number of users that access the data increases, it becomes important to have applications that use transactions efficiently. En esta guía se describen los mecanismos de versiones de fila y bloqueo que el Motor de base de datos de SQL ServerSQL Server Database Engine utiliza para garantizar la integridad física de cada transacción y proporciona información acerca de cómo las aplicaciones pueden controlar las transacciones de manera eficaz.This guide describes the locking and row versioning mechanisms the Motor de base de datos de SQL ServerSQL Server Database Engine uses to ensure the physical integrity of each transaction and provides information on how applications can control transactions efficiently.

Se aplica a: SQL ServerSQL Server (desde SQL Server 2005 (9.x)SQL Server 2005 (9.x) hasta SQL Server 2019 (15.x)SQL Server 2019 (15.x), a menos que se especifique lo contrario) y Azure SQL DatabaseAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2005 (9.x)SQL Server 2005 (9.x) through SQL Server 2019 (15.x)SQL Server 2019 (15.x), unless noted otherwise) and Azure SQL DatabaseAzure SQL Database.

Conceptos básicos sobre las transaccionesTransaction Basics

Una transacción es una secuencia de operaciones realizadas como una sola unidad lógica de trabajo.A transaction is a sequence of operations performed as a single logical unit of work. Una unidad lógica de trabajo debe exhibir cuatro propiedades, conocidas como propiedades de atomicidad, coherencia, aislamiento y durabilidad (ACID), para ser calificada como transacción.A logical unit of work must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction.

AtomicidadAtomicity
Una transacción debe ser una unidad atómica de trabajo, tanto si se realizan todas sus modificaciones en los datos, como si no se realiza ninguna de ellas.A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them are performed.

CoherenciaConsistency
Cuando finaliza, una transacción debe dejar todos los datos en un estado coherente.When completed, a transaction must leave all data in a consistent state. En una base de datos relacional, se deben aplicar todas las reglas a las modificaciones de la transacción para mantener la integridad de todos los datos.In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. Todas las estructuras internas de datos, como índices de árbol b o listas doblemente vinculadas, deben estar correctas al final de la transacción.All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.

AislamientoIsolation
Las modificaciones realizadas por transacciones simultáneas se deben aislar de las modificaciones llevadas a cabo por otras transacciones simultáneas.Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. Una transacción reconoce los datos en el estado en que estaban antes de que otra transacción simultánea los modificara o después de que la segunda transacción haya concluido, pero no reconoce un estado intermedio.A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state. Esto se conoce como seriabilidad, ya que deriva en la capacidad de volver a cargar los datos iniciales y reproducir una serie de transacciones para finalizar con los datos en el mismo estado en que estaban después de realizar las transacciones originales.This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

DurabilidadDurability
Una vez concluida una transacción totalmente durable, sus efectos son permanentes en el sistema.After a fully durable transaction has completed, its effects are permanently in place in the system. Las modificaciones persisten aún en el caso de producirse un error del sistema.The modifications persist even in the event of a system failure. SQL Server 2014 (12.x)SQL Server 2014 (12.x) y versiones posteriores habilitan las transacciones durables diferidas.and later enable delayed durable transactions. Las transacciones durables diferidas se confirman antes de que la entrada de registro de transacciones se guarde en el disco.Delayed durable transactions commit before the transaction log record is persisted to disk. Para más información sobre la durabilidad de las transacciones diferidas, vea el tema Durabilidad de transacciones.For more information on delayed transaction durability see the topic Transaction Durability.

Los programadores de SQL son los responsables de iniciar y finalizar las transacciones en puntos que exijan la coherencia lógica de los datos.SQL programmers are responsible for starting and ending transactions at points that enforce the logical consistency of the data. El programador debe definir la secuencia de modificaciones de datos que los dejan en un estado coherente en relación con las reglas de negocios de la organización.The programmer must define the sequence of data modifications that leave the data in a consistent state relative to the organization's business rules. El programador incluye estas instrucciones de modificación en una sola transacción de forma que Motor de base de datos de SQL ServerSQL Server Database Engine puede hacer cumplir la integridad física de la misma.The programmer includes these modification statements in a single transaction so that the Motor de base de datos de SQL ServerSQL Server Database Engine can enforce the physical integrity of the transaction.

Es responsabilidad de un sistema de base de datos corporativo, como una instancia de Motor de base de datos de SQL ServerSQL Server Database Engine, proporcionar los mecanismos que aseguren la integridad física de cada transacción.It is the responsibility of an enterprise database system, such as an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine, to provide mechanisms ensuring the physical integrity of each transaction. Motor de base de datos de SQL ServerSQL Server Database Engine proporciona:The Motor de base de datos de SQL ServerSQL Server Database Engine provides:

  • Servicios de bloqueo que preservan el aislamiento de la transacción.Locking facilities that preserve transaction isolation.

  • Los servicios de registro garantizan la durabilidad de la transacción.Logging facilities ensure transaction durability. Para las transacciones totalmente durables, la entrada de registro se graba en el disco antes de la confirmación de las transacciones.For fully durable transactions the log record is hardened to disk before the transactions commits. Por tanto, aunque se produzca un error en el hardware del servidor, el sistema operativo o la instancia de Motor de base de datos de SQL ServerSQL Server Database Engine, la instancia usa los registros de transacciones durante el reinicio para revertir automáticamente las transacciones incompletas al punto en que se haya producido el error del sistema.Thus, even if the server hardware, operating system, or the instance of the Motor de base de datos de SQL ServerSQL Server Database Engine itself fails, the instance uses the transaction logs upon restart to automatically roll back any incomplete transactions to the point of the system failure. Las transacciones durables diferidas se confirman antes de que la entrada del registro de transacciones se grabe en el disco.Delayed durable transactions commit before the transaction log record is hardened to disk. Este tipo de transacciones se puede perder si se produce un error del sistema antes de que la entrada del registro se grabe en el disco.Such transactions may be lost if there is a system failure before the log record is hardened to disk. Para más información sobre la durabilidad de las transacciones diferidas, vea el tema Durabilidad de transacciones.For more information on delayed transaction durability see the topic Transaction Durability.

  • Características de administración de transacciones que exigen la atomicidad y coherencia de la transacción.Transaction management features that enforce transaction atomicity and consistency. Una vez iniciada una transacción, debe concluirse correctamente (confirmarse); en caso contrario, la instancia del Motor de base de datos de SQL ServerSQL Server Database Engine deshará todas las modificaciones de datos realizadas desde que se inició la transacción.After a transaction has started, it must be successfully completed (committed), or the Motor de base de datos de SQL ServerSQL Server Database Engine undoes all of the data modifications made since the transaction started. Nos referimos a esta operación como revertir una transacción porque devuelve los datos al estado en el que estaban antes de esos cambios.This operation is referred to as rolling back a transaction because it returns the data to the state it was prior to those changes.

Control de transaccionesControlling Transactions

Las aplicaciones controlan las transacciones principalmente al especificar cuándo se inicia y finaliza una transacción.Applications control transactions mainly by specifying when a transaction starts and ends. Se pueden especificar mediante instrucciones Transact-SQLTransact-SQL o funciones de la interfaz de programación de aplicaciones (API) de bases de datos.This can be specified by using either Transact-SQLTransact-SQL statements or database application programming interface (API) functions. El sistema también debe ser capaz de controlar correctamente los errores que terminan una transacción antes de que se concluya.The system must also be able to correctly handle errors that terminate a transaction before it completes. Para obtener más información, vea Transacciones, Transacciones en ODBC y Transacciones en SQL Server Native Client (OLEDB).For more information, see Transactions, Transactions in ODBC and Transactions in SQL Server Native Client (OLEDB).

De manera predeterminada, las transacciones se administran en las conexiones.By default, transactions are managed at the connection level. Cuando se inicia una transacción en una conexión, todas las instrucciones Transact-SQLTransact-SQL ejecutadas en esa conexión forman parte de la transacción hasta que ésta finaliza.When a transaction is started on a connection, all Transact-SQLTransact-SQL statements executed on that connection are part of the transaction until the transaction ends. No obstante, en una sesión de conjunto de resultados activos múltiples (MARS), una transacción de Transact-SQLTransact-SQL explícita o implícita se convierte en una transacción de lote que se administra en los lotes.However, under a multiple active result set (MARS) session, a Transact-SQLTransact-SQL explicit or implicit transaction becomes a batch-scoped transaction that is managed at the batch level. Cuando se termina el lote, si la transacción de lote no se confirma ni se revierte, SQL ServerSQL Server la revierte automáticamente.When the batch completes, if the batch-scoped transaction is not committed or rolled back, it is automatically rolled back by SQL ServerSQL Server. Para obtener más información, vea Utilizar conjuntos de resultados activos múltiples (MARS).For more information, see Using Multiple Active Result Sets (MARS).

Iniciar transaccionesStarting Transactions

Mediante funciones de la API e instrucciones Transact-SQLTransact-SQL, puede iniciar transacciones en una instancia de Motor de base de datos de SQL ServerSQL Server Database Engine como transacciones explícitas, de confirmación automática o implícitas.Using API functions and Transact-SQLTransact-SQL statements, you can start transactions in an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine as explicit, autocommit, or implicit transactions.

Transacciones explícitasExplicit Transactions
En una transacción explícita se define explícitamente tanto el inicio como el final de la transacción a través de una función API o emitiendo las instrucciones Transact-SQLTransact-SQLTransact-SQLTransact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION o ROLLBACK WORK.An explicit transaction is one in which you explicitly define both the start and end of the transaction through an API function or by issuing the Transact-SQLTransact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQLTransact-SQL statements. Cuando la transacción termina, la conexión vuelve al modo de transacción en que estaba antes de iniciar la transacción explícita, es decir, el modo implícito o el modo de confirmación automática.When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started, either implicit or autocommit mode.

En una transacción explícita se pueden utilizar todas las instrucciones Transact-SQLTransact-SQL, excepto las siguientes:You can use all Transact-SQLTransact-SQL statements in an explicit transaction, except for the following statements:

ALTER DATABASEALTER DATABASE

CREATE DATABASECREATE DATABASE

DROP FULLTEXT INDEXDROP FULLTEXT INDEX

ALTER FULLTEXT CATALOGALTER FULLTEXT CATALOG

CREATE FULLTEXT CATALOGCREATE FULLTEXT CATALOG

RECONFIGURERECONFIGURE

ALTER FULLTEXT INDEXALTER FULLTEXT INDEX

CREATE FULLTEXT INDEXCREATE FULLTEXT INDEX

RESTORERESTORE

BACKUPBACKUP

DROP DATABASEDROP DATABASE

Procedimientos almacenados de la búsqueda de texto completoFull-text system stored procedures

CREATE DATABASECREATE DATABASE

DROP FULLTEXT CATALOGDROP FULLTEXT CATALOG

sp_dboption para establecer opciones de base de datos ni utilizar ningún procedimiento del sistema que modifique la base de datos maestra en transacciones explícitas o implícitas.sp_dboption to set database options or any system procedure that modifies the master database inside explicit or implicit transactions.

Nota

UPDATE STATISTICS se puede utilizar dentro de una transacción explícita.UPDATE STATISTICS can be used inside an explicit transaction. Sin embargo, UPDATE STATISTICS se confirma independientemente de la transacción que la incluye y no se puede revertir.However, UPDATE STATISTICS commits independently of the enclosing transaction and cannot be rolled back.

Transacciones de confirmación automáticaAutocommit Transactions
El modo de confirmación automática es el modo de administración de transacciones predeterminado de Motor de base de datos de SQL ServerSQL Server Database Engine.Autocommit mode is the default transaction management mode of the Motor de base de datos de SQL ServerSQL Server Database Engine. Cada instrucción Transact-SQLTransact-SQL se confirma o se revierte cuando finaliza.Every Transact-SQLTransact-SQL statement is committed or rolled back when it completes. Si una instrucción termina correctamente, se confirma; si encuentra un error, se revierte.If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. Una conexión a una instancia de Motor de base de datos de SQL ServerSQL Server Database Engine funciona en modo de confirmación automática siempre que no se suplante el modo predeterminado mediante transacciones explícitas o implícitas.A connection to an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. El modo de confirmación automática es también el modo predeterminado para ADO, OLE DB, ODBC y DB-Library.Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

Transacciones implícitasImplicit Transactions
Cuando una conexión funciona en modo de transacciones implícitas, Motor de base de datos de SQL ServerSQL Server Database Engine inicia automáticamente una nueva transacción después de confirmar o revertir la transacción actual.When a connection is operating in implicit transaction mode, the instance of the Motor de base de datos de SQL ServerSQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. No tiene que realizar ninguna acción para delinear el inicio de una transacción, solo tiene que confirmar o revertir cada transacción.You do nothing to delineate the start of a transaction; you only commit or roll back each transaction. El modo de transacciones implícitas genera una cadena continua de transacciones.Implicit transaction mode generates a continuous chain of transactions. Establezca el modo de transacción implícita a través de una función de la API o la instrucción SET IMPLICIT_TRANSACTIONS ON de Transact-SQLTransact-SQL.Set implicit transaction mode on through either an API function or the Transact-SQLTransact-SQL SET IMPLICIT_TRANSACTIONS ON statement. Este modo también se denomina Autocommit OFF. Vea Método setAutoCommit en JDBC.This mode is also known as Autocommit OFF, see setAutoCommit Method in JDBC

Tras establecer el modo de transacciones implícitas en una conexión, la instancia de Motor de base de datos de SQL ServerSQL Server Database Engine inicia automáticamente una transacción la primera vez que ejecuta una de estas instrucciones:After implicit transaction mode has been set on for a connection, the instance of the Motor de base de datos de SQL ServerSQL Server Database Engine automatically starts a transaction when it first executes any of these statements:

ALTER TABLEALTER TABLE

FETCHFETCH

REVOKEREVOKE

CREATECREATE

GRANTGRANT

SELECTSELECT

DeleteDELETE

INSERTINSERT

TRUNCATE TABLETRUNCATE TABLE

DROPDROP

OPENOPEN

UPDATEUPDATE

  • Transacciones de ámbito de loteBatch-scoped Transactions
    Una transacción implícita o explícita de Transact-SQLTransact-SQL que se inicia en una sesión de MARS (conjuntos de resultados activos múltiples), que solo es aplicable a MARS, se convierte en una transacción de ámbito de lote.Applicable only to multiple active result sets (MARS), a Transact-SQLTransact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. Si no se confirma o revierte una transacción de ámbito de lote cuando se completa el lote, SQL ServerSQL Server la revierte automáticamente.A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL ServerSQL Server.

  • Transacciones distribuidasDistributed Transactions
    Las transacciones distribuidas abarcan dos o más servidores conocidos como administradores de recursos.Distributed transactions span two or more servers known as resource managers. La administración de la transacción debe ser coordinada entre los administradores de recursos mediante un componente de servidor llamado administrador de transacciones.The management of the transaction must be coordinated between the resource managers by a server component called a transaction manager. Cada instancia de Motor de base de datos de SQL ServerSQL Server Database Engine puede funcionar como administrador de recursos en las transacciones distribuidas que coordinan los administradores de transacciones, como el Coordinador de transacciones distribuidas de MicrosoftMicrosoft (MS DTC) u otros administradores que admitan la especificación Open Group XA del procesamiento de transacciones distribuidas.Each instance of the Motor de base de datos de SQL ServerSQL Server Database Engine can operate as a resource manager in distributed transactions coordinated by transaction managers, such as MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC), or other transaction managers that support the Open Group XA specification for distributed transaction processing. Para obtener más información, consulte la documentación de MS DTC.For more information, see the MS DTC documentation.

    Una transacción de una sola instancia de Motor de base de datos de SQL ServerSQL Server Database Engine que abarque dos o más bases de datos es, de hecho, una transacción distribuida.A transaction within a single instance of the Motor de base de datos de SQL ServerSQL Server Database Engine that spans two or more databases is actually a distributed transaction. La instancia administra la transacción distribuida internamente; para el usuario funciona como una transacción local.The instance manages the distributed transaction internally; to the user, it operates as a local transaction.

    En la aplicación, una transacción distribuida se administra de forma muy parecida a una transacción local.At the application, a distributed transaction is managed much the same as a local transaction. Al final de la transacción, la aplicación solicita que se confirme o se revierta la transacción.At the end of the transaction, the application requests the transaction to be either committed or rolled back. El administrador de transacciones debe administrar una confirmación distribuida de forma diferente para reducir al mínimo el riesgo de que, si se produce un error en la red, algunos administradores de recursos realicen confirmaciones mientras los demás revierten la transacción.A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure may result in some resource managers successfully committing while others roll back the transaction. Esto se consigue mediante la administración del proceso de confirmación en dos fases (la fase de preparación y la fase de confirmación), que se conoce como confirmación en dos fases (2PC).This is achieved by managing the commit process in two phases (the prepare phase and the commit phase), which is known as a two-phase commit (2PC).

    • Fase de preparaciónPrepare phase
      Cuando el administrador de transacciones recibe una solicitud de confirmación, envía un comando de preparación a todos los administradores de recursos implicados en la transacción.When the transaction manager receives a commit request, it sends a prepare command to all of the resource managers involved in the transaction. Cada administrador de recursos hace lo necesario para que la transacción sea duradera y todos los búferes que contienen imágenes del registro de la transacción se pasan a disco.Each resource manager then does everything required to make the transaction durable, and all buffers holding log images for the transaction are flushed to disk. A medida que cada administrador de recursos completa la fase de preparación, notifica si la preparación ha tenido éxito o no al administrador de transacciones.As each resource manager completes the prepare phase, it returns success or failure of the prepare to the transaction manager. SQL Server 2014 (12.x)SQL Server 2014 (12.x) introdujo la durabilidad diferida de transacciones.introduced delayed transaction durability. Las transacciones durables diferidas se confirman antes de que las imágenes del registro de la transacción se vacíen en el disco.Delayed durable transactions commit before log images for the transaction are flushed to disk. Para más información sobre la durabilidad de las transacciones diferidas, vea el tema Durabilidad de transacciones.For more information on delayed transaction durability see the topic Transaction Durability.

    • Fase de confirmaciónCommit phase
      Si el administrador de transacciones recibe la notificación de que todas las preparaciones son correctas por parte de todos los administradores de recursos, envía comandos de confirmación a cada administrador de recursos.If the transaction manager receives successful prepares from all of the resource managers, it sends commit commands to each resource manager. A continuación, los administradores de recursos pueden completar la confirmación.The resource managers can then complete the commit. Si todos los administradores de recursos indican que la confirmación ha sido correcta, el administrador de transacciones envía una notificación de éxito a la aplicación.If all of the resource managers report a successful commit, the transaction manager then sends a success notification to the application. Si algún administrador de recursos informó de un error al realizar la preparación, el administrador de transacciones envía un comando para revertir la transacción a cada administrador de recursos e indica a la aplicación que se ha producido un error de confirmación.If any resource manager reported a failure to prepare, the transaction manager sends a rollback command to each resource manager and indicates the failure of the commit to the application.

      Las aplicaciones de Motor de base de datos de SQL ServerSQL Server Database Engine pueden administrar transacciones distribuidas a través de Transact-SQLTransact-SQL o de la API de base de datos.Motor de base de datos de SQL ServerSQL Server Database Engine applications can manage distributed transactions either through Transact-SQLTransact-SQL or the database API. Para obtener más información, vea BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).For more information, see BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Finalizar transaccionesEnding Transactions

Puede finalizar las transacciones con una instrucción COMMIT o ROLLBACK, o mediante una función de la API correspondiente.You can end transactions with either a COMMIT or ROLLBACK statement, or through a corresponding API function.

  • COMMITCOMMIT
    Si una transacción es correcta, confírmela.If a transaction is successful, commit it. La instrucción COMMIT garantiza que todas las modificaciones de la transacción se conviertan en una parte permanente de la base de datos.A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. La instrucción COMMIT también libera recursos que utiliza la transacción como, por ejemplo, los bloqueos.A COMMIT also frees resources, such as locks, used by the transaction.

  • ROLLBACKROLLBACK
    Si se produce un error en una transacción o el usuario decide cancelar la transacción, revierta la transacción.If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. La instrucción ROLLBACK revierte todas las modificaciones realizadas en la transacción al devolver los datos al estado en que estaban al inicio de la transacción.A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. La instrucción ROLLBACK también libera los recursos que mantiene la transacción.A ROLLBACK also frees resources held by the transaction.

Nota

En conexiones habilitadas para admitir varios conjuntos de resultados activos (MARS), una transacción explícita que se haya iniciado mediante una función de la API no se puede confirmar mientras haya solicitudes de ejecución pendientes.Under connections enabled to support multiple active result sets (MARS), an explicit transaction started through an API function cannot be committed while there are pending requests for execution. Cualquier intento de confirmación de una transacción de este tipo mientras se ejecutan operaciones pendientes tendrá como resultado un error.Any attempt to commit this type of transaction while there are outstanding operations running will result in an error.

Errores al procesar la transacciónErrors During Transaction Processing

Si un error impide la terminación correcta de una transacción, SQL ServerSQL Server revierte automáticamente la transacción y libera todos los recursos que mantiene la transacción.If an error prevents the successful completion of a transaction, SQL ServerSQL Server automatically rolls back the transaction and frees all resources held by the transaction. Si se interrumpe la conexión de red del cliente con una instancia de Motor de base de datos de SQL ServerSQL Server Database Engine, las transacciones pendientes de la conexión revierten al estado anterior cuando la red notifica la interrupción a la instancia.If the client's network connection to an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. Si la aplicación cliente falla o si el equipo cliente se bloquea o se reinicia, también se interrumpe la conexión y la instancia de Motor de base de datos de SQL ServerSQL Server Database Engine revierte las conexiones pendientes cuando la red le notifica la interrupción.If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Motor de base de datos de SQL ServerSQL Server Database Engine rolls back any outstanding connections when the network notifies it of the break. Si el cliente cierra la aplicación, las transacciones pendientes se revierten.If the client logs off the application, any outstanding transactions are rolled back.

Si se produce el error de una instrucción en tiempo de ejecución (como una infracción de restricciones) en un archivo por lotes, el comportamiento predeterminado de Motor de base de datos de SQL ServerSQL Server Database Engine consiste en revertir solamente la instrucción que generó el error.If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Motor de base de datos de SQL ServerSQL Server Database Engine is to roll back only the statement that generated the error. Puede modificar este comportamiento con la instrucción SET XACT_ABORT.You can change this behavior using the SET XACT_ABORT statement. Una vez ejecutada la instrucción SET XACT_ABORT, los errores de instrucciones en tiempo de ejecución hacen que se revierta automáticamente la transacción actual.After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Los errores de compilación, como los de sintaxis, no se ven afectados por SET XACT_ABORT.Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. Para obtener más información, vea SET XACT_ABORT (Transact-SQL).For more information, see SET XACT_ABORT (Transact-SQL).

Cuando se producen errores, la acción correctora (COMMIT o ROLLBACK) debería incluirse en el código de aplicación.When errors occur, corrective action (COMMIT or ROLLBACK) should be included in application code. Una herramienta eficaz para controlar errores, incluidos los de transacciones, es la construcción TRY…CATCH de Transact-SQLTransact-SQL TRY...CATCH.One effective tool for handling errors, including those in transactions, is the Transact-SQLTransact-SQL TRY...CATCH construct. Para obtener más información y ejemplos que incluyan transacciones, vea TRY...CATCH (Transact-SQL).For more information with examples that include transactions, see TRY...CATCH (Transact-SQL). A partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x), puede usar la instrucción THROW para generar una excepción y transferir la ejecución a un bloque CATCH o a una construcción TRY...CATCH.Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), you can use the THROW statement to raise an exception and transfers execution to a CATCH block of a TRY...CATCH construct. Para obtener más información, vea THROW (Transact-SQL).For more information, see THROW (Transact-SQL).

Errores de compilación y tiempo de ejecución del modo de confirmación automáticaCompile and Run-time Errors in Autocommit mode

En el modo de confirmación automática, a veces parece que Motor de base de datos de SQL ServerSQL Server Database Engine ha revertido un proceso por lotes completo en vez de revertir solamente una instrucción SQL.In autocommit mode, it sometimes appears as if an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine has rolled back an entire batch instead of just one SQL statement. Esto sucede si se trata de un error de compilación, no en el caso de un error en tiempo de ejecución.This happens if the error encountered is a compile error, not a run-time error. Los errores de compilación impiden que Motor de base de datos de SQL ServerSQL Server Database Engine genere un plan de ejecución, por lo que no se ejecuta ninguna instrucción del proceso por lotes.A compile error prevents the Motor de base de datos de SQL ServerSQL Server Database Engine from building an execution plan, so nothing in the batch is executed. Aunque parezca que se han revertido todas las instrucciones anteriores a la que generó el error, el error impidió que se ejecutara ninguna instrucción del proceso por lotes.Although it appears that all of the statements before the one generating the error were rolled back, the error prevented anything in the batch from being executed. En el ejemplo siguiente, no se ejecutó ninguna de las instrucciones INSERT del tercer proceso por lotes debido a un error de compilación.In the following example, none of the INSERT statements in the third batch are executed because of a compile error. Parece que se han revertido las dos primeras instrucciones INSERT cuando, en realidad, nunca se ejecutaron.It appears that the first two INSERT statements are rolled back when they are never executed.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.  
GO  
SELECT * FROM TestBatch;  -- Returns no rows.  
GO  

En el ejemplo siguiente, la tercera instrucción INSERT genera un error de clave principal duplicada en tiempo de ejecución.In the following example, the third INSERT statement generates a run-time duplicate primary key error. Las dos primeras instrucciones INSERT eran correctas y se han confirmado, por lo que permanecen después de producirse el error en tiempo de ejecución.The first two INSERT statements are successful and committed, so they remain after the run-time error.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.  
GO  
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.  
GO  

Motor de base de datos de SQL ServerSQL Server Database Engine utiliza la resolución demorada de nombres, en la que no se resuelven los nombres de los objetos hasta la ejecución.The Motor de base de datos de SQL ServerSQL Server Database Engine uses deferred name resolution, in which object names are not resolved until execution time. En el ejemplo siguiente, se ejecutaron y confirmaron las dos primeras instrucciones INSERT y las dos filas permanecen en la tabla TestBatch después de que la tercera instrucción INSERT generara un error en tiempo de ejecución al hacer referencia a una tabla que no existe.In the following example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error by referring to a table that does not exist.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.  
GO  
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.  
GO  

Conceptos básicos sobre las versiones de fila y bloqueoLocking and Row Versioning Basics

El Motor de base de datos de SQL ServerSQL Server Database Engine utiliza los siguientes mecanismos para garantizar la integridad de las transacciones y mantener la coherencia de las bases de datos cuando varios usuarios obtienen acceso a los datos al mismo tiempo:The Motor de base de datos de SQL ServerSQL Server Database Engine uses the following mechanisms to ensure the integrity of transactions and maintain the consistency of databases when multiple users are accessing data at the same time:

  • BloqueoLocking

    Cada transacción solicita diferentes tipos de bloqueo en los recursos como, por ejemplo, filas, páginas o tablas de los que depende la transacción.Each transaction requests locks of different types on the resources, such as rows, pages, or tables, on which the transaction is dependent. Estos bloqueos impiden que otras transacciones puedan modificar los recursos de forma que esto provoque problemas para la transacción que solicita el bloqueo.The locks block other transactions from modifying the resources in a way that would cause problems for the transaction requesting the lock. Cada transacción libera sus bloqueos cuando ya no depende de los recursos bloqueados.Each transaction frees its locks when it no longer has a dependency on the locked resources.

  • Versiones de filaRow versioning

    Cuando un nivel de aislamiento basado en versiones de fila está habilitado, Motor de base de datos de SQL ServerSQL Server Database Engine mantiene versiones de cada fila que se ha modificado.When a row versioning-based isolation level is enabled, the Motor de base de datos de SQL ServerSQL Server Database Engine maintains versions of each row that is modified. Las aplicaciones pueden especificar que una transacción utilice las versiones de fila para ver los datos tal como eran al empezar la transacción o la consulta en lugar de proteger todas las lecturas con bloqueos.Applications can specify that a transaction use the row versions to view data as it existed at the start of the transaction or query instead of protecting all reads with locks. Mediante las versiones de fila, las probabilidades de que una operación de lectura bloquee otras transacciones se reduce notablemente.By using row versioning, the chance that a read operation will block other transactions is greatly reduced.

El bloqueo y las versiones de fila impiden a los usuarios leer los datos no confirmados así como cualquier intento de cambiar los mismos datos a la vez.Locking and row versioning prevent users from reading uncommitted data and prevent multiple users from attempting to change the same data at the same time. Sin el bloqueo o las versiones de fila, las consultas ejecutadas para esos datos podrían generar resultados inesperados al devolver datos que todavía no se han confirmado en la base de datos.Without locking or row versioning, queries executed against that data could produce unexpected results by returning data that has not yet been committed in the database.

Las aplicaciones pueden elegir los niveles de aislamiento de las transacciones, que definen el nivel de protección de la transacción frente a las modificaciones efectuadas por otras transacciones.Applications can choose transaction isolation levels, which define the level of protection for the transaction from modifications made by other transactions. Las sugerencias de nivel de tabla pueden especificarse para instrucciones Transact-SQLTransact-SQL concretas para personalizar el comportamiento con el fin de que se ajuste a los requisitos de la aplicación.Table-level hints can be specified for individual Transact-SQLTransact-SQL statements to further tailor behavior to fit the requirements of the application.

Administrar el acceso simultáneo a datosManaging Concurrent Data Access

En ocasiones, los usuarios tienen acceso a un recurso al mismo tiempo, es decir, simultáneamente.Users who access a resource at the same time are said to be accessing the resource concurrently. El acceso simultáneo a los datos requiere la utilización de mecanismos para impedir efectos negativos cuando varios usuarios intentan modificar recursos que otros usuarios están utilizando.Concurrent data access requires mechanisms to prevent adverse effects when multiple users try to modify resources that other users are actively using.

Efectos de la simultaneidadConcurrency Effects

Los usuarios que modifican datos pueden afectar a otros usuarios que leen o modifican los mismos datos a la vez.Users modifying data can affect other users who are reading or modifying the same data at the same time. Se dice que estos usuarios tienen acceso a los datos de forma simultánea.These users are said to be accessing the data concurrently. Si un sistema de almacenamiento de datos no dispone de control de simultaneidad, los usuarios se pueden encontrar con los siguientes efectos secundarios:If a data storage system has no concurrency control, users could see the following side effects:

  • Actualizaciones perdidasLost updates

    Este problema surge cuando dos o más transacciones seleccionan la misma fila y, a continuación, la actualizan de acuerdo con el valor seleccionado originalmente.Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Ninguna transacción es consciente de las otras transacciones.Each transaction is unaware of the other transactions. La última actualización sobrescribe las actualizaciones realizadas por las otras transacciones y, en consecuencia, se pierden datos.The last update overwrites updates made by the other transactions, which results in lost data.

    Por ejemplo, dos editores realizan una copia electrónica del mismo documento.For example, two editors make an electronic copy of the same document. Cada editor modifica la copia de forma independiente y después la guarda, sobrescribiendo el documento original.Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. El editor que guarda la copia modificada en último lugar sobrescribe las modificaciones que realizó el otro editor.The editor who saves the changed copy last overwrites the changes made by the other editor. Este problema se puede evitar si un editor no tiene acceso al archivo hasta que el otro finaliza y confirma la transacción.This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction.

  • Dependencia no confirmada (lectura no actualizada)Uncommitted dependency (dirty read)

    Este problema se produce cuando una transacción selecciona una fila que está siendo actualizada por otra transacción.Uncommitted dependency occurs when a second transaction selects a row that is being updated by another transaction. La segunda transacción lee datos que no han sido confirmados aún y pueden ser modificados por la transacción que está actualizando la fila.The second transaction is reading data that has not been committed yet and may be changed by the transaction updating the row.

    Por ejemplo, un editor realiza cambios en un documento electrónico.For example, an editor is making changes to an electronic document. Durante las modificaciones, un segundo editor toma una copia del documento que contiene todas las modificaciones realizadas hasta el momento y la distribuye a los destinatarios.During the changes, a second editor takes a copy of the document that includes all the changes made so far, and distributes the document to the intended audience. El primer editor decide que los cambios realizados son erróneos, así que los elimina y guarda el documento.The first editor then decides the changes made so far are wrong and removes the edits and saves the document. El documento distribuido contiene modificaciones que ya no existen y deben tratarse como si nunca hubieran existido.The distributed document contains edits that no longer exist and should be treated as if they never existed. Este problema se puede evitar si nadie lee el documento modificado hasta que el primer editor realiza el almacenamiento final de las modificaciones y confirma la transacción.This problem could be avoided if no one could read the changed document until the first editor does the final save of modifications and commits the transaction.

  • Análisis contradictorios (lectura irrepetible)Inconsistent analysis (nonrepeatable read)

    Este problema se produce cuando una transacción obtiene acceso a la misma fila varias veces y en cada ocasión lee datos diferentes.Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time. El análisis incoherente es similar a la dependencia no confirmada en tanto que una transacción está modificando los datos que está leyendo una segunda transacción.Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading. Sin embargo, en el caso del análisis incoherente, los datos que lee la segunda transacción están confirmados por la transacción que realizó el cambio.However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. Además, el análisis incoherente comprende varias lecturas (dos o más) de la misma fila y las transacciones modifican la información cada vez; de ahí el término de lectura irrepetible.Also, inconsistent analysis involves multiple reads (two or more) of the same row, and each time the information is changed by another transaction; thus, the term nonrepeatable read.

    Por ejemplo, un editor lee el mismo documento dos veces pero, entre cada lectura, el escritor vuelve a escribir el documento.For example, an editor reads the same document twice, but between each reading the writer rewrites the document. Cuando el editor lee el documento por segunda vez, éste ha cambiado.When the editor reads the document for the second time, it has changed. La lectura original no era repetible.The original read was not repeatable. Este problema se puede evitar si el escritor no cambia el documento hasta que el editor finaliza la lectura por última vez.This problem could be avoided if the writer could not change the document until the editor has finished reading it for the last time.

  • Lecturas fantasmaPhantom reads

    Una lectura fantasma es una situación que se produce cuando se ejecutan dos consultas idénticas y la recopilación de filas devuelta por la segunda consulta es diferente.A phantom read is a situation that occurs when two identical queries are executed and the collection of rows returned by the second query is different. En el siguiente ejemplo se muestra cómo se puede producir esto.The example below shows how this may occur. Suponga que las dos transacciones siguientes se están ejecutando al mismo tiempo.Assume the two transactions below are executing at the same time. Las dos instrucciones SELECT de la primera transacción pueden devolver resultados diferentes porque la instrucción INSERT de la segunda transacción cambia los datos utilizados por ambas.The two SELECT statements in the first transaction may return different results because the INSERT statement in the second transaction changes the data used by both.

    --Transaction 1  
    BEGIN TRAN;  
    SELECT ID FROM dbo.employee  
    WHERE ID > 5 and ID < 10;  
    --The INSERT statement from the second transaction occurs here.  
    SELECT ID FROM dbo.employee  
    WHERE ID > 5 and ID < 10;  
    COMMIT;  
    
    --Transaction 2  
    BEGIN TRAN;  
    INSERT INTO dbo.employee  
      (Id, Name) VALUES(6 ,'New');  
    COMMIT;   
    
  • Dobles lecturas o lecturas que faltan por causa de las actualizaciones de las filasMissing and double reads caused by row updates

    • No se encuentra una fila actualizada o una fila actualizada aparece varias vecesMissing an updated row or seeing an updated row multiple times

      Las transacciones que se ejecutan en el nivel READ UNCOMMITTED no emiten bloqueos compartidos para impedir que otras transacciones modifiquen los datos leídos por la transacción actual.Transactions that are running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. Las transacciones que se están ejecutando en el nivel de READ COMMITTED emiten bloqueos compartidos, pero los bloqueos de fila o página se liberan una vez leída la fila.Transactions that are running at the READ COMMITTED level do issue shared locks, but the row or page locks are released after the row is read. En cualquier caso, al recorrer un índice, si otro usuario cambia la columna de clave de índice de la fila mientras usted lo está leyendo, la fila podría aparecer de nuevo si el cambio en la clave movió la fila a una posición situada por delante de su punto de recorrido.In either case, when you are scanning an index, if another user changes the index key column of the row during your read, the row might appear again if the key change moved the row to a position ahead of your scan. De igual forma, la fila podría no aparecer si el cambio en la clave movió la fila a una posición en el índice que ya había sido leída.Similarly, the row might not appear if the key change moved the row to a position in the index that you had already read. Para evitarlo, utilice la sugerencia SERIALIZABLE o HOLDLOCK, o bien las versiones de fila.To avoid this, use the SERIALIZABLE or HOLDLOCK hint, or row versioning. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).For more information, see Table Hints (Transact-SQL).

    • Faltan una o más filas que no eran objeto de la actualizaciónMissing one or more rows that were not the target of update

      Cuando utilice READ UNCOMMITTED, si su consulta lee filas mediante recorrido del orden de asignación (uso de páginas IAM), podría perder filas si otra transacción está produciendo una división de página.When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if another transaction is causing a page split. Esto no puede suceder si utiliza la lectura confirmada porque la tabla se mantiene bloqueada durante la división de la página y no pasa si la tabla no tiene un índice clúster, porque las actualizaciones no producen divisiones de página.This cannot occur when you are using read committed because a table lock is held during a page split and does not happen if the table does not have a clustered index, because updates do not cause page splits.

Tipos de simultaneidadTypes of Concurrency

Cuando varias personas intentan modificar los datos de una base de datos al mismo tiempo, debe implementarse un sistema de controles de forma que las modificaciones realizadas por una persona no afecten negativamente a las de otra.When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. Esto se denomina control de simultaneidad.This is called concurrency control.

La teoría del control de simultaneidad tiene dos clasificaciones para los métodos que establecen dicho control:Concurrency control theory has two classifications for the methods of instituting concurrency control:

  • Control de simultaneidad pesimistaPessimistic concurrency control

    Un sistema de bloqueos impide que los usuarios modifiquen los datos de forma que afecte a otros usuarios.A system of locks prevents users from modifying data in a way that affects other users. Cuando un usuario lleve a cabo una acción que da lugar a que se aplique un bloqueo, los demás usuarios no podrán realizar acciones que crearían conflictos con el bloqueo hasta que el propietario lo libere.After a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the owner releases it. Esto se denomina control pesimista porque se utiliza principalmente en entornos donde hay muchos conflictos por la obtención de datos, y en los que el coste de la protección de datos con bloqueos es menor que el de revertir las transacciones si se producen conflictos de simultaneidad.This is called pessimistic control because it is mainly used in environments where there is high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.

  • Control de simultaneidad optimistaOptimistic concurrency control

    En el control de simultaneidad optimista, los usuarios no bloquean los datos cuando los leen.In optimistic concurrency control, users do not lock data when they read it. Cuando un usuario realiza una actualización de datos, el sistema comprueba si otro usuario ha cambiado los datos después de la lectura.When a user updates data, the system checks to see if another user changed the data after it was read. Si otro usuario actualizó los datos, se produce un error.If another user updated the data, an error is raised. Normalmente, el usuario que recibe el error revierte la transacción y comienza de nuevo.Typically, the user receiving the error rolls back the transaction and starts over. Este tipo de control se denomina optimista porque se utiliza principalmente en entornos donde hay pocos problemas de contención por la obtención de datos y en los que el coste de revertir ocasionalmente una transacción es menor que el de bloquear los datos cuando se leen.This is called optimistic because it is mainly used in environments where there is low contention for data, and where the cost of occasionally rolling back a transaction is lower than the cost of locking data when read.

SQL ServerSQL Server permite el uso de una serie de controles de simultaneidad.supports a range of concurrency control. Los usuarios especifican el tipo de control de simultaneidad seleccionando niveles de aislamiento de transacción para las conexiones u opciones de simultaneidad en cursores.Users specify the type of concurrency control by selecting transaction isolation levels for connections or concurrency options on cursors. Estos atributos se pueden definir mediante instrucciones Transact-SQLTransact-SQL o bien mediante las propiedades y los atributos de interfaces de programación de aplicaciones (API) de bases de datos como ADO, ADO.NET, OLE DB y ODBC.These attributes can be defined using Transact-SQLTransact-SQL statements, or through the properties and attributes of database application programming interfaces (APIs) such as ADO, ADO.NET, OLE DB, and ODBC.

Niveles de aislamiento en Motor de base de datos de SQL ServerSQL Server Database EngineIsolation Levels in the Motor de base de datos de SQL ServerSQL Server Database Engine

Las transacciones especifican un nivel de aislamiento que define el grado en que se debe aislar una transacción de las modificaciones de recursos o datos realizadas por otras transacciones.Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Los niveles de aislamiento se describen en cuanto a los efectos secundarios de la simultaneidad que se permiten, como las lecturas desfasadas o fantasma.Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

Control de los niveles de aislamiento de transacción:Transaction isolation levels control:

  • Controla si se realizan bloqueos cuando se leen los datos y qué tipos de bloqueos se solicitan.Whether locks are taken when data is read, and what type of locks are requested.
  • Duración de los bloqueos de lectura.How long the read locks are held.
  • Si una operación de lectura que hace referencia a filas modificadas por otra transacción:Whether a read operation referencing rows modified by another transaction:
    • Se bloquea hasta que se libera el bloqueo exclusivo de la fila.Blocks until the exclusive lock on the row is freed.
    • Recupera la versión confirmada de la fila que existía en el momento en el que empezó la instrucción o la transacción.Retrieves the committed version of the row that existed at the time the statement or transaction started.
    • Lee la modificación de los datos no confirmados.Reads the uncommitted data modification.

Importante

La elección de un nivel de aislamiento de transacción no afecta a los bloqueos adquiridos para proteger la modificación de datos.Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. Siempre se obtiene un bloqueo exclusivo en los datos modificados de una transacción, bloqueo que se mantiene hasta que se completa la transacción, independientemente del nivel de aislamiento seleccionado para la misma.A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. En el caso de las operaciones de lectura, los niveles de aislamiento de transacción definen básicamente el nivel de protección contra los efectos de las modificaciones que realizan otras transacciones.For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

Un nivel de aislamiento menor significa que los usuarios tienen un mayor acceso a los datos simultáneamente, con lo que aumentan los efectos de simultaneidad que pueden experimentar, como las lecturas desfasadas o la pérdida de actualizaciones.A lower isolation level increases the ability of many users to access data at the same time, but increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter. Por el contrario, un nivel de aislamiento mayor reduce los tipos de efectos de simultaneidad, pero requiere más recursos del sistema y aumenta las posibilidades de que una transacción bloquee otra.Conversely, a higher isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another. El nivel de aislamiento apropiado depende del equilibrio entre los requisitos de integridad de los datos de la aplicación y la sobrecarga de cada nivel de aislamiento.Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level. El nivel de aislamiento superior, que es serializable, garantiza que una transacción recuperará exactamente los mismos datos cada vez que repita una operación de lectura, aunque para ello aplicará un nivel de bloqueo que puede afectar a los demás usuarios en los sistemas multiusuario.The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multi-user systems. El nivel de aislamiento inferior, de lectura sin confirmar, puede recuperar datos modificados pero no confirmados por otras transacciones.The lowest isolation level, read uncommitted, may retrieve data that has been modified but not committed by other transactions. En este nivel se pueden producir todos los efectos secundarios de simultaneidad, pero no hay bloqueos ni versiones de lectura, por lo que se minimiza la sobrecarga.All of the concurrency side effects can happen in read uncommitted, but there is no read locking or versioning, so overhead is minimized.

Niveles de aislamiento del motor de base de datosDatabase Engine Isolation Levels

El estándar ISO define los niveles de aislamiento siguientes, todos ellos compatibles con el Motor de base de datos de SQL ServerSQL Server Database Engine:The ISO standard defines the following isolation levels, all of which are supported by the Motor de base de datos de SQL ServerSQL Server Database Engine:

Nivel de aislamientoIsolation Level DefiniciónDefinition
Lectura pendiente de confirmaciónRead uncommitted El nivel más bajo de aislamiento donde se aíslan las transacciones lo suficiente como para garantizar que no se leen datos físicamente dañados.The lowest isolation level where transactions are isolated only enough to ensure that physically corrupt data is not read. En este nivel, se permiten las lecturas no actualizadas por lo que es posible que una transacción vea cambios que no se han confirmado aún efectuados por otras transacciones.In this level, dirty reads are allowed, so one transaction may see not-yet-committed changes made by other transactions.
Lectura confirmadaRead committed Permite que una transacción lea los datos previamente leídos (no modificados) por otra transacción, sin tener que esperar a que la primera transacción finalice.Allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. El Motor de base de datos de SQL ServerSQL Server Database Engine mantiene los bloqueos de lectura (adquiridos en datos seleccionados) hasta el final de la transacción, pero los bloqueos de lectura se liberan tan pronto se efectúa la operación SELECT.The Motor de base de datos de SQL ServerSQL Server Database Engine keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed. Este es el nivel predeterminado del Motor de base de datos de SQL ServerSQL Server Database Engine.This is the Motor de base de datos de SQL ServerSQL Server Database Engine default level.
Lectura repetibleRepeatable read El Motor de base de datos de SQL ServerSQL Server Database Engine mantiene los bloqueos de lectura y escritura adquiridos en datos seleccionados hasta el final de la transacción.The Motor de base de datos de SQL ServerSQL Server Database Engine keeps read and write locks that are acquired on selected data until the end of the transaction. Sin embargo, puesto que los bloqueos de rangos no están administrados, pueden darse lecturas fantasma.However, because range-locks are not managed, phantom reads can occur.
SerializableSerializable El nivel más alto, en el que se aíslan completamente las transacciones entre sí.The highest level where transactions are completely isolated from one another. El Motor de base de datos de SQL ServerSQL Server Database Engine mantiene los bloqueos de lectura y escritura adquiridos en datos seleccionados y que se liberarán al final de la transacción.The Motor de base de datos de SQL ServerSQL Server Database Engine keeps read and write locks acquired on selected data to be released at the end of the transaction. Los bloqueos de rangos se adquieren cuando una operación SELECT utiliza una cláusula WHERE con rango, especialmente para evitar lecturas fantasma.Range-locks are acquired when a SELECT operation uses a ranged WHERE clause, especially to avoid phantom reads.

Nota: Al solicitar el nivel de aislamiento serializable se puede producir un error en las operaciones DDL y las transacciones de tablas replicadas.Note: DDL operations and transactions on replicated tables may fail when serializable isolation level is requested. La causa es que en las consultas de replicación se utilizan sugerencias que pueden ser incompatibles con el nivel de aislamiento serializable.This is because replication queries use hints that may be incompatible with serializable isolation level.

SQL ServerSQL Server también admite dos niveles de aislamiento de transacción adicionales que utilizan versiones de fila.also supports two additional transaction isolation levels that use row versioning. Uno es una implementación de aislamiento de lectura confirmada y el otro un nivel de aislamiento de transacción, la instantánea.One is an implementation of read committed isolation, and one is a transaction isolation level, snapshot.

Nivel de aislamiento de versiones de filaRow Versioning Isolation Level DefiniciónDefinition
Instantánea de lectura confirmada (RCSI)Read Committed Snapshot (RCSI) Cuando el valor de la opción de base de datos READ_COMMITTED_SNAPSHOT es ON, el aislamiento de lectura confirmada utiliza las versiones de fila para proporcionar una coherencia de lectura en las instrucciones.When the READ_COMMITTED_SNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. Las operaciones de lectura solo requieren bloqueos de tablas SCH-S, pero no bloqueos de páginas ni filas.Read operations require only SCH-S table level locks and no page or row locks. Es decir, Motor de base de datos de SQL ServerSQL Server Database Engine utiliza versiones de fila para presentar a cada instrucción una instantánea coherente, desde el punto de vista transaccional, de los datos tal como se encontraban al comenzar la instrucción.That is, the Motor de base de datos de SQL ServerSQL Server Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. No se emplean bloqueos para impedir que otras transacciones actualicen los datos.Locks are not used to protect the data from updates by other transactions. Una función definida por el usuario puede devolver datos confirmados después del inicio de la instrucción que contiene que la UDF.A user-defined function can return data that was committed after the time the statement containing the UDF began.

Cuando la opción de base de datos READ_COMMITTED_SNAPSHOT está establecida en OFF, que es el valor de configuración predeterminado, el aislamiento confirmado de lectura utiliza bloqueos compartidos para evitar que otras transacciones modifiquen filas mientras la transacción actual está ejecutando una operación de lectura.When the READ_COMMITTED_SNAPSHOT database option is set OFF, which is the default setting, read committed isolation uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. Los bloqueos compartidos impiden también que la instrucción lea las filas modificadas por otras transacciones hasta que la otra transacción haya finalizado.The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. Ambas implementaciones cumplen la definición ISO del aislamiento de lectura confirmada.Both implementations meet the ISO definition of read committed isolation.
InstantáneaSnapshot El nivel de aislamiento de instantánea utiliza las versiones de fila para proporcionar una coherencia de lectura en las transacciones.The snapshot isolation level uses row versioning to provide transaction-level read consistency. No se adquiere ningún bloqueo de páginas ni filas en las operaciones de lectura, solo los bloqueos de tabla SCH-S.Read operations acquire no page or row locks; only SCH-S table locks are acquired. Cuando se leen filas modificadas por otras transacciones, se recupera la versión de la fila que existía cuando empezó la transacción.When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started. El aislamiento de instantánea solo se puede utilizar en una base de datos cuando la opción de base de datos ALLOW_SNAPSHOT_ISOLATION está establecida en ON.You can only use Snapshot isolation against a database when the ALLOW_SNAPSHOT_ISOLATION database option is set ON. De forma predeterminada, el valor de esta opción es OFF para las bases de datos de usuarios.By default, this option is set OFF for user databases.

Nota: SQL ServerSQL Server no permite controlar las versiones de los metadatos.Note: SQL ServerSQL Server does not support versioning of metadata. Por ello, hay restricciones en qué operaciones de DDL se puede realizar en una transacción explícita que se está ejecutando bajo el aislamiento de instantánea.For this reason, there are restrictions on what DDL operations can be performed in an explicit transaction that is running under snapshot isolation. No se permiten las siguientes instrucciones DDL en el aislamiento de instantánea después de una instrucción BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME o cualquier instrucción de DDL de Common Language Runtime (CLR).The following DDL statements are not permitted under snapshot isolation after a BEGIN TRANSACTION statement: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or any common language runtime (CLR) DDL statement. Estas instrucciones se permiten cuando usa el aislamiento de la instantánea en transacciones implícitas.These statements are permitted when you are using snapshot isolation within implicit transactions. Una transacción implícita, por definición, es una instrucción única que permite aplicar la semántica del aislamiento de instantánea, incluso con instrucciones de DDL.An implicit transaction, by definition, is a single statement that makes it possible to enforce the semantics of snapshot isolation, even with DDL statements. Las infracciones de este principio pueden producir el error 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.Violations of this principle can cause error 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

En la tabla siguiente se muestran los efectos secundarios de la simultaneidad habilitados por los distintos niveles de aislamiento.The following table shows the concurrency side effects enabled by the different isolation levels.

Nivel de aislamientoIsolation level Lectura desfasadaDirty read Lectura no repetibleNonrepeatable read FantasmaPhantom
Lectura pendiente de confirmaciónRead uncommitted Yes Yes Yes
Lectura confirmadaRead committed NoNo Yes Yes
Lectura repetibleRepeatable read NoNo NoNo Yes
InstantáneaSnapshot NoNo NoNo NoNo
SerializableSerializable NoNo NoNo NoNo

Para obtener más información sobre los tipos de bloqueo específicos o las versiones de fila que controlan cada nivel de aislamiento de transacción, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).For more information about the specific types of locking or row versioning controlled by each transaction isolation level, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Se pueden establecer los niveles de aislamiento de transacción con Transact-SQLTransact-SQL o mediante una API de bases de datos.Transaction isolation levels can be set using Transact-SQLTransact-SQL or through a database API.

Transact-SQLTransact-SQL
Los scripts Transact-SQLTransact-SQL usan la instrucción SET TRANSACTION ISOLATION LEVEL.Transact-SQLTransact-SQL scripts use the SET TRANSACTION ISOLATION LEVEL statement.

ADOADO
Las aplicaciones ADO establecen la propiedad IsolationLevel del objeto Connection como adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead o adXactReadSerializable.ADO applications set the IsolationLevel property of the Connection object to adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, or adXactReadSerializable.

ADO.NETADO.NET
Las aplicaciones ADO.NET que usan el espacio de nombres administrado por System.Data.SqlClient pueden llamar al método SqlConnection.BeginTransaction y establecer la opción IsolationLevel en Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable y Snapshot.ADO.NET applications using the System.Data.SqlClient managed namespace can call the SqlConnection.BeginTransaction method and set the IsolationLevel option to Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot.

OLE DBOLE DB
Cuando se inicia una transacción, las aplicaciones que utilizan OLE DB llaman a ITransactionLocal::StartTransaction con isoLevel establecido en ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT o ISOLATIONLEVEL_SERIALIZABLE.When starting a transaction, applications using OLE DB call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT, or ISOLATIONLEVEL_SERIALIZABLE.

Cuando se especifica el nivel de aislamiento de transacción en el modo de confirmación automática, las aplicaciones OLE DB pueden establecer la propiedad DBPROP_SESS_AUTOCOMMITISOLEVELS de DBPROPSET_SESSION en DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED o DBPROPVAL_TI_SNAPSHOT.When specifying the transaction isolation level in autocommit mode, OLE DB applications can set the DBPROPSET_SESSION property DBPROP_SESS_AUTOCOMMITISOLEVELS to DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED, or DBPROPVAL_TI_SNAPSHOT.

ODBCODBC
Las aplicaciones de ODBC llaman a SQLSetConnectAttr con Attribute establecido en SQL_ATTR_TXN_ISOLATION y ValuePtr establecido en SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ o SQL_TXN_SERIALIZABLE.ODBC applications call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, or SQL_TXN_SERIALIZABLE.

Para las transacciones de instantáneas, las aplicaciones llaman a SQLSetConnectAttr con Attribute establecido en SQL_COPT_SS_TXN_ISOLATION y ValuePtr establecido en SQL_TXN_SS_SNAPSHOT.For snapshot transactions, applications call SQLSetConnectAttr with Attribute set to SQL_COPT_SS_TXN_ISOLATION and ValuePtr set to SQL_TXN_SS_SNAPSHOT. Una transacción de instantánea se puede recuperar mediante SQL_COPT_SS_TXN_ISOLATION o SQL_ATTR_TXN_ISOLATION.A snapshot transaction can be retrieved using either SQL_COPT_SS_TXN_ISOLATION or SQL_ATTR_TXN_ISOLATION.

Bloqueo del motor de base de datosLocking in the Database Engine

El bloqueo es el mecanismo que utiliza el Motor de base de datos de SQL ServerSQL Server Database Engine para sincronizar el acceso por parte de varios usuarios al mismo elemento de datos simultáneamente.Locking is a mechanism used by the Motor de base de datos de SQL ServerSQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.

Antes de que una transacción obtenga una dependencia del estado actual de un elemento de datos, como la lectura o modificación de los datos, debe protegerse de los efectos de otra transacción que modifica los mismos datos.Before a transaction acquires a dependency on the current state of a piece of data, such as by reading or modifying the data, it must protect itself from the effects of another transaction modifying the same data. Para ello, la transacción solicita un bloqueo en el elemento de datos.The transaction does this by requesting a lock on the piece of data. Los bloqueos disponen de diferentes modos, como compartido o exclusivo.Locks have different modes, such as shared or exclusive. El modo del bloqueo indica el nivel de dependencia que la transacción tiene sobre los datos.The lock mode defines the level of dependency the transaction has on the data. No se puede conceder a una transacción un bloqueo que genere un conflicto con el modo de un bloqueo ya concedido para unos datos a otra transacción.No transaction can be granted a lock that would conflict with the mode of a lock already granted on that data to another transaction. Si una transacción solicita un modo de bloqueo que cree un conflicto con otro bloqueo ya concedido sobre los mismos datos, la instancia de Motor de base de datos de SQL ServerSQL Server Database Engine pausará la transacción que realiza la solicitud hasta que se libere el primer bloqueo.If a transaction requests a lock mode that conflicts with a lock that has already been granted on the same data, the instance of the Motor de base de datos de SQL ServerSQL Server Database Engine will pause the requesting transaction until the first lock is released.

Si una transacción modifica un elemento de datos, conserva el bloqueo que protege la modificación hasta el final de la transacción.When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. El tiempo que una transacción conserva los bloqueos obtenidos para proteger operaciones de lectura depende de la configuración del nivel de aislamiento de la transacción.How long a transaction holds the locks acquired to protect read operations depends on the transaction isolation level setting. Todos los bloqueos de una transacción se liberan cuando ésta finaliza (se confirma o se revierte).All locks held by a transaction are released when the transaction completes (either commits or rolls back).

Por regla general, las aplicaciones no solicitan los bloqueos directamente.Applications do not typically request locks directly. Una parte de Motor de base de datos de SQL ServerSQL Server Database Engine, denominada administrador de bloqueos, es la que se encarga de administrar los bloqueos de forma interna.Locks are managed internally by a part of the Motor de base de datos de SQL ServerSQL Server Database Engine called the lock manager. Cuando una instancia de Motor de base de datos de SQL ServerSQL Server Database Engine procesa una instrucción Transact-SQLTransact-SQL, el procesador de consultas de Motor de base de datos de SQL ServerSQL Server Database Engine determina los recursos a los que se va a tener acceso.When an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine processes a Transact-SQLTransact-SQL statement, the Motor de base de datos de SQL ServerSQL Server Database Engine query processor determines which resources are to be accessed. El procesador de consultas determina también qué tipos de bloqueos se necesitan para proteger cada recurso, basándose en el tipo de acceso y en la configuración del nivel de aislamiento de la transacción.The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. A continuación, el procesador de consultas solicita los bloqueos adecuados al administrador de bloqueos.The query processor then requests the appropriate locks from the lock manager. Éste concede los bloqueos si no existen bloqueos en conflicto de otras transacciones.The lock manager grants the locks if there are no conflicting locks held by other transactions.

Granularidad y jerarquías de bloqueoLock Granularity and Hierarchies

El Motor de base de datos de SQL ServerSQL Server Database Engine admite bloqueo multigranular. Esta función permite que una transacción bloquee diferentes tipos de recursos.The Motor de base de datos de SQL ServerSQL Server Database Engine has multigranular locking that allows different types of resources to be locked by a transaction. Para minimizar el costo del bloqueo, Motor de base de datos de SQL ServerSQL Server Database Engine bloquea automáticamente los recursos en el nivel apropiado para la tarea.To minimize the cost of locking, the Motor de base de datos de SQL ServerSQL Server Database Engine locks resources automatically at a level appropriate to the task. Los bloqueos de menor granularidad, como es el caso de las filas, aumentan la simultaneidad. Sin embargo, se produce una sobrecarga mayor porque cuantas más filas se bloquean, más bloqueos se deben mantener.Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked. Los bloqueos realizados en una granularidad alta, por ejemplo en tablas, reducen la simultaneidad porque el bloqueo de toda una tabla restringe el acceso de otras transacciones a cualquier parte de la tabla.Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions. Sin embargo, produce una sobrecarga menor debido a que se mantienen menos bloqueos.However, it has a lower overhead because fewer locks are being maintained.

El Motor de base de datos de SQL ServerSQL Server Database Engine a menudo se ve en la obligación de adquirir bloqueos en distintos niveles de granularidad para brindar una protección completa a un recurso.The Motor de base de datos de SQL ServerSQL Server Database Engine often has to acquire locks at multiple levels of granularity to fully protect a resource. Este grupo de bloqueos en distintos niveles de granularidad se denomina jerarquía de bloqueos.This group of locks at multiple levels of granularity is called a lock hierarchy. Por ejemplo, para brindar protección completa a la lectura de un índice, probablemente sea necesario que una instancia del Motor de base de datos de SQL ServerSQL Server Database Engine adquiera bloqueos compartidos en filas y bloqueos con intención compartida en las páginas y la tabla.For example, to fully protect a read of an index, an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine may have to acquire share locks on rows and intent share locks on the pages and table.

En la siguiente tabla se muestran los recursos que el Motor de base de datos de SQL ServerSQL Server Database Engine puede bloquear.The following table shows the resources that the Motor de base de datos de SQL ServerSQL Server Database Engine can lock.

RecursoResource DescripciónDescription
RIDRID Identificador de fila que se utiliza para bloquear una sola fila de un montón.A row identifier used to lock a single row within a heap.
KEYKEY Bloqueo de fila dentro de un índice que se utiliza para proteger intervalos de claves en transacciones serializables.A row lock within an index used to protect key ranges in serializable transactions.
PAGEPAGE Página de 8 kilobytes (KB) de una base de datos, como páginas de datos o de índices.An 8-kilobyte (KB) page in a database, such as data or index pages.
EXTENTEXTENT Grupo contiguo de ocho páginas, como páginas de datos o de índices.A contiguous group of eight pages, such as data or index pages.
HoBTHoBT Montón o árbol b.A heap or B-tree. Bloqueo que protege un árbol B (índice) o las páginas de datos del montón en una tabla que no posee un índice clúster.A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.
TABLETABLE Tabla completa, con todos los datos e índices.The entire table, including all data and indexes.
FILEFILE Archivos de la base de datos.A database file.
APPLICATIONAPPLICATION Recurso especificado por la aplicación.An application-specified resource.
METADATOSMETADATA Bloqueos de metadatos.Metadata locks.
ALLOCATION_UNITALLOCATION_UNIT Unidad de asignación.An allocation unit.
DATABASEDATABASE Base de datos completa.The entire database.

Nota

La opción LOCK_ESCALATION de ALTER TABLE puede afectar a los bloqueos HoBT y TABLE.HoBT and TABLE locks can be affected by the LOCK_ESCALATION option of ALTER TABLE.

Modos de bloqueoLock Modes

El Motor de base de datos de SQL ServerSQL Server Database Engine bloquea los recursos con diferentes modos de bloqueo que determinan el modo en que las transacciones simultáneas pueden tener acceso a los recursos.The Motor de base de datos de SQL ServerSQL Server Database Engine locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.

En la siguiente tabla se indican los modos de bloqueo de recursos que emplea Motor de base de datos de SQL ServerSQL Server Database Engine.The following table shows the resource lock modes that the Motor de base de datos de SQL ServerSQL Server Database Engine uses.

Modo de bloqueoLock mode DescripciónDescription
Compartido (S)Shared (S) Se utiliza para operaciones de lectura que no cambian ni actualizan datos, como la instrucción SELECT.Used for read operations that do not change or update data, such as a SELECT statement.
Actualizado (U)Update (U) Se utiliza en recursos que se pueden actualizar.Used on resources that can be updated. Evita una forma común de interbloqueo que se produce cuando varias sesiones leen, bloquean y actualizan recursos.Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusivo (X)Exclusive (X) Se utiliza para operaciones de modificación de datos, como INSERT, UPDATE o DELETE.Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Garantiza que no puedan realizarse varias actualizaciones simultáneamente en el mismo recurso.Ensures that multiple updates cannot be made to the same resource at the same time.
IntenciónIntent Se utiliza para establecer una jerarquía de bloqueos.Used to establish a lock hierarchy. Los tipos de bloqueo de intención son: intención compartido (IS), intención exclusivo (IX) y compartido con intención exclusivo (SIX).The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
EsquemaSchema Se utiliza cuando se ejecuta una operación que depende del esquema de una tabla.Used when an operation dependent on the schema of a table is executing. Hay dos tipos de bloqueo de esquema: modificación del esquema (Sch-M) y modificación de estabilidad (Sch-S).The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Actualización masiva (BU)Bulk Update (BU) Se usa cuando se copian datos de forma masiva en una tabla y se especifica la sugerencia TABLOCK.Used when bulk copying data into a table and the TABLOCK hint is specified.
Intervalo de clavesKey-range Protege el intervalo de filas que lee una consulta cuando se utiliza el nivel de aislamiento de transacciones serializables.Protects the range of rows read by a query when using the serializable transaction isolation level. Garantiza que otras transacciones no puedan insertar filas que podrían incluirse como respuesta de las consultas de la transacción serializable si las consultas se volvieran a ejecutar.Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Bloqueos compartidosShared Locks

Los bloqueos compartidos (S) permiten que varias transacciones simultáneas lean (SELECT) un recurso en situaciones de control de simultaneidad pesimista.Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. Ninguna otra transacción podrá modificar los datos mientras el bloqueo compartido (S) exista en el recurso.No other transactions can modify the data while shared (S) locks exist on the resource. Los bloqueos compartidos (S) en un recurso se liberan tan pronto como finaliza la operación de lectura, a menos que se haya establecido el nivel de aislamiento de la transacción como REPEATABLE READ o más alto, o bien se utilice una sugerencia de bloqueo para mantener los bloqueos compartidos (S) durante la transacción.Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

Bloqueos de actualizaciónUpdate Locks

Los bloqueos de actualización (U) evitan una forma común de interbloqueo.Update (U) locks prevent a common form of deadlock. En una transacción de lectura repetible o serializable, la transacción lee los datos, adquiere un bloqueo compartido (S) en el recurso (página o fila) y, a continuación, modifica los datos, lo que requiere una conversión del bloqueo en un bloqueo exclusivo (X).In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. Si dos transacciones adquieren bloqueos compartidos en un recurso y, a continuación, intentan actualizar los datos simultáneamente, una de ellas intenta convertir el bloqueo en un bloqueo exclusivo (X).If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. La conversión de bloqueo compartido en exclusivo debe esperar, ya que el bloqueo exclusivo de una transacción no es compatible con el bloqueo compartido de la otra. Por tanto, se produce una espera de bloqueos.The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. La segunda transacción intenta adquirir un bloqueo exclusivo (X) para realizar su actualización.The second transaction attempts to acquire an exclusive (X) lock for its update. Debido a que ambas transacciones intentan convertir los bloqueos en exclusivos (X) y cada una espera a que la otra libere su bloqueo de modo compartido, se produce un interbloqueo.Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.

Para evitar este posible problema de interbloqueo, se utilizan los bloqueos de actualización (U).To avoid this potential deadlock problem, update (U) locks are used. Dos transacciones no pueden obtener simultáneamente un bloqueo de actualización (U) para un recurso.Only one transaction can obtain an update (U) lock to a resource at a time. Si una transacción modifica un recurso, el bloqueo de actualización (U) se convierte en un bloqueo exclusivo (X).If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.

Bloqueos exclusivosExclusive Locks

Los bloqueos exclusivos (X) evitan que transacciones simultáneas tengan acceso a un recurso.Exclusive (X) locks prevent access to a resource by concurrent transactions. Al utilizar un bloqueo exclusivo (X), el resto de las transacciones no pueden modificar los datos; las operaciones de lectura solo se pueden realizar si se utiliza la sugerencia NOLOCK o el nivel de aislamiento de lectura no confirmada.With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.

Las instrucciones para modificar datos, como INSERT, UPDATE y DELETE combinan las operaciones de modificación con las de lectura.Data modification statements, such as INSERT, UPDATE, and DELETE combine both modification and read operations. En primer lugar, la instrucción lleva a cabo operaciones de lectura para adquirir los datos antes de proceder a ejecutar las operaciones de modificación necesarias.The statement first performs read operations to acquire data before performing the required modification operations. Por tanto, las instrucciones de modificación de datos suelen solicitar bloqueos compartidos y exclusivos.Data modification statements, therefore, typically request both shared locks and exclusive locks. Por ejemplo, una instrucción UPDATE puede modificar las filas de una tabla a partir de una combinación con otra tabla.For example, an UPDATE statement might modify rows in one table based on a join with another table. En este caso, la instrucción UPDATE solicita bloqueos compartidos para la filas leídas en la tabla de combinación, además de bloqueos exclusivos para las filas actualizadas.In this case, the UPDATE statement requests shared locks on the rows read in the join table in addition to requesting exclusive locks on the updated rows.

Bloqueos con intenciónIntent Locks

Motor de base de datos de SQL ServerSQL Server Database Engine utiliza bloqueos con intención para proteger la aplicación de un bloqueo compartido (S) o exclusivo (X) en un recurso inferior en la jerarquía de bloqueos.The Motor de base de datos de SQL ServerSQL Server Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Los bloqueos con intención se denominan así porque se adquieren antes que los bloqueos de los niveles inferiores y, por lo tanto, señalan la intención de aplicar bloqueos en un nivel inferior.Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

Los bloqueos con intención se utilizan con dos fines:Intent locks serve two purposes:

  • Para evitar que otras transacciones modifiquen el recurso de nivel superior de forma que invaliden el bloqueo del nivel inferior.To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
  • Para mejorar la eficacia de Motor de base de datos de SQL ServerSQL Server Database Engine para detectar conflictos de bloqueo en el nivel superior de granularidad.To improve the efficiency of the Motor de base de datos de SQL ServerSQL Server Database Engine in detecting lock conflicts at the higher level of granularity.

Por ejemplo, un bloqueo con intención compartida para el nivel de tabla se solicita antes que los bloqueos compartidos (S) para las páginas o filas de la tabla.For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Establecer un bloqueo con intención en una tabla evita que otra transacción adquiera un bloqueo exclusivo (X) para la tabla que contiene esa página.Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Los bloqueos con intención mejoran el rendimiento, porque Motor de base de datos de SQL ServerSQL Server Database Engine examina los bloqueos con intención solo en el nivel de tabla para determinar si una transacción puede adquirir un bloqueo de dicha tabla de forma segura.Intent locks improve performance because the Motor de base de datos de SQL ServerSQL Server Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. Esto elimina la necesidad de examinar cada bloqueo de fila o de página de la tabla para determinar si una transacción puede bloquear toda la tabla.This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

Los bloqueos con intención incluyen: Intención compartida (IS), Intención exclusiva (IX) e Intención compartida exclusiva (SIX).Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Modo de bloqueoLock mode DescripciónDescription
Intención compartida (IS)Intent shared (IS) Protege los bloqueos compartidos solicitados o adquiridos de algunos recursos (aunque no todos) situados en un nivel inferior de la jerarquía.Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.
Intención exclusiva (IX)Intent exclusive (IX) Protege los bloqueos exclusivos solicitados o adquiridos de algunos recursos (aunque no todos) situados en un nivel inferior de la jerarquía.Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX es un superconjunto de IS, y protege las solicitudes de bloqueos compartidos en recursos de niveles inferiores.IX is a superset of IS, and it also protects requesting shared locks on lower level resources.
Compartido con intención exclusiva (SIX)Shared with intent exclusive (SIX) Protege los bloqueos compartidos solicitados o adquiridos de todos los recursos situados en un nivel inferior de la jerarquía y los bloqueos con intención exclusiva de algunos (aunque no todos) los recursos de niveles inferiores.Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. Se permiten los bloqueos IS simultáneos en el recurso de nivel superior.Concurrent IS locks at the top-level resource are allowed. Por ejemplo, al adquirir un bloqueo SIX para una tabla, también se adquieren bloqueos con intención exclusiva de las páginas que se modifican y bloqueos exclusivos de las filas modificadas.For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. Solo puede haber un bloqueo SIX simultáneo por recurso, para impedir que otras transacciones lo actualicen, aunque otras transacciones pueden leer los recursos inferiores de la jerarquía obteniendo bloqueos IS en el nivel de tabla.There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.
Actualizar intención (IU)Intent update (IU) Protege los bloqueos de actualización solicitados o adquiridos de todos los recursos de niveles inferiores de la jerarquía.Protects requested or acquired update locks on all resources lower in the hierarchy. Los bloqueos IU solo se utilizan para los recursos de página.IU locks are used only on page resources. Los bloqueos IU se convierten en bloqueos IX cuando se ejecutan operaciones de actualización.IU locks are converted to IX locks if an update operation takes place.
Actualizar intención compartida (SIU)Shared intent update (SIU) Combinación de bloqueos S e IU que resulta de adquirir estos bloqueos por separado y de mantenerlos simultáneamente.A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. Por ejemplo, sería el caso de una transacción que ejecuta una consulta con la sugerencia PAGLOCK y luego ejecuta una operación de actualización.For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. La consulta con la sugerencia PAGLOCK adquiere el bloqueo S y la operación de actualización, el bloqueo IU.The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.
Actualizar intención exclusiva (UIX)Update intent exclusive (UIX) Combinación de bloqueos U e IX que resulta de adquirir estos bloqueos por separado y de mantenerlos simultáneamente.A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

Bloqueos de esquemaSchema Locks

Motor de base de datos de SQL ServerSQL Server Database Engine utiliza bloqueos de modificación del esquema (Sch-M) cuando se realiza una operación de lenguaje de definición de datos (DDL) en tablas como, por ejemplo, agregar una columna o quitar una tabla.The Motor de base de datos de SQL ServerSQL Server Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. Mientras se conserva, el bloqueo Sch-M evita el acceso simultáneo a la tabla.During the time that it is held, the Sch-M lock prevents concurrent access to the table. Esto significa que el bloqueo Sch-M bloquea todas las operaciones externas hasta que el bloqueo se libera.This means the Sch-M lock blocks all outside operations until the lock is released.

Algunas operaciones del lenguaje de manipulación de datos (DML), como el truncamiento de tablas, utilizan los bloqueos Sch-M para impedir el acceso a las tablas afectadas por operaciones simultáneas.Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.

Motor de base de datos de SQL ServerSQL Server Database Engine usa bloqueos de estabilidad del esquema (Sch-S) al compilar y ejecutar consultas.The Motor de base de datos de SQL ServerSQL Server Database Engine uses schema stability (Sch-S) locks when compiling and executing queries. Los bloqueos Sch-S no impiden los bloqueos de transacciones, incluidos los bloqueos exclusivos (X).Sch-S locks do not block any transactional locks, including exclusive (X) locks. Por tanto, otras transacciones, incluidas las que tienen bloqueos X de una tabla, pueden seguir ejecutándose mientras se compila una consulta.Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. No obstante, en la tabla no se pueden realizar operaciones DDL simultáneas ni operaciones DML simultáneas que adquieren bloqueos Sch-M.However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table.

Bloqueos de actualización masivaBulk Update Locks

Los bloqueos de actualización masiva (BU) permiten que varios subprocesos copien datos de forma masiva y simultánea en la misma tabla, pero impiden que otros procesos que no están copiando datos de forma masiva tengan acceso a la tabla.Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table. El Motor de base de datos de SQL ServerSQL Server Database Engine utiliza bloqueos de actualización masiva cuando las dos condiciones siguientes son verdaderas.The Motor de base de datos de SQL ServerSQL Server Database Engine uses bulk update (BU) locks when both of the following conditions are true.

  • Está usando la instrucción Transact-SQLTransact-SQL BULK INSERT o la función OPENROWSET(BULK), o está usando uno de los comandos de API Bulk Insert como .NET SqlBulkCopy, las API de carga rápida de OLEDB o las API de copia masiva de ODBC para copiar de forma masiva datos en una tabla.You use the Transact-SQLTransact-SQL BULK INSERT statement, or the OPENROWSET(BULK) function, or you use one of the Bulk Insert API commands such as .NET SqlBulkCopy, OLEDB Fast Load APIs, or the ODBC Bulk Copy APIs to bulk copy data into a table.
  • Se especifica la sugerencia TABLOCK o se establece la opción de tabla table lock on bulk load con sp_tableoption.The TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption.

Sugerencia

A diferencia de la instrucción BULK INSERT, que contiene un bloqueo Bulk Update menos restrictivo, INSERT INTO…SELECT con la sugerencia TABLOCK retiene un bloqueo exclusivo (X) en la tabla.Unlike the BULK INSERT statement, which holds a less restrictive Bulk Update lock, INSERT INTO...SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. Esto significa que no se pueden insertar filas mediante operaciones de inserción en paralelo.This means that you cannot insert rows using parallel insert operations.

Bloqueos de intervalo de clavesKey-Range Locks

Los bloqueos de rangos con clave protegen un intervalo de filas incluido implícitamente en un conjunto de registros que se lee con una instrucción Transact-SQLTransact-SQL mientras se utiliza el nivel de aislamiento de transacción serializable.Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQLTransact-SQL statement while using the serializable transaction isolation level. El bloqueo de intervalos con clave impide las lecturas fantasma.Key-range locking prevents phantom reads. Al proteger los intervalos de claves entre filas, también se evitan inserciones o eliminaciones fantasma en los conjuntos de registros a los que obtienen acceso las transacciones.By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

Compatibilidad de bloqueosLock Compatibility

La compatibilidad de bloqueos controla si varias transacciones pueden adquirir bloqueos sobre el mismo recurso a la vez.Lock compatibility controls whether multiple transactions can acquire locks on the same resource at the same time. Si un recurso ya está bloqueado por otra transacción, solo se puede conceder una nueva solicitud de bloqueo si el bloqueo solicitado es compatible con el modo del bloqueo existente.If a resource is already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock. Si el modo del bloqueo solicitado no es compatible con el bloqueo existente, la transacción que solicita el nuevo bloqueo espera a que se libere el bloqueo existente o a que expire el intervalo de tiempo de espera del bloqueo.If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire. Por ejemplo, ningún modo de bloqueo es compatible con bloqueos exclusivos.For example, no lock modes are compatible with exclusive locks. Mientras se mantiene un bloqueo exclusivo (X), ninguna otra transacción puede adquirir un bloqueo de ninguna clase (compartido, de actualización o exclusivo) en dicho recurso hasta que se libere el bloqueo exclusivo.While an exclusive (X) lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that resource until the exclusive (X) lock is released. Como alternativa, si se ha aplicado un bloqueo compartido (S) a un recurso, otras transacciones también pueden adquirir un bloqueo compartido o de actualización (U) en el elemento, aunque la primera transacción no haya terminado.Alternatively, if a shared (S) lock has been applied to a resource, other transactions can also acquire a shared lock or an update (U) lock on that item even if the first transaction has not completed. Sin embargo, otras transacciones no pueden adquirir un bloqueo exclusivo si no se anula el bloqueo compartido.However, other transactions cannot acquire an exclusive lock until the shared lock has been released.

En la tabla siguiente se muestra la compatibilidad de los modos de bloqueo más frecuentes.The following table shows the compatibility of the most commonly encountered lock modes.

Modo concedido existenteExisting granted mode ISIS SS UU IXIX SIXSIX XX
Modo solicitadoRequested mode
Intención compartida (IS)Intent shared (IS) Yes Yes Yes Yes Yes NoNo
Compartido (S)Shared (S) Yes Yes Yes NoNo NoNo NoNo
Actualizado (U)Update (U) Yes Yes NoNo NoNo NoNo NoNo
Intención exclusiva (IX)Intent exclusive (IX) Yes NoNo NoNo Yes NoNo NoNo
Compartido con intención exclusiva (SIX)Shared with intent exclusive (SIX) Yes NoNo NoNo NoNo NoNo NoNo
Exclusivo (X)Exclusive (X) NoNo NoNo NoNo NoNo NoNo NoNo

Nota

Un bloqueo con intención exclusivo (IX) es compatible con un modo de bloqueo IX, porque IX indica la intención de actualizar solamente algunas de las filas, no todas.An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. También se permite que otras transacciones intenten leer o actualizar algunas filas, siempre y cuando no se trate de las mismas filas que están actualizando las demás transacciones.Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions. Además, si dos transacciones intentan actualizar la misma fila, se permitirá a ambas transacciones un bloqueo IX en el nivel de tabla y de página.Further, if two transactions attempt to update the same row, both transactions will be granted an IX lock at table and page level. Sin embargo, un bloqueo X en el nivel de fila solo se permitirá a una transacción.However, one transaction will be granted an X lock at row level. La otra transacción deberá esperar a que se quite el bloqueo en el nivel de fila.The other transaction must wait until the row-level lock is removed.

Utilice la siguiente tabla para determinar la compatibilidad de todos los modos de bloqueo disponibles en SQL ServerSQL Server.Use the following table to determine the compatibility of all the lock modes available in SQL ServerSQL Server.

lock_conflicts

Bloquear intervalos con claveKey-Range Locking

Los bloqueos de rangos con clave protegen un intervalo de filas incluido implícitamente en un conjunto de registros que se lee con una instrucción Transact-SQLTransact-SQL mientras se utiliza el nivel de aislamiento de transacción serializable.Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQLTransact-SQL statement while using the serializable transaction isolation level. El nivel de aislamiento serializable requiere que las consultas ejecutadas durante una transacción deben obtener el mismo conjunto de filas cada vez que se ejecutan en la transacción.The serializable isolation level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the transaction. El bloqueo de intervalos con clave protege este requisito, ya que impide que otras transacciones inserten nuevas filas cuyas claves se incluirían en el intervalo de claves leído por la transacción serializable.A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction.

El bloqueo de intervalos con clave impide las lecturas fantasma.Key-range locking prevents phantom reads. La protección de los intervalos de claves entre filas también impide las inserciones fantasma en un conjunto de registros a los que tiene acceso una transacción.By protecting the ranges of keys between rows, it also prevents phantom insertions into a set of records accessed by a transaction.

El bloqueo de intervalos con clave se incluye en un índice, especificando los valores de clave inicial y final.A key-range lock is placed on an index, specifying a beginning and ending key value. Este bloqueo impide la inserción, actualización o eliminación de filas con un valor de clave incluido en el intervalo, ya que estas operaciones deben obtener en primer lugar un bloqueo en el índice.This lock blocks any attempt to insert, update, or delete any row with a key value that falls in the range because those operations would first have to acquire a lock on the index. Por ejemplo, una transacción serializable podría emitir una instrucción SELECT que lee todas las filas cuyos valores clave coincidan con la condición BETWEEN 'AAA' AND 'CZZ'.For example, a serializable transaction could issue a SELECT statement that reads all rows whose key values match the condition BETWEEN 'AAA' AND 'CZZ'. El bloqueo de intervalos con clave en los valores de clave del intervalo ' AAA ' a ' CZZ ' impide que otras transacciones inserten filas con valores de clave situados en dicho intervalo, como ' ADG ' , ' BBD ' o ' CAL ' .A key-range lock on the key values in the range from ' AAA ' to ' CZZ ' prevents other transactions from inserting rows with key values anywhere in that range, such as ' ADG ', ' BBD ', or ' CAL '.

Modos de bloqueo de intervalos con claveKey-Range Lock Modes

Los bloqueos de intervalos con clave incluyen dos componentes, una fila y un intervalo, especificados con el formato intervalo-fila:Key-range locks include both a range and a row component specified in range-row format:

  • El intervalo representa el modo de bloqueo que protege el intervalo entre dos entradas de índice consecutivas.Range represents the lock mode protecting the range between two consecutive index entries.

  • La fila representa el modo de bloqueo que protege la entrada de índice.Row represents the lock mode protecting the index entry.

  • El modo representa el modo de bloqueo combinado que se utiliza.Mode represents the combined lock mode used. Los modos de bloqueo del intervalo de claves constan de dos partes.Key-range lock modes consist of two parts. La primera representa el tipo de bloqueo que se utiliza para bloquear el intervalo del índice (Range T) y la segunda representa el tipo de bloqueo que se utiliza para bloquear una clave específica (K).The first represents the type of lock used to lock the index range (Range T) and the second represents the lock type used to lock a specific key (K). Ambas partes se conectan con un guion (-), como Range T-K.The two parts are connected with a hyphen (-), such as Range T-K.

    IntervaloRange RowRow ModeMode DescripciónDescription
    RangeSRangeS SS RangeS-SRangeS-S Intervalo compartido, bloqueo de recurso compartido; recorrido de intervalo serializable.Shared range, shared resource lock; serializable range scan.
    RangeSRangeS UU RangeS-URangeS-U Intervalo compartido, bloqueo de recurso de actualización; recorrido de actualización serializable.Shared range, update resource lock; serializable update scan.
    RangeIRangeI NullNull RangeI-NRangeI-N Intervalo de inserción, bloqueo de recurso nulo; se utiliza para comprobar los intervalos antes de insertar una nueva clave en un índice.Insert range, null resource lock; used to test ranges before inserting a new key into an index.
    RangeXRangeX XX RangeX-XRangeX-X Intervalo exclusivo, bloqueo de recurso exclusivo; se utiliza al actualizar una clave de un intervalo.Exclusive range, exclusive resource lock; used when updating a key in a range.

Nota

El modo de bloqueo Null interno es compatible con los demás modos de bloqueo.The internal Null lock mode is compatible with all other lock modes.

Los modos de bloqueo de intervalos con clave tienen una matriz de compatibilidad que muestra los bloqueos que son compatibles con otros bloqueos obtenidos en claves e intervalos superpuestos.Key-range lock modes have a compatibility matrix that shows which locks are compatible with other locks obtained on overlapping keys and ranges.

Modo concedido existenteExisting granted mode SS UU XX RangeS-SRangeS-S RangeS-URangeS-U RangeI-NRangeI-N RangeX-XRangeX-X
Modo solicitadoRequested mode
Compartido (S)Shared (S) Yes Yes NoNo Yes Yes Yes NoNo
Actualizado (U)Update (U) Yes NoNo NoNo Yes NoNo Yes NoNo
Exclusivo (X)Exclusive (X) NoNo NoNo NoNo NoNo NoNo Yes NoNo
RangeS-SRangeS-S Yes Yes NoNo Yes Yes NoNo NoNo
RangeS-URangeS-U Yes NoNo NoNo Yes NoNo NoNo NoNo
RangeI-NRangeI-N Yes Yes Yes NoNo NoNo Yes NoNo
RangeX-XRangeX-X NoNo NoNo NoNo NoNo NoNo NoNo NoNo

Bloqueos de conversiónConversion Locks

Los bloqueos de conversión se crean cuando un bloqueo de intervalos con clave se superpone a otro bloqueo.Conversion locks are created when a key-range lock overlaps another lock.

Bloqueo 1Lock 1 Bloqueo 2Lock 2 Bloqueo de conversiónConversion lock
SS RangeI-NRangeI-N RangeI-SRangeI-S
UU RangeI-NRangeI-N RangeI-URangeI-U
XX RangeI-NRangeI-N RangeI-XRangeI-X
RangeI-NRangeI-N RangeS-SRangeS-S RangeX-SRangeX-S
RangeI-NRangeI-N RangeS-URangeS-U RangeX-URangeX-U

Los bloqueos de conversión se producen durante breves períodos de tiempo en circunstancias diversas y complejas, y en ocasiones mientras se ejecutan procesos simultáneos.Conversion locks can be observed for a short period of time under different complex circumstances, sometimes while running concurrent processes.

Recorrido de intervalo serializable, captura de singleton, eliminación e inserciónSerializable Range Scan, Singleton Fetch, Delete, and Insert

El bloqueo de intervalos con clave garantiza que las siguientes operaciones son serializables:Key-range locking ensures that the following operations are serializable:

  • Consulta de recorrido de intervalosRange scan query
  • Captura de singleton de fila inexistenteSingleton fetch of nonexistent row
  • Operación de eliminaciónDelete operation
  • Operación de inserciónInsert operation

Para que el bloqueo de intervalos con clave se produzca, es necesario que se cumplan las condiciones siguientes:Before key-range locking can occur, the following conditions must be satisfied:

  • El nivel de aislamiento de las transacciones se debe establecer en SERIALIZABLE.The transaction-isolation level must be set to SERIALIZABLE.
  • El procesador de consultas debe utilizar un índice para implementar el predicado del filtro de intervalo.The query processor must use an index to implement the range filter predicate. Por ejemplo, la cláusula WHERE de una instrucción SELECT puede establecer una condición de intervalo con este predicado: ColumnX BETWEEN N " AAA " AND N " CZZ " .For example, the WHERE clause in a SELECT statement could establish a range condition with this predicate: ColumnX BETWEEN N ' AAA ' AND N ' CZZ '. El bloqueo de intervalos con clave solo se puede adquirir si una clave de índice abarca ColumnX.A key-range lock can only be acquired if ColumnX is covered by an index key.

EjemplosExamples

La tabla y el índice siguientes se utilizan como base para los ejemplos de bloqueo de intervalos con clave que se muestran a continuación.The following table and index are used as a basis for the key-range locking examples that follow.

btree

Consulta de recorrido de intervalosRange Scan Query

Para poder asegurar que una consulta de recorrido de intervalos es serializable, la misma consulta debe devolver los mismos resultados cada vez que se ejecuta en la misma transacción.To ensure a range scan query is serializable, the same query should return the same results each time it is executed within the same transaction. Otras transacciones no deben insertar nuevas filas en la consulta de recorrido de intervalos; de lo contrario, se convierten en inserciones fantasma.New rows must not be inserted within the range scan query by other transactions; otherwise, these become phantom inserts. Por ejemplo, la siguiente consulta utiliza la tabla y el índice de la ilustración anterior:For example, the following query uses the table and index in the previous illustration:

SELECT name  
FROM mytable  
WHERE name BETWEEN 'A' AND 'C';  

Los bloqueos de intervalos con clave se colocan en las entradas de índice que se corresponden al intervalo de filas de datos cuyo nombre se encuentra entre los valores Adam y Dale, lo que impide que se agreguen o eliminen nuevas filas obtenidas en la consulta anterior.Key-range locks are placed on the index entries corresponding to the range of data rows where the name is between the values Adam and Dale, preventing new rows qualifying in the previous query from being added or deleted. Aunque el primer nombre del intervalo es Adam, el bloqueo de intervalos con clave RangeS-S en esta entrada de índice garantiza que no se pueden agregar nombres nuevos que empiecen por la letra A delante de Adam, como Abigail.Although the first name in this range is Adam, the RangeS-S mode key-range lock on this index entry ensures that no new names beginning with the letter A can be added before Adam, such as Abigail. De forma similar, el bloqueo de intervalos con clave RangeS-S en la entrada de índice de Dale garantiza que no se van a agregar nombres nuevos que empiecen por la letra C detrás de Carlos, como Clive.Similarly, the RangeS-S key-range lock on the index entry for Dale ensures that no new names beginning with the letter C can be added after Carlos, such as Clive.

Nota

El número de bloqueos RangeS-S que se mantiene es n+1, siendo n el número de filas que satisfacen la consulta.The number of RangeS-S locks held is n+1, where n is the number of rows that satisfy the query.

Captura de singleton de datos inexistentesSingleton Fetch of Nonexistent Data

Si una consulta de una transacción intenta seleccionar una fila que no existe, la ejecución de la consulta en un punto posterior de la misma transacción tiene que devolver el mismo resultado.If a query within a transaction attempts to select a row that does not exist, issuing the query at a later point within the same transaction has to return the same result. No se puede permitir a otra transacción insertar la fila inexistente.No other transaction can be allowed to insert that nonexistent row. Por ejemplo, con esta consulta:For example, given this query:

SELECT name  
FROM mytable  
WHERE name = 'Bill';  

Se aplica un bloqueo de intervalos con clave a la entrada de índice correspondiente al intervalo de nombres comprendido entre Ben y Bing, ya que se podría insertar el nombre Bill entre estas dos entradas de índice adyacentes.A key-range lock is placed on the index entry corresponding to the name range from Ben to Bing because the name Bill would be inserted between these two adjacent index entries. El bloqueo de intervalos con clave del modo RangeS-S se coloca en la entrada de índice Bing.The RangeS-S mode key-range lock is placed on the index entry Bing. Esto impide que otra transacción inserte valores, como Bill, entre las entradas de índice Ben y Bing.This prevents any other transaction from inserting values, such as Bill, between the index entries Ben and Bing.

Operación de eliminaciónDelete Operation

Cuando se elimina un valor en una transacción, el intervalo en el que entra el valor no debe estar bloqueado mientras se ejecuta la transacción que realiza la operación de eliminación.When deleting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the delete operation. Para mantener la seriabilidad basta con bloquear el valor de la clave eliminada hasta el final de la transacción.Locking the deleted key value until the end of the transaction is sufficient to maintain serializability. Por ejemplo, con esta instrucción DELETE:For example, given this DELETE statement:

DELETE mytable  
WHERE name = 'Bob';  

Se ha colocado un bloqueo exclusivo (X) en la entrada de índice correspondiente al nombre Bob.An exclusive (X) lock is placed on the index entry corresponding to the name Bob. Otras transacciones pueden insertar o eliminar valores antes o después del valor eliminado Bob.Other transactions can insert or delete values before or after the deleted value Bob. Sin embargo, cualquier transacción que intente leer, insertar o eliminar el valor Bob se bloqueará hasta que la transacción de eliminación se confirme o se revierta.However, any transaction that attempts to read, insert, or delete the value Bob will be blocked until the deleting transaction either commits or rolls back.

La eliminación del intervalo se puede ejecutar con tres modos de bloqueo básicos: bloqueo de fila, de página o de tabla.Range delete can be executed using three basic lock modes: row, page, or table lock. El optimizador de consultas decide la estrategia de bloqueo de página, tabla o fila, o bien la especifica el usuario mediante sugerencias del optimizador como ROWLOCK, PAGLOCK o TABLOCK.The row, page, or table locking strategy is decided by Query Optimizer or can be specified by the user through Query Optimizer hints such as ROWLOCK, PAGLOCK, or TABLOCK. Cuando se utiliza PAGLOCK o TABLOCK, Motor de base de datos de SQL ServerSQL Server Database Engine anula de forma inmediata la asignación de una página de índice si se eliminan todas sus filas.When PAGLOCK or TABLOCK is used, the Motor de base de datos de SQL ServerSQL Server Database Engine immediately deallocates an index page if all rows are deleted from this page. Por el contrario, cuando se utiliza ROWLOCK, todas las filas eliminadas se marcan solo como eliminadas, y se quitan de la página de índice posteriormente mediante una tarea en segundo plano.In contrast, when ROWLOCK is used, all deleted rows are marked only as deleted; they are removed from the index page later using a background task.

Operación de inserciónInsert Operation

Cuando se inserta un valor en una transacción, el intervalo en el que entra el valor no debe estar bloqueado mientras se ejecuta la transacción que realiza la operación de inserción.When inserting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the insert operation. Basta con bloquear el valor de clave insertado hasta el final de la transacción para mantener la seriabilidad.Locking the inserted key value until the end of the transaction is sufficient to maintain serializability. Por ejemplo, con esta instrucción INSERT:For example, given this INSERT statement:

INSERT mytable VALUES ('Dan');  

El bloqueo de intervalos con clave de modo RangeI-N se coloca en la entrada de índice correspondiente al nombre David para probar el intervalo.The RangeI-N mode key-range lock is placed on the index entry corresponding to the name David to test the range. Si se concede el bloqueo, se inserta Dan y se coloca un bloqueo exclusivo (X) en el valor Dan.If the lock is granted, Dan is inserted and an exclusive (X) lock is placed on the value Dan. El bloqueo de intervalos con clave de modo RangeI-N solo es necesario para probar el intervalo y no se mantiene mientras se ejecuta la transacción que realiza la operación de inserción.The RangeI-N mode key-range lock is necessary only to test the range and is not held for the duration of the transaction performing the insert operation. Otras transacciones pueden insertar o eliminar valores antes o después del valor insertado Dan.Other transactions can insert or delete values before or after the inserted value Dan. Sin embargo, cualquier transacción que intente leer, insertar o eliminar el valor Dan se bloqueará hasta que se confirme o se revierta la transacción de inserción.However, any transaction attempting to read, insert, or delete the value Dan will be locked until the inserting transaction either commits or rolls back.

Extensión de bloqueoLock Escalation

La extensión de bloqueo es el proceso de convertir muchos bloqueos concretos en menos bloqueos más generales, lo que reduce la sobrecarga del sistema al tiempo que aumenta la probabilidad de contención de simultaneidad.Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.

A medida que Motor de base de datos de SQL ServerSQL Server Database Engine adquiere bloqueos de bajo nivel, también coloca bloqueos preventivos en los objetos que contienen los objetos de nivel más bajo:As the Motor de base de datos de SQL ServerSQL Server Database Engine acquires low-level locks, it also places intent locks on the objects that contain the lower-level objects:

  • Al bloquear filas o intervalos de clave de índice, Motor de base de datosDatabase Engine coloca un bloqueo preventivo en las páginas que contienen las filas o claves.When locking rows or index key ranges, the Motor de base de datosDatabase Engine places an intent lock on the pages that contain the rows or keys.
  • Al bloquear páginas, Motor de base de datosDatabase Engine coloca un bloqueo preventivo en los objetos de más alto nivel que contienen las páginas.When locking pages, the Motor de base de datosDatabase Engine places an intent lock on the higher level objects that contain the pages. Además del bloqueo de intención en el objeto, los bloqueos de página de intento se solicitan en los objetos siguientes:In addition to intent lock on the object, intent page locks are requested on the following objects:
    • Páginas de nivel de hoja de índices no clústerLeaf-level pages of nonclustered indexes
    • Páginas de datos de los índices clústerData pages of clustered indexes
    • Páginas de datos de montónHeap data pages

Es posible que Motor de base de datosDatabase Engine realice bloqueos de fila y página para una misma instrucción a fin de minimizar el número de bloqueos y reducir la probabilidad de que sea necesario realizar la extensión de bloqueo.The Motor de base de datosDatabase Engine might do both row and page locking for the same statement to minimize the number of locks and reduce the likelihood that lock escalation will be necessary. Por ejemplo, el Motor de base de datos podría colocar bloqueos de página en un índice no agrupado (si se seleccionan suficientes claves contiguas del nodo del índice como para satisfacer la consulta) y bloqueos de fila en los datos.For example, the Database Engine could place page locks on a nonclustered index (if enough contiguous keys in the index node are selected to satisfy the query) and row locks on the data.

Para extender los bloqueos, Motor de base de datosDatabase Engine intenta cambiar el bloqueo preventivo de la tabla por el correspondiente bloqueo completo; por ejemplo, cambiar un bloqueo preventivo exclusivo (IX) por un bloqueo exclusivo (X), o bien un bloqueo preventivo compartido (IS) por un bloqueo compartido (S).To escalate locks, the Motor de base de datosDatabase Engine attempts to change the intent lock on the table to the corresponding full lock, for example, changing an intent exclusive (IX) lock to an exclusive (X) lock, or an intent shared (IS) lock to a shared (S) lock). Si el intento de extensión de bloqueo se realiza correctamente y se adquiere el bloqueo de toda la tabla, se liberan todos los bloqueos de montón o árbol B, página (PAGE) o fila (RID) retenidos por la transacción en el montón o el índice.If the lock escalation attempt succeeds and the full table lock is acquired, then all heap or B-tree, page (PAGE), or row-level (RID) locks held by the transaction on the heap or index are released. Si no se puede adquirir el bloqueo completo, no se produce ninguna extensión de bloqueo en el momento y el Motor de base de datos continúa para adquirir bloqueos de fila, clave o página.If the full lock cannot be acquired, no lock escalation happens at that time and the Database Engine will continue to acquire row, key, or page locks.

Motor de base de datosDatabase Engine no extiende los bloqueos de filas o de intervalo de claves a bloqueos de páginas, sino que lo hace directamente en bloqueos de tablas.The Motor de base de datosDatabase Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. De manera similar, los bloqueos de páginas se concentran siempre en bloqueos de tablas.Similarly, page locks are always escalated to table locks. El bloqueo de tablas con particiones se puede extender al nivel HoBT para la partición asociada en lugar de hacerlo al bloqueo de la tabla.Locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. Un bloqueo de nivel HOBT no bloquea necesariamente los HoBTs alineados para la partición.A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.

Nota

Los bloqueos de nivel HOBT normalmente aumentan la simultaneidad, pero suponen un riesgo potencial de interbloqueos cuando cada una de las transacciones que están bloqueando diferentes particiones quiere ampliar sus bloqueos exclusivos a las demás particiones.HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. En algunos casos poco habituales, la granularidad de bloqueo TABLE dará mejores resultados.In rare instances, TABLE locking granularity might perform better.

Si no se produce un intento de extensión de bloqueo debido a conflictos de bloqueo retenidos por transacciones simultáneas, Motor de base de datosDatabase Engine intentará realizar de nuevo la extensión de bloqueo por cada 1250 bloqueos adicionales adquiridos por la transacción.If a lock escalation attempt fails because of conflicting locks held by concurrent transactions, the Motor de base de datosDatabase Engine will retry the lock escalation for each additional 1,250 locks acquired by the transaction.

Cada evento de extensión funciona principalmente en una sola instrucción Transact-SQLTransact-SQL.Each escalation event operates primarily at the level of a single Transact-SQLTransact-SQL statement. Cuando el evento se inicia, Motor de base de datosDatabase Engine intenta extender todos los bloqueos propiedad de la transacción actual que se encuentren en alguna de las tablas a las que se hace referencia en la instrucción activa, siempre que cumpla los requisitos de umbral de extensión.When the event starts, the Motor de base de datosDatabase Engine attempts to escalate all the locks owned by the current transaction in any of the tables that have been referenced by the active statement provided it meets the escalation threshold requirements. Si el evento de concentración se inicia antes de que la instrucción tenga acceso a la tabla, no se hace ningún intento de concentrar los bloqueos de la tabla.If the escalation event starts before the statement has accessed a table, no attempt is made to escalate the locks on that table. Si la extensión de bloqueo se realiza correctamente, los bloqueos adquiridos por la transacción en una instrucción anterior que sigan retenidos en el momento en que se inicia el evento se concentrarán, si se hace referencia a la tabla en la instrucción actual y se incluye la tabla en el evento de concentración.If lock escalation succeeds, any locks acquired by the transaction in a previous statement and still held at the time the event starts will be escalated if the table is referenced by the current statement and is included in the escalation event.

Por ejemplo, suponga que la sesión realiza estas operaciones:For example, assume that a session performs these operations:

  • Inicia una transacción.Begins a transaction.
  • Actualiza TableA.Updates TableA. Esto genera bloqueos exclusivos de fila en TableA que se retienen hasta que se completa la transacción.This generates exclusive row locks in TableA that are held until the transaction completes.
  • Actualiza TableB.Updates TableB. Esto genera bloqueos exclusivos de fila en TableB que se retienen hasta que se completa la transacción.This generates exclusive row locks in TableB that are held until the transaction completes.
  • Realiza una operación SELECT que combina TableA con TableC.Performs a SELECT that joins TableA with TableC. El plan de ejecución de la consulta llama a las filas que se van a recuperar de TableA antes de que se recuperen las de TableC.The query execution plan calls for the rows to be retrieved from TableA before the rows are retrieved from TableC.
  • La instrucción SELECT desencadena la extensión de bloqueo mientras recupera filas de TableA y antes de acceder a TableC.The SELECT statement triggers lock escalation while it is retrieving rows from TableA and before it has accessed TableC.

Si la extensión de bloqueo se realiza correctamente, solo se extienden los bloqueos retenidos por la sesión en TableA.If lock escalation succeeds, only the locks held by the session on TableA are escalated. Esto incluye los bloqueos compartidos de la instrucción SELECT y los bloqueos exclusivos de la instrucción UPDATE anterior.This includes both the shared locks from the SELECT statement and the exclusive locks from the previous UPDATE statement. Aunque para determinar si se debe realizar la extensión de bloqueo solo se cuentan los bloqueos que la sesión ha adquirido en TableA para la instrucción SELECT, cuando la extensión se realiza correctamente, todos los bloqueos retenidos por la sesión en TableA se extienden a un bloqueo exclusivo en la tabla y se liberan los demás bloqueos de granularidad inferior, incluidos los bloqueos preventivos, de TableA.While only the locks the session acquired in TableA for the SELECT statement are counted to determine if lock escalation should be done, once escalation is successful all locks held by the session in TableA are escalated to an exclusive lock on the table, and all other lower-granularity locks, including intent locks, on TableA are released.

No se intenta extender los bloqueos de TableB porque no había una referencia activa a TableB en la instrucción SELECT.No attempt is made to escalate locks on TableB because there was no active reference to TableB in the SELECT statement. De manera similar, no se intenta extender los bloqueos de TableC, ya que no están extendidos porque todavía no se había accedido a esta tabla cuando se produjo la extensión.Similarly no attempt is made to escalate the locks on TableC, which are not escalated because it had not yet been accessed when the escalation occurred.

Umbrales de extensión de bloqueoLock Escalation Thresholds

La extensión de bloqueo se activa cuando esa operación no se ha deshabilitado en la tabla mediante la opción ALTER TABLE SET LOCK_ESCALATION y cuando se da cualquiera de las condiciones siguientes:Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:

  • Una sola instrucción Transact-SQLTransact-SQL adquiere al menos 5 000 bloqueos en un solo índice o tabla sin particiones.A single Transact-SQLTransact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
  • Una sola instrucción Transact-SQLTransact-SQL adquiere al menos 5 000 bloqueos en una sola partición de una tabla o índice con particiones, y la opción ALTER TABLE SET LOCK_ESCALATION está establecida en AUTO.A single Transact-SQLTransact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
  • El número de bloqueos en una instancia de Motor de base de datosDatabase Engine supera los umbrales de memoria o de configuración.The number of locks in an instance of the Motor de base de datosDatabase Engine exceeds memory or configuration thresholds.

Si los bloqueos no se pueden extender debido a conflictos de bloqueo, Motor de base de datosDatabase Engine desencadena periódicamente la extensión de bloqueo cada 1250 nuevos bloqueos adquiridos.If locks cannot be escalated because of lock conflicts, the Motor de base de datosDatabase Engine periodically triggers lock escalation at every 1,250 new locks acquired.

Umbral de concentración para una instrucción de Transact-SQLEscalation Threshold for a Transact-SQL Statement

Cuando Motor de base de datosDatabase Engine comprueba si hay posibles extensiones cada 1250 bloqueos recién adquiridos, se producirá una extensión de bloqueo solo si una instrucción Transact-SQLTransact-SQL ha adquirido al menos 5 000 bloqueos en una única referencia de una tabla.When the Motor de base de datosDatabase Engine checks for possible escalations at every 1,250 newly acquired locks, a lock escalation will occur if and only if a Transact-SQLTransact-SQL statement has acquired at least 5,000 locks on a single reference of a table. La extensión de bloqueo se desencadena cuando una instrucción Transact-SQLTransact-SQL adquiere al menos 5 000 bloqueos en una única referencia de una tabla.Lock escalation is triggered when a Transact-SQLTransact-SQL statement acquires at least 5,000 locks on a single reference of a table. Por ejemplo, la extensión de bloqueo no se desencadena si una instrucción adquiere 3.000 bloqueos en un índice y 3.000 bloqueos en otro índice de la misma tabla.For example, lock escalation is not triggered if a statement acquires 3,000 locks in one index and 3,000 locks in another index of the same table. De forma similar, la extensión de bloqueo no se desencadena si una instrucción tiene una autocombinación en una tabla y cada referencia a la tabla solo adquiere 3.000 bloqueos de la tabla.Similarly, lock escalation is not triggered if a statement has a self join on a table, and each reference to the table only acquires 3,000 locks in the table.

La extensión de bloqueo solo se produce para tablas a las que se ha obtenido acceso en el momento en que se desencadena la concentración.Lock escalation only occurs for tables that have been accessed at the time the escalation is triggered. Imagine que una única instrucción SELECT es una combinación que accede a tres tablas por este orden: TableA, TableB y TableC.Assume that a single SELECT statement is a join that accesses three tables in this sequence: TableA, TableB, and TableC. La instrucción adquiere 3 000 bloqueos de fila del índice agrupado de TableA y, al menos, 5 000 bloqueos de fila del índice agrupado de TableB, pero todavía no ha accedido a TableC.The statement acquires 3,000 row locks in the clustered index for TableA and at least 5,000 row locks in the clustered index for TableB, but has not yet accessed TableC. Cuando Motor de base de datosDatabase Engine detecta que la instrucción ha adquirido al menos 5 000 bloqueos de fila de TableB, intenta extender todos los bloqueos retenidos por la transacción actual en TableB.When the Motor de base de datosDatabase Engine detects that the statement has acquired at least 5,000 row locks in TableB, it attempts to escalate all locks held by the current transaction on TableB. También intenta extender todos los bloqueos retenidos por la transacción actual en TableA, pero como el número de bloqueos de TableA es inferior a 5 000, no se realiza la extensión.It also attempts to escalate all locks held by the current transaction on TableA, but since the number of locks on TableA is less than 5,000, the escalation will not succeed. No se intenta ninguna extensión de bloqueo en TableC porque todavía no se había accedido a ella cuando se produjo la extensión.No lock escalation is attempted for TableC because it had not yet been accessed when the escalation occurred.

Umbral de concentración para una instancia del motor de base de datosEscalation Threshold for an Instance of the Database Engine

Siempre que el número de bloqueos sea mayor que el umbral de memoria para la extensión de bloqueo, Motor de base de datosDatabase Engine desencadena la extensión de bloqueo.Whenever the number of locks is greater than the memory threshold for lock escalation, the Motor de base de datosDatabase Engine triggers lock escalation. El umbral de memoria depende del valor de la opción de configuración locks:The memory threshold depends on the setting of the locks configuration option:

  • Si la opción locks se establece en el valor predeterminado de 0, el umbral de extensión de bloqueo se alcanza cuando la memoria que usan los objetos del bloqueo es un 24 % de la que usa el Motor de base de datos, sin contar la memoria AWE.If the locks option is set to its default setting of 0, then the lock escalation threshold is reached when the memory used by lock objects is 24 percent of the memory used by the Database Engine, excluding AWE memory. La estructura de datos que se utiliza para representar un bloqueo tiene aproximadamente una longitud de 100 bytes.The data structure used to represent a lock is approximately 100 bytes long. Este umbral es dinámico debido a que el Motor de base de datos adquiere y libera memoria de forma dinámica para ajustarse a las variaciones de las cargas de trabajo.This threshold is dynamic because the Database Engine dynamically acquires and frees memory to adjust for varying workloads.

  • Si la opción locks es un valor distinto de 0, el umbral de extensión de bloqueo es 40 % (o menos si existe presión de memoria) del valor de la opción.If the locks option is a value other than 0, then the lock escalation threshold is 40 percent (or less if there is a memory pressure) of the value of the locks option.

El Motor de base de datosDatabase Engine puede elegir cualquier instrucción activa de cualquier sesión para la extensión; además, por cada 1250 nuevos bloqueos, elegirá instrucciones para la extensión siempre y cuando la memoria de bloqueo utilizada en la instancia se mantenga por encima del umbral.The Motor de base de datosDatabase Engine can choose any active statement from any session for escalation, and for every 1,250 new locks it will choose statements for escalation as long as the lock memory used in the instance remains above the threshold.

Concentrar tipos de bloqueo mixtosEscalating Mixed Lock Types

Cuando se produce una extensión de bloqueo, el bloqueo seleccionado para el montón o índice es lo suficientemente fuerte como para cumplir con los requisitos del bloqueo de bajo nivel más restrictivo.When lock escalation occurs, the lock selected for the heap or index is strong enough to meet the requirements of the most restrictive lower level lock.

Por ejemplo, suponga que una sesión:For example, assume a session:

  • Inicia una transacción.Begins a transaction.
  • Actualiza una tabla que contiene un índice clúster.Updates a table containing a clustered index.
  • Emite una instrucción SELECT que hace referencia a la misma tabla.Issues a SELECT statement that references the same table.

La instrucción UPDATE adquiere estos bloqueos:The UPDATE statement acquires these locks:

  • Bloqueos exclusivos (X) en las filas de datos actualizadas.Exclusive (X) locks on the updated data rows.
  • Bloqueos con intención exclusivos (IX) en las páginas del índice clúster que contienen estas filas.Intent exclusive (IX) locks on the clustered index pages containing those rows.
  • Un bloqueo IX en el índice clúster y otro en la tabla.An IX lock on the clustered index and another on the table.

La instrucción SELECT adquiere estos bloqueos:The SELECT statement acquires these locks:

  • Bloqueos compartidos (S) en todas las filas de datos que lee, a no ser que la fila esté ya protegida por un bloqueo X de la instrucción UPDATE.Shared (S) locks on all data rows it reads, unless the row is already protected by an X lock from the UPDATE statement.
  • Bloqueos con intención compartidos en todas las páginas de índice clúster que contienen dichas filas, a no ser que la página esté ya protegida por un bloqueo IX.Intent Share locks on all clustered index pages containing those rows, unless the page is already protected by an IX lock.
  • Ningún bloqueo en el índice clúster o la tabla porque ya están protegidos por bloqueos IX.No lock on the clustered index or table because they are already protected by IX locks.

Si la instrucción SELECT adquiere suficientes bloqueos como para desencadenar la extensión de bloqueo y ésta se realiza correctamente, el bloqueo IX de la tabla se convierte en un bloqueo X y se liberan todos los bloqueos de fila, página e índice.If the SELECT statement acquires enough locks to trigger lock escalation and the escalation succeeds, the IX lock on the table is converted to an X lock, and all the row, page, and index locks are freed. Las actualizaciones y las lecturas están protegidas por el bloqueo X de la tabla.Both the updates and reads are protected by the X lock on the table.

Reducir bloqueos y extensionesReducing Locking and Escalation

En la mayoría de los casos, Motor de base de datosDatabase Engine presta el mejor rendimiento cuando funciona con la configuración predeterminada de bloqueo y extensión de bloqueo.In most cases, the Motor de base de datosDatabase Engine delivers the best performance when operating with its default settings for locking and lock escalation. Si una instancia de Motor de base de datosDatabase Engine genera gran cantidad de bloqueos y se producen extensiones de bloqueo frecuentes, considere la posibilidad de reducir la cantidad de bloqueos mediante:If an instance of the Motor de base de datosDatabase Engine generates a lot of locks and is seeing frequent lock escalations, consider reducing the amount of locking by:

  • El uso de un nivel de aislamiento que no genere bloqueos compartidos para las operaciones de lectura:Using an isolation level that does not generate shared locks for read operations:

    • Un nivel de aislamiento READ COMMITTED si la opción de base de datos READ_COMMITTED_SNAPSHOT está en ON.READ COMMITTED isolation level when the READ_COMMITTED_SNAPSHOT database option is ON.
    • Un nivel de aislamiento SNAPSHOT.SNAPSHOT isolation level.
    • Un nivel de aislamiento READ UNCOMMITTED.READ UNCOMMITTED isolation level. Este nivel solo se puede utilizar en sistemas que puedan funcionar con lecturas no actualizadas.This can only be used for systems that can operate with dirty reads.

    Nota

    Cambiar el nivel de aislamiento afecta a todas las tablas en la instancia de Motor de base de datosDatabase Engine.Changing the isolation level affects all tables on the instance of the Motor de base de datosDatabase Engine.

  • El uso de las sugerencias de tabla PAGLOCK o TABLOCK para que el Motor de base de datos utilice bloqueos de página, montón o índice en lugar de bloqueos de filas.Using the PAGLOCK or TABLOCK table hints to have the Database Engine use page, heap, or index locks instead of row locks. Esta opción, sin embargo, aumenta los problemas derivados de usuarios que bloquean a otros usuarios al intentar obtener acceso a los mismos datos y solo se debe utilizar en sistemas con pocos usuarios simultáneos.Using this option, however, increases the problems of users blocking other users attempting to access the same data and should not be used in systems with more than a few concurrent users.

  • Para las tablas con particiones, use la opción LOCK_ESCALATION de ALTER TABLE para extender los bloqueos al nivel HoBT en lugar de hacerlo al nivel de la tabla, o bien para deshabilitar la extensión de bloqueo.For partitioned tables, use the LOCK_ESCALATION option of ALTER TABLE to escalate locks to the HoBT level instead of the table or to disable lock escalation.

  • Divida las operaciones en lote grandes en varias operaciones más pequeñas.Break up large batch operations into several smaller operations. Por ejemplo, imagine que ha ejecutado la consulta siguiente para quitar varios cientos de miles de registros antiguos de una tabla de auditoría y, después, ha comprobado que se ha producido una extensión de bloqueo que ha bloqueado a otros usuarios:For example, suppose you ran the following query to remove several hundred thousand old records from an audit table, and then you found that it caused a lock escalation that blocked other users:

    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    

    Si quita varios cientos de estos registros cada vez, puede reducir drásticamente el número de bloqueos que se acumulan por transacción y evitar la extensión de bloqueo.By removing these records a few hundred at a time, you can dramatically reduce the number of locks that accumulate per transaction and prevent lock escalation. Por ejemplo:For example:

    SET ROWCOUNT 500
    delete_more:
      DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
    
  • Reduzca la superficie de bloqueo de una consulta; para ello, intente que la consulta sea lo más eficaz posible.Reduce a query's lock footprint by making the query as efficient as possible. Los recorridos grandes o un gran número de búsquedas de marcadores pueden aumentar la posibilidad de que se necesite la extensión de bloqueo; además, aumenta la posibilidad de los interbloqueos y, por lo general, afecta de manera negativa a la simultaneidad y el rendimiento.Large scans or large numbers of Bookmark Lookups may increase the chance of lock escalation; additionally, it increases the chance of deadlocks, and generally adversely affects concurrency and performance. Después de encontrar la consulta que provoca la extensión de bloqueo, busque oportunidades para crear índices o agregar columnas a un índice existente a fin de quitar los recorridos de índice o de tabla, y maximizar la eficacia de las búsquedas de índice.After you find the query that causes lock escalation, look for opportunities to create new indexes or to add columns to an existing index to remove index or table scans and to maximize the efficiency of index seeks. Considere la posibilidad de usar el Asistente para la optimización de motor de base de datos para realizar un análisis automático de índices en la consulta.Consider using the Database Engine Tuning Advisor to perform an automatic index analysis on the query. Para más información, consulte el Tutorial: Asistente para la optimización de motor de base de datos.For more information, see Tutorial: Database Engine Tuning Advisor. Un objetivo de esta optimización es hacer que las búsquedas de índice devuelvan el menor número posible de filas para minimizar el costo de las búsquedas de marcadores (se maximiza la selectividad del índice para esa consulta concreta).One goal of this optimization is to make index seeks return as few rows as possible to minimize the cost of Bookmark Lookups (maximize the selectivity of the index for the particular query). Si Motor de base de datosDatabase Engine estima que un operador lógico de búsqueda de marcadores puede devolver muchas filas, puede usar una instrucción PREFETCH para realizar la búsqueda de marcadores.If the Motor de base de datosDatabase Engine estimates that a Bookmark Lookup logical operator may return many rows, it may use a PREFETCH to perform the bookmark lookup. Si Motor de base de datosDatabase Engine usa PREFETCH para una búsqueda de marcadores, debe aumentar el nivel de aislamiento de transacción de una parte de la consulta para leer de forma repetida una parte de la consulta.If the Motor de base de datosDatabase Engine does use PREFETCH for a bookmark lookup, it must increase the transaction isolation level of a portion of the query to repeatable read for a portion of the query. Esto significa que lo que puede parecer similar a una instrucción SELECT en un nivel de aislamiento de lectura confirmada puede adquirir muchos miles de bloqueos de clave (tanto en el índice agrupado como en el no agrupado), lo que puede hacer que esa consulta supere los umbrales de extensión de bloqueo.This means that what may look similar to a SELECT statement at a read-committed isolation level may acquire many thousands of key locks (on both the clustered index and one nonclustered index), which can cause such a query to exceed the lock escalation thresholds. Esto es especialmente importante si observa que el bloqueo extendido es un bloqueo de tabla compartido que no se suele ver en el nivel de aislamiento predeterminado de lectura confirmada.This is especially important if you find that the escalated lock is a shared table lock, which, however, is not commonly seen at the default read-committed isolation level. Si una búsqueda de marcadores con una cláusula PREFETCH es el origen de la extensión, considere la posibilidad de agregar columnas adicionales al índice no agrupado que aparece en el operador lógico de Index Seek o Index Scan debajo del operador lógico de Bookmark Lookup en el plan de consulta.If a Bookmark Lookup WITH PREFETCH clause is causing the escalation, consider adding additional columns to the nonclustered index that appears in the Index Seek or the Index Scan logical operator below the Bookmark Lookup logical operator in the query plan. Puede crear un índice de cobertura (que incluya todas las columnas de una tabla que se han usado en la consulta), o al menos un índice que abarque las columnas que se han usado para los criterios de combinación o en la cláusula WHERE si no es práctico incluir todo en la lista de columnas de selección.It may be possible to create a covering index (an index that includes all columns in a table that were used in the query), or at least an index that covers the columns that were used for join criteria or in the WHERE clause if including everything in the select column list is impractical. Una combinación de bucle anidado también puede usar PREFETCH, lo que provoca el mismo comportamiento de bloqueo.A Nested Loop join may also use PREFETCH, and this causes the same locking behavior.

  • No se puede realizar la extensión de bloqueo si otro SPID contiene un bloqueo de tabla no compatible.Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock. La extensión de bloqueo siempre se extiende a un bloqueo de tabla y nunca a bloqueos de página.Lock escalation always escalates to a table lock, and never to page locks. Además, si se produce un error en un intento de extensión de bloqueo porque otro SPID contiene un bloqueo TAB no compatible, la consulta que ha intentado la extensión no se bloquea mientras espera un bloqueo TAB.Additionally, if a lock escalation attempt fails because another SPID holds an incompatible TAB lock, the query that attempted escalation does not block while waiting for a TAB lock. En su lugar, continúa con la adquisición de bloqueos en su nivel original y más granular (fila, clave o página), realizando periódicamente más intentos de extensión.Instead, it continues to acquire locks at its original, more granular level (row, key, or page), periodically making additional escalation attempts. Por tanto, un método para evitar la extensión de bloqueo en una tabla concreta consiste en adquirir y mantener un bloqueo en otra conexión que no sea compatible con el tipo de bloqueo extendido.Therefore, one method to prevent lock escalation on a particular table is to acquire and to hold a lock on a different connection that is not compatible with the escalated lock type. Un bloqueo IX (preventivo exclusivo) en el nivel de tabla no bloquea ninguna fila o página, pero todavía no es compatible con un bloqueo TAB S (compartido) o X (exclusivo).An IX (intent exclusive) lock at the table level does not lock any rows or pages, but it is still not compatible with an escalated S (shared) or X (exclusive) TAB lock. Por ejemplo, imagine que tiene que ejecutar un trabajo por lotes que modifique un gran número de filas en la tabla mytable y que haya provocado el bloqueo debido a la extensión de bloqueo.For example, assume that you must run a batch job that modifies a large number of rows in the mytable table and that has caused blocking that occurs because of lock escalation. Si este trabajo se completa siempre en menos de una hora, puede crear un trabajo de Transact-SQLTransact-SQL que contenga el código siguiente y programar el nuevo trabajo para que se inicie varios minutos antes del trabajo por lotes:If this job always completes in less than an hour, you might create a Transact-SQLTransact-SQL job that contains the following code, and schedule the new job to start several minutes before the batch job's start time:

    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN
    

    Esta consulta adquiere y mantiene un bloqueo IX en mytable durante una hora, lo que evita la extensión de bloqueo en la tabla durante ese tiempo.This query acquires and holds an IX lock on mytable for one hour, which prevents lock escalation on the table during that time. Este lote no modifica ningún dato ni bloquea otras consultas (a menos que la otra consulta fuerce un bloqueo de tabla con la sugerencia TABLOCK, o bien si un administrador ha deshabilitado los bloqueos de página o fila mediante un procedimiento almacenado sp_indexoption).This batch does not modify any data or block other queries (unless the other query forces a table lock with the TABLOCK hint or if an administrator has disabled page or row locks by using an sp_indexoption stored procedure).

También se pueden utilizar las marcas de seguimiento 1211 y 1224 para deshabilitar todas las extensiones de bloqueo o solo algunas.You can also use trace flags 1211 and 1224 to disable all or some lock escalations. Pero estas marcas de seguimiento deshabilitan toda la extensión de bloqueo globalmente para la totalidad de Motor de base de datosDatabase Engine.However, these trace flags disable all lock escalation globally for the entire Motor de base de datosDatabase Engine. La extensión de bloqueo tiene una finalidad muy útil en Motor de base de datosDatabase Engine, ya que maximiza la eficacia de las consultas que de otro modo se ralentizan por la sobrecarga de la adquisición y liberación de varios miles de bloqueos.Lock escalation serves a very useful purpose in the Motor de base de datosDatabase Engine by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. La extensión de bloqueo también ayuda a minimizar la memoria necesaria para realizar el seguimiento de los bloqueos.Lock escalation also helps to minimize the required memory to keep track of locks. La memoria que Motor de base de datosDatabase Engine puede asignar de forma dinámica para las estructuras de bloqueo es finita, por lo que si deshabilita la extensión de bloqueo y la memoria de bloqueo crece lo suficiente, se pueden producir errores en los intentos de asignar bloqueos adicionales a cualquier consulta y se produce el siguiente error:The memory that the Motor de base de datosDatabase Engine can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail and the following error occurs:

The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

Nota

Cuando se produce el error 1204, se detiene el procesamiento de la instrucción actual y se provoca la reversión de la transacción activa.When error 1204 occurs, it stops the processing of the current statement and causes a rollback of the active transaction. La propia reversión puede bloquear a los usuarios o generar un tiempo de recuperación largo de la base de datos si se reinicia el servicio de base de datos.The rollback itself may block users or lead to a long database recovery time if you restart the database service.

Nota

El uso de una sugerencia de bloqueo como ROWLOCK solo altera el plan de bloqueo inicial.Using a lock hint such as ROWLOCK only alters the initial lock plan. Las sugerencias de bloqueo no impiden la extensión de bloqueo.Lock hints do not prevent lock escalation.

Además, supervise la extensión de bloqueo mediante el evento extendido lock_escalation (xEvent), como en el ejemplo siguiente:Also, monitor lock escalation by using the lock_escalation Extended Event (xEvent), such as in the following example:

-- Session creates a histogram of the number of lock escalations per database 
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER 
ADD EVENT sqlserver.lock_escalation(SET collect_database_name=(1),collect_statement=(1)
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.histogram(SET source=N'sqlserver.database_id')
GO

Importante

El evento extendido lock_escalation (xEvent) se debe usar en lugar de la clase de eventos Lock:Escalation en Seguimiento SQL o SQL ProfilerThe lock_escalation Extended Event (xEvent) should be used instead of the Lock:Escalation event class in SQL Trace or SQL Profiler

Bloqueo dinámicoDynamic Locking

La utilización de bloqueos de bajo nivel, como los de fila, aumenta la simultaneidad reduciendo la probabilidad de que dos transacciones soliciten bloqueos de los mismos datos al mismo tiempo.Using low-level locks, such as row locks, increases concurrency by decreasing the probability that two transactions will request locks on the same piece of data at the same time. También aumenta el número de bloqueos y los recursos necesarios para administrarlos.Using low-level locks also increases the number of locks and the resources needed to manage them. Los bloqueos de alto nivel de tabla o página producen una sobrecarga menor, pero a costa de reducir la simultaneidad.Using high-level table or page locks lowers overhead, but at the expense of lowering concurrency.

Costo de bloqueo frente a costo de simultaneidad

El Motor de base de datos de SQL ServerSQL Server Database Engine utiliza una estrategia de bloqueo dinámico para determinar los bloqueos que son más eficaces.The Motor de base de datos de SQL ServerSQL Server Database Engine uses a dynamic locking strategy to determine the most cost-effective locks. El Motor de base de datos de SQL ServerSQL Server Database Engine determina automáticamente los bloqueos más apropiados cuando se ejecuta la consulta, basándose en las características del esquema y de la consulta.The Motor de base de datos de SQL ServerSQL Server Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query. Por ejemplo, para reducir la sobrecarga de bloqueos, el optimizador puede decidir la realización de bloqueos de página en un índice al realizar un recorrido del índice.For example, to reduce the overhead of locking, the optimizer may choose page-level locks in an index when performing an index scan.

El bloqueo dinámico presenta las ventajas siguientes:Dynamic locking has the following advantages:

  • Administración simplificada de la base de datos.Simplified database administration. Los administradores de bases de datos no tienen que preocuparse de ajustar los umbrales de extensión de bloqueo.Database administrators do not have to adjust lock escalation thresholds.
  • Mayor rendimiento.Increased performance. El Motor de base de datos de SQL ServerSQL Server Database Engine minimiza la sobrecarga del sistema al utilizar los bloqueos apropiados para la tarea.The Motor de base de datos de SQL ServerSQL Server Database Engine minimizes system overhead by using locks appropriate to the task.
  • Los programadores de aplicaciones se pueden concentrar en la programación.Application developers can concentrate on development. El Motor de base de datos de SQL ServerSQL Server Database Engine ajusta los bloqueos automáticamente.The Motor de base de datos de SQL ServerSQL Server Database Engine adjusts locking automatically.

A partir de SQL Server 2008SQL Server 2008, el comportamiento de la extensión de bloqueo ha cambiado con la introducción de la opción LOCK_ESCALATION.Starting with SQL Server 2008SQL Server 2008, the behavior of lock escalation has changed with the introduction of the LOCK_ESCALATION option. Para obtener más información, vea la opción LOCK_ESCALATION de ALTER TABLE.For more information, see the LOCK_ESCALATION option of ALTER TABLE.

InterbloqueosDeadlocks

Un interbloqueo se produce cuando dos o más tareas se bloquean entre sí permanentemente teniendo cada tarea un bloqueo en un recurso que las otras tareas intentan bloquear.A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. Por ejemplo:For example:

  • La transacción A adquiere un bloqueo compartido de la fila 1.Transaction A acquires a shared lock on row 1.
  • La transacción B adquiere un bloqueo compartido de la fila 2.Transaction B acquires a shared lock on row 2.
  • Ahora la transacción A solicita un bloqueo exclusivo de la fila 2 y se bloquea hasta que la transacción B finalice y libere el bloqueo compartido que tiene de la fila 2.Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the shared lock it has on row 2.
  • Ahora la transacción B solicita un bloqueo exclusivo de la fila 1 y se bloquea hasta que la transacción A finalice y libere el bloqueo compartido que tiene de la fila 1.Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the shared lock it has on row 1.

La transacción A no se puede completar hasta que se complete la transacción B, pero la transacción B está bloqueada por la transacción A. Esta condición también se denomina una dependencia cíclica: La transacción A tiene una dependencia de la transacción B y la transacción B cierra el círculo con una dependencia en la transacción A.Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

Ambas transacciones con un interbloqueo esperarán para siempre, a no ser que un proceso externo rompa el interbloqueo.Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. La supervisión de interbloqueos del Motor de base de datos de SQL ServerSQL Server Database Engine comprueba periódicamente si hay tareas con un interbloqueo.The Motor de base de datos de SQL ServerSQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. Si el monitor detecta una dependencia cíclica, elige una de las tareas como el sujeto y finaliza su transacción con un error.If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. Esto permite a la otra tarea completar su transacción.This allows the other task to complete its transaction. La aplicación con la transacción que terminó con un error puede reintentar la transacción, que suele completarse después de que la otra transacción interbloqueada haya finalizado.The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

A menudo se confunden los interbloqueos con los bloqueos normales.Deadlocking is often confused with normal blocking. Cuando una transacción solicita un bloqueo en un recurso bloqueado por otra transacción, la transacción solicitante espera hasta que se libere el bloqueo.When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. De forma predeterminada, las transacciones de SQL ServerSQL Server no tienen tiempo de espera, a menos que se establezca LOCK_TIMEOUT.By default, SQL ServerSQL Server transactions do not time out, unless LOCK_TIMEOUT is set. La transacción solicitante está bloqueada, no interbloqueada, porque la transacción solicitante no ha hecho nada para bloquear la transacción a la que pertenece el bloqueo.The requesting transaction is blocked, not deadlocked, because the requesting transaction has not done anything to block the transaction owning the lock. Finalmente, la transacción a la que pertenece el bloqueo se completará y liberará el bloqueo, y a la transacción solicitante se le concederá el bloqueo y continuará.Eventually, the owning transaction will complete and release the lock, and then the requesting transaction will be granted the lock and proceed.

Nota

A veces, los interbloqueos se denominan "abrazo mortal".Deadlocks are sometimes called a deadly embrace.

Un interbloqueo es una condición que se puede dar en cualquier sistema con varios subprocesos, no solo en un sistema de administración de bases de datos relacionales, y puede producirse para recursos distintos a los bloqueos en objetos de base de datos.Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. Por ejemplo, un subproceso en un sistema operativo con varios subprocesos puede adquirir uno o más recursos, como bloqueos de memoria.For example, a thread in a multithreaded operating system might acquire one or more resources, such as blocks of memory. Si el recurso que se desea adquirir pertenece actualmente a otro subproceso, puede que el primer subproceso deba esperar a que el otro libere el recurso de destino.If the resource being acquired is currently owned by another thread, the first thread may have to wait for the owning thread to release the target resource. En consecuencia, se dice que el subproceso que está en espera depende del subproceso que posee ese recurso concreto.The waiting thread is said to have a dependency on the owning thread for that particular resource. En una instancia del Motor de base de datos de SQL ServerSQL Server Database Engine, las sesiones pueden interbloquearse cuando adquieren recursos ajenos a la base de datos, como memoria o subprocesos.In an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine, sessions can deadlock when acquiring nondatabase resources, such as memory or threads.

Diagrama en el que se muestra un interbloqueo de transacciones

En la ilustración, la transacción T1 tiene una dependencia de la transacción T2 para el recurso de bloqueo de la tabla Part.In the illustration, transaction T1 has a dependency on transaction T2 for the Part table lock resource. Del mismo modo, la transacción T2 tiene una dependencia de la transacción T1 para el recurso de bloqueo de la tabla Supplier.Similarly, transaction T2 has a dependency on transaction T1 for the Supplier table lock resource. Puesto que estas dependencias forman un ciclo, hay un interbloqueo entre las transacciones T1 y T2.Because these dependencies form a cycle, there is a deadlock between transactions T1 and T2.

Los interbloqueos también se pueden producir cuando se crean particiones en una tabla y el valor de LOCK_ESCALATION de ALTER TABLE se fija en AUTO.Deadlocks can also occur when a table is partitioned and the LOCK_ESCALATION setting of ALTER TABLE is set to AUTO. Cuando LOCK_ESCALATION es AUTO, la simultaneidad aumenta permitiendo a Motor de base de datos de SQL ServerSQL Server Database Engine bloquear las particiones de la tabla en el nivel HoBT en lugar de en el nivel de tabla.When LOCK_ESCALATION is set to AUTO, concurrency increases by allowing the Motor de base de datos de SQL ServerSQL Server Database Engine to lock table partitions at the HoBT level instead of at the table level. Sin embargo, cuando transacciones independientes mantienen bloqueos de partición en una tabla y desean un bloqueo en algún punto de la partición de otras transacciones, se produce un interbloqueo.However, when separate transactions hold partition locks in a table and want a lock somewhere on the other transactions partition, this causes a deadlock. Este tipo de interbloqueo se puede evitar asignando LOCK_ESCALATION a TABLE; aunque este valor reducirá la simultaneidad obligando a las grandes actualizaciones de las particiones a esperar a que se produzca un bloqueo de la tabla.This type of deadlock can be avoided by setting LOCK_ESCALATION to TABLE; although this setting will reduce concurrency by forcing large updates to a partition to wait for a table lock.

Detectar y finalizar interbloqueosDetecting and Ending Deadlocks

Un interbloqueo se produce cuando dos o más tareas se bloquean entre sí permanentemente teniendo cada tarea un bloqueo en un recurso que las otras tareas intentan bloquear.A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. En el siguiente gráfico se presenta una vista de alto nivel de un estado de interbloqueo donde:The following graph presents a high level view of a deadlock state where:

  • La tarea T1 tiene un bloqueo en el recurso R1 (indicado por la flecha de R1 a T1) y ha solicitado un bloqueo en el recurso R2 (indicado por la flecha de T1 a R2).Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).
  • La tarea T2 tiene un bloqueo en el recurso R2 (indicado por la flecha de R2 a T2) y ha solicitado un bloqueo en el recurso R1 (indicado por la flecha de T2 a R1).Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).
  • Dado que ninguna tarea puede continuar hasta que un recurso esté disponible y ningún recurso puede liberarse hasta que continúe una tarea, existe un estado de interbloqueo.Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

Diagrama en el que se muestran tareas en un estado de interbloqueo

El Motor de base de datos de SQL ServerSQL Server Database Engine detecta automáticamente los ciclos de interbloqueo en SQL ServerSQL Server.The Motor de base de datos de SQL ServerSQL Server Database Engine automatically detects deadlock cycles within SQL ServerSQL Server. El Motor de base de datos de SQL ServerSQL Server Database Engine elige una de las sesiones como sujeto del interbloqueo y la transacción actual finaliza con un error para romper el interbloqueo.The Motor de base de datos de SQL ServerSQL Server Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.

Recursos que pueden causar interbloqueosResources that can Deadlock

Cada sesión de usuario puede tener una o más tareas en ejecución y cada tarea puede adquirir o esperar para adquirir una serie de recursos.Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. Los siguientes tipos de recursos pueden causar bloqueos que podrían dar como resultado un interbloqueo.The following types of resources can cause blocking that could result in a deadlock.

  • Bloqueos.Locks. Esperar para adquirir bloqueos en recursos, como objetos, páginas, filas, metadatos y aplicaciones, puede causar un interbloqueo.Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock. Por ejemplo, la transacción T1 tiene un bloqueo compartido (S) en la fila f1 y está esperando para obtener un bloqueo exclusivo (X) en f2.For example, transaction T1 has a shared (S) lock on row r1 and is waiting to get an exclusive (X) lock on r2. La transacción T2 tiene un bloqueo compartido (S) en f2 y está esperando para obtener un bloqueo exclusivo (X) en la fila f1.Transaction T2 has a shared (S) lock on r2 and is waiting to get an exclusive (X) lock on row r1. Esta situación tiene como resultado un ciclo de bloqueo en el que T1 y T2 esperan que la otra transacción libere los recursos bloqueados.This results in a lock cycle in which T1 and T2 wait for each other to release the locked resources.

  • Subprocesos de trabajo.Worker threads. Una tarea en cola que espera un subproceso de trabajo disponible puede causar un interbloqueo.A queued task waiting for an available worker thread can cause deadlock. Si la tarea en cola es propietaria de recursos que están bloqueando todos los subprocesos de trabajo, se generará un interbloqueo.If the queued task owns resources that are blocking all worker threads, a deadlock will result. Por ejemplo, la sesión S1 inicia una transacción y adquiere un bloqueo compartido (S) en la fila f1 y, a continuación, se suspende.For example, session S1 starts a transaction and acquires a shared (S) lock on row r1 and then goes to sleep. Las sesiones activas que se ejecutan en todos los subprocesos de trabajo disponibles intentan adquirir bloqueos exclusivos (X) en la fila f1.Active sessions running on all available worker threads are trying to acquire exclusive (X) locks on row r1. Dado que la sesión S1 no puede adquirir un subproceso de trabajo, no puede confirmar la transacción y liberar el bloqueo de la fila f1.Because session S1 cannot acquire a worker thread, it cannot commit the transaction and release the lock on row r1. Esta situación tiene como resultado un interbloqueo.This results in a deadlock.

  • Memoria.Memory. Cuando hay solicitudes simultáneas esperando concesiones de memoria que no se pueden satisfacer con la memoria disponible, puede producirse un interbloqueo.When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur. Por ejemplo, dos consultas simultáneas, C1 y C2, se ejecutan como funciones definidas por el usuario que adquieren 10 MB y 20 MB de memoria respectivamente.For example, two concurrent queries, Q1 and Q2, execute as user-defined functions that acquire 10MB and 20MB of memory respectively. Si cada consulta necesita 30 MB y el total de memoria disponible es 20 MB, C1 y C2 tienen que esperar a que la otra consulta libere memoria, y esta situación tiene como resultado un interbloqueo.If each query needs 30MB and the total available memory is 20MB, then Q1 and Q2 must wait for each other to release memory, and this results in a deadlock.

  • Recursos relacionados con la ejecución de consultas en paralelo.Parallel query execution-related resources. Los subprocesos de coordinador, productor o consumidor asociados a un puerto de intercambio se pueden bloquear entre sí y provocar un interbloqueo si incluyen al menos otro proceso que no forma parte de la consulta en paralelo.Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. Además, cuando se inicia la ejecución de una consulta en paralelo, SQL ServerSQL Server determina el grado de paralelismo, o el número de subprocesos de trabajo, en función de la carga de trabajo actual.Also, when a parallel query starts execution, SQL ServerSQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. Si la carga de trabajo del sistema cambia de forma inesperada, por ejemplo, si se empiezan a ejecutar nuevas consultas en el servidor o el sistema se queda sin subprocesos de trabajo, se puede producir un interbloqueo.If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.

  • Conjuntos de resultados activos múltiples (MARS) .Multiple Active Result Sets (MARS) resources. Estos recursos se utilizan para controlar la intercalación de varias solicitudes activas en MARS.These resources are used to control interleaving of multiple active requests under MARS. Para obtener más información, vea Utilizar conjuntos de resultados activos múltiples (MARS).For more information, see Using Multiple Active Result Sets (MARS).

    • Recurso de usuario.User resource. Cuando un subproceso espera un recurso que potencialmente está controlado por una aplicación de usuario, se considera que el recurso es externo o de usuario y se trata como un bloqueo.When a thread is waiting for a resource that is potentially controlled by a user application, the resource is considered to be an external or user resource and is treated like a lock.

    • Exclusión mutua de sesión.Session mutex. Las tareas que se ejecutan en una sesión se intercalan, lo que significa que solo puede ejecutarse una tarea en la sesión en un momento dado.The tasks running in one session are interleaved, meaning that only one task can run under the session at a given time. Antes de que se pueda ejecutar la tarea, debe tener acceso exclusivo a la exclusión mutua de sesión.Before the task can run, it must have exclusive access to the session mutex.

    • Exclusión mutua de transacción.Transaction mutex. Todas las tareas que se ejecutan en una transacción se intercalan, lo que significa que solo puede ejecutarse una tarea en la transacción en un momento dado.All tasks running in one transaction are interleaved, meaning that only one task can run under the transaction at a given time. Antes de que se pueda ejecutar la tarea, debe tener acceso exclusivo a la exclusión mutua de transacción.Before the task can run, it must have exclusive access to the transaction mutex.

    Para que una tarea se ejecute en MARS, debe adquirir la exclusión mutua de sesión.In order for a task to run under MARS, it must acquire the session mutex. Si la tarea se ejecuta en una transacción, debe adquirir la exclusión mutua de transacción.If the task is running under a transaction, it must then acquire the transaction mutex. Esto garantiza que solo una tarea esté activa en un momento dado en una sesión determinada y en una transacción concreta.This guarantees that only one task is active at one time in a given session and a given transaction. Una vez adquiridas las exclusiones mutuas necesarias, se puede ejecutar la tarea.Once the required mutexes have been acquired, the task can execute. Cuando finaliza la tarea, o se produce en medio de la solicitud, primero liberará la exclusión mutua de transacción seguida de la exclusión mutua de sesión en el orden inverso a la adquisición.When the task finishes, or yields in the middle of the request, it will first release transaction mutex followed by the session mutex in reverse order of acquisition. Sin embargo, pueden producirse interbloqueos con estos recursos.However, deadlocks can occur with these resources. En el ejemplo de código siguiente hay dos tareas, la solicitud de usuario U1 y la solicitud de usuario U2, que se ejecutan en la misma sesión.In the following code example, two tasks, user request U1 and user request U2, are running in the same session.

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");  
    U2:    Rs2=Command2.Execute("select colA from sometable");  
    

    El procedimiento almacenado que se ejecuta a partir de la solicitud de usuario U1 ha adquirido la exclusión mutua de sesión.The stored procedure executing from user request U1 has acquired the session mutex. Si el procedimiento almacenado tarda mucho tiempo en ejecutarse, el Motor de base de datos de SQL ServerSQL Server Database Engine considerará que el procedimiento almacenado está esperando la intervención del usuario.If the stored procedure takes a long time to execute, it is assumed by the Motor de base de datos de SQL ServerSQL Server Database Engine that the stored procedure is waiting for input from the user. La solicitud de usuario U2 está esperando la exclusión mutua de sesión mientras que el usuario está esperando el conjunto de resultados de U2, y U1 está esperando un recurso de usuario.User request U2 is waiting for the session mutex while the user is waiting for the result set from U2, and U1 is waiting for a user resource. Éste es un estado de interbloqueo que se ilustra de forma lógica como:This is deadlock state logically illustrated as:

LogicFlowExamplec

Detección de interbloqueosDeadlock Detection

Todos los recursos enumerados en la sección anterior participan en el esquema de detección de interbloqueos del Motor de base de datos de SQL ServerSQL Server Database Engine.All of the resources listed in the section above participate in the Motor de base de datos de SQL ServerSQL Server Database Engine deadlock detection scheme. La detección de interbloqueos la realiza un subproceso de supervisión de bloqueos que periódicamente inicia una búsqueda por todas las tareas de una instancia del Motor de base de datos de SQL ServerSQL Server Database Engine.Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine. En los siguientes puntos se describe el proceso de búsqueda:The following points describe the search process:

  • El intervalo predeterminado es de 5 segundos.The default interval is 5 seconds.
  • Si el subproceso de supervisión de bloqueos encuentra interbloqueos, el intervalo de detección de interbloqueos pasará de 5 segundos a hasta solo 100 milisegundos, en función de la frecuencia de los interbloqueos.If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.
  • Si el subproceso de supervisión de bloqueos deja de encontrar interbloqueos, el Motor de base de datos de SQL ServerSQL Server Database Engine aumentará los intervalos entre las búsquedas a 5 segundos.If the lock monitor thread stops finding deadlocks, the Motor de base de datos de SQL ServerSQL Server Database Engine increases the intervals between searches to 5 seconds.
  • Si se acaba de detectar un interbloqueo, se considera que los siguientes subprocesos que deben esperar un bloqueo entran en el ciclo de interbloqueo.If a deadlock has just been detected, it is assumed that the next threads that must wait for a lock are entering the deadlock cycle. La primera pareja de esperas de bloqueo después de que se haya detectado un interbloqueo desencadenará inmediatamente una búsqueda de interbloqueos, en vez de esperar al siguiente intervalo de detección de interbloqueos.The first couple of lock waits after a deadlock has been detected will immediately trigger a deadlock search rather than wait for the next deadlock detection interval. Por ejemplo, si el intervalo actual es de 5 segundos y se acaba de detectar un interbloqueo, la siguiente espera de bloqueo activará inmediatamente el detector de interbloqueos.For example, if the current interval is 5 seconds, and a deadlock was just detected, the next lock wait will kick off the deadlock detector immediately. Si esta espera de bloqueo forma parte de un interbloqueo, se detectará en seguida en lugar de durante la siguiente búsqueda de interbloqueos.If this lock wait is part of a deadlock, it will be detected right away rather than during next deadlock search.

El Motor de base de datos de SQL ServerSQL Server Database Engine solo suele realizar detecciones de interbloqueos periódicas.The Motor de base de datos de SQL ServerSQL Server Database Engine typically performs periodic deadlock detection only. Dado que el número de interbloqueos que se encuentran en el sistema suele ser pequeño, si se detectan periódicamente, el sistema no se ve sobrecargado por este tipo de detecciones.Because the number of deadlocks encountered in the system is usually small, periodic deadlock detection helps to reduce the overhead of deadlock detection in the system.

Cuando el monitor de bloqueos inicia una búsqueda de interbloqueos para un subproceso determinado, identifica el recurso que está esperando.When the lock monitor initiates deadlock search for a particular thread, it identifies the resource on which the thread is waiting. Después, el monitor de bloqueos encuentra al propietario o a los propietarios de ese recurso y continúa recursivamente la búsqueda de interbloqueos para esos subprocesos hasta que encuentra un ciclo.The lock monitor then finds the owner(s) for that particular resource and recursively continues the deadlock search for those threads until it finds a cycle. Un ciclo que se identifica de esta manera forma un interbloqueo.A cycle identified in this manner forms a deadlock.

Una vez detectado un interbloqueo, el Motor de base de datos de SQL ServerSQL Server Database Engine finaliza un interbloqueo eligiendo uno de los subprocesos como sujeto del interbloqueo.After a deadlock is detected, the Motor de base de datos de SQL ServerSQL Server Database Engine ends a deadlock by choosing one of the threads as a deadlock victim. El Motor de base de datos de SQL ServerSQL Server Database Engine finaliza el lote actual que se está ejecutando para el subproceso, revierte la transacción del sujeto del interbloqueo y devuelve un error 1205 a la aplicación.The Motor de base de datos de SQL ServerSQL Server Database Engine terminates the current batch being executed for the thread, rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. Revertir la transacción para el sujeto del interbloqueo libera todos los bloqueos que tiene la transacción.Rolling back the transaction for the deadlock victim releases all locks held by the transaction. Esto permite que las transacciones de otros subprocesos se desbloqueen y continúen.This allows the transactions of the other threads to become unblocked and continue. El error 1205 del sujeto del interbloqueo registra información sobre los subprocesos y recursos implicados en un interbloqueo en el registro de errores.The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.

De forma predeterminada, el Motor de base de datos de SQL ServerSQL Server Database Engine elige como sujeto del interbloqueo la sesión que ejecuta la transacción cuya reversión resulta menos costosa.By default, the Motor de base de datos de SQL ServerSQL Server Database Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back. Como alternativa, un usuario puede especificar la prioridad de las sesiones en una situación de interbloqueo mediante la instrucción SET DEADLOCK_PRIORITY.Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. DEADLOCK_PRIORITY puede establecerse como LOW, NORMAL o HIGH; también puede establecerse como un valor entero en el intervalo de -10 a 10.DEADLOCK_PRIORITY can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10). El valor predeterminado de la prioridad de interbloqueo es NORMAL.The deadlock priority defaults to NORMAL. Si dos sesiones tienen distintas prioridades de interbloqueo, la sesión con la prioridad menor se elige como el sujeto del interbloqueo.If two sessions have different deadlock priorities, the session with the lower priority is chosen as the deadlock victim. Si ambas sesiones tienen la misma prioridad de interbloqueo, se elige la sesión con la transacción cuya reversión resulta menos costosa.If both sessions have the same deadlock priority, the session with the transaction that is least expensive to roll back is chosen. Si las sesiones implicadas en el ciclo de interbloqueo tienen la misma prioridad de interbloqueo y el mismo costo, se elige un sujeto de forma aleatoria.If sessions involved in the deadlock cycle have the same deadlock priority and the same cost, a victim is chosen randomly.

Cuando se trabaja con CLR, el monitor de interbloqueos detecta automáticamente el interbloqueo de los recursos de sincronización (monitores, bloqueo de lectura y escritura, y combinación de subprocesos) a los que se ha tenido acceso dentro de los procedimientos administrados.When working with CLR, the deadlock monitor automatically detects deadlock for synchronization resources (monitors, reader/writer lock and thread join) accessed inside managed procedures. Si embargo, el interbloqueo se resuelve iniciando una excepción en el procedimiento que se seleccionó como sujeto del interbloqueo.However, the deadlock is resolved by throwing an exception in the procedure that was selected to be the deadlock victim. Es importante comprender que la excepción no libera automáticamente los recursos que posee actualmente el sujeto; los recursos se tienen que liberar de forma explícita.It is important to understand that the exception does not automatically release resources currently owned by the victim; the resources must be explicitly released. De forma coherente con el comportamiento de la excepción, la excepción utilizada para identificar un sujeto del interbloqueo se puede interceptar y descartar.Consistent with exception behavior, the exception used to identify a deadlock victim can be caught and dismissed.

Herramientas de información de interbloqueosDeadlock Information Tools

Para ver la información de interbloqueos, Motor de base de datos de SQL ServerSQL Server Database Engine proporciona herramientas de supervisión en la forma de la sesión system_health de xEvent, dos marcas de seguimiento y el evento de grafo de interbloqueo en SQL Profiler.To view deadlock information, the Motor de base de datos de SQL ServerSQL Server Database Engine provides monitoring tools in the form of the system_health xEvent session, two trace flags, and the deadlock graph event in SQL Profiler.

Evento extendido de interbloqueoDeadlock Extended Event

A partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x), se debe usar el evento extendido de xml_deadlock_report (xEvent) en lugar de la clase de eventos Deadlock Graph en Seguimiento de SQL o SQL Profiler.Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), the xml_deadlock_report Extended Event (xEvent) should be used instead of the Deadlock graph event class in SQL Trace or SQL Profiler.

También a partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x), cuando se producen interbloqueos, la sesión *system_health _ captura todos los xEvents xml_deadlock_report que contienen el grafo de interbloqueo.Also starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), when deadlocks occur, the *system_health _ session already captures all xml_deadlock_report xEvents which contain the deadlock graph. Como la sesión _system_health* está habilitada de forma predeterminada, no es necesario configurar una sesión de xEvent independiente para capturar la información de interbloqueo.Because the _system_health* session is enabled by default, it's not required that a separate xEvent session is configured to capture deadlock information.

El grafo de interbloqueo que se capturaba normalmente consta de tres nodos distintos:The deadlock graph captured typically has three distinct nodes:

  • victim-list.victim-list. Identificador de proceso del elemento afectado por el interbloqueo.The deadlock victim process identifier.
  • process-list.process-list. Información sobre todos los procesos implicados en el interbloqueo.Information on all the processes involved in the deadlock.
  • resource-list.resource-list. Información sobre todos los recursos implicados en el interbloqueo.Information about the resources involved in the deadlock.

Al abrir el archivo de la sesión system_health o el búfer en anillo, si se registra el xEvent xml_deadlock_report, Management StudioManagement Studio presenta una representación gráfica de las tareas y recursos que participan en un interbloqueo, tal como se muestra en el ejemplo siguiente:Opening the system_health session file or ring buffer, if the xml_deadlock_report xEvent is recorded, Management StudioManagement Studio presents a graphical depiction of the tasks and resources involved in a deadlock, as seen in the following example:

Grafo de interbloqueo de xEvent

La consulta siguiente puede ver todos los eventos de interbloqueo capturados por el búfer en anillo de la sesión system_health:The following query can view all deadlock events captured by the system_health session ring buffer:

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
    xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
            FROM sys.dm_xe_session_targets AS xt
            INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
            WHERE xs.name = N'system_health'
              AND xt.target_name = N'ring_buffer'
    ) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC

El conjunto de resultados es el siguiente:Here is the result set.

system_health_xevent_query_result

En el ejemplo siguiente se muestra el resultado, después de hacer clic en el primer vínculo del resultado anterior:The following example shows the output, after clicking on the first link of the result above:

<event name="xml_deadlock_report" package="sqlserver" timestamp="2018-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2018-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-02-18T00:26:22.893" lastbatchcompleted="2018-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2016CTP3.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1) 
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2018-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-02-18T00:26:22.890" lastbatchcompleted="2018-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2016CTP3.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1) 
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2016CTP3.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2016CTP3.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

Para obtener más información, vea Usar la sesión system_health.For more information, see Use the system_health Session

Marcas de seguimiento 1204 y 1222Trace Flag 1204 and Trace Flag 1222

Cuando se produce el interbloqueo, los marcadores de seguimiento 1204 y 1222 devuelven la información que se ha capturado en el registro de errores de SQL ServerSQL Server.When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL ServerSQL Server error log. El marcador de seguimiento 1204 informa sobre el interbloqueo con un formato que especifica cada nodo implicado en el mismo.Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. El marcador de seguimiento 1222 aplica formato a la información de interbloqueo, primero por procesos y luego por recursos.Trace flag 1222 formats deadlock information, first by processes and then by resources. Es posible habilitar ambas marcas de seguimiento para obtener dos representaciones del mismo evento de interbloqueo.It is possible to enable both trace flags to obtain two representations of the same deadlock event.

Importante

Evite utilizar la marca de seguimiento 1204 y 1222 en sistemas con un uso intensivo de cargas de trabajo que causan interbloqueos.Avoid using trace flag 1204 and 1222 on workload-intensive systems that are causing deadlocks. El uso de estas marcas de seguimiento puede presentar problemas de rendimiento.Using these trace flags may introduce performance issues. En su lugar, use el evento de interbloqueo extendido (#deadlock_xevent).Instead, use the Deadlock Extended Event(#deadlock_xevent).

Además de definir las propiedades de las marcas de seguimiento 1204 y 1222, en la siguiente tabla se muestran las similitudes y las diferencias.In addition to defining the properties of trace flag 1204 and 1222, the following table also shows the similarities and differences.

PropiedadProperty Marcas de seguimiento 1204 y 1222Trace Flag 1204 and Trace Flag 1222 Solo marca de seguimiento 1204Trace Flag 1204 only Solo marca de seguimiento 1222Trace Flag 1222 only
Formato de salidaOutput format Los resultados se capturan en el registro de errores de SQL ServerSQL Server.Output is captured in the SQL ServerSQL Server error log. Se centra en los nodos implicados en el interbloqueo.Focused on the nodes involved in the deadlock. Cada nodo tiene una sección dedicada y la última sección describe al sujeto del interbloqueo.Each node has a dedicated section, and the final section describes the deadlock victim. Devuelve información en un formato XML que no se ajusta a un esquema de definición de esquemas XML (XSD).Returns information in an XML-like format that does not conform to an XML Schema Definition (XSD) schema. El formato tiene tres secciones principales.The format has three major sections. La primera sección declara el sujeto del interbloqueo.The first section declares the deadlock victim. La segunda sección describe los procesos implicados en el interbloqueo.The second section describes each process involved in the deadlock. La tercera sección describe los recursos que son sinónimos de nodos en la marca de seguimiento 1204.The third section describes the resources that are synonymous with nodes in trace flag 1204.
Atributos de identificaciónIdentifying attributes SPID:<x> ECID:<x>.SPID:<x> ECID:<x>. Identifica el subproceso del identificador de proceso del sistema en los casos de procesos paralelos.Identifies the system process ID thread in cases of parallel processes. La entrada SPID:<x> ECID:0, en que <x> se sustituye por el valor del SPID, representa el subproceso principal.The entry SPID:<x> ECID:0, where <x> is replaced by the SPID value, represents the main thread. La entrada SPID:<x> ECID:<y>, en que <x> se sustituye por el valor del SPID e <y> es mayor que 0, representa los subprocesos secundarios del mismo SPID.The entry SPID:<x> ECID:<y>, where <x> is replaced by the SPID value and <y> is greater than 0, represents the sub-threads for the same SPID.

BatchID (sbid para la marca de seguimiento 1222).BatchID (sbid for trace flag 1222). Identifica el lote desde el que la ejecución del código está solicitando o manteniendo un bloqueo.Identifies the batch from which code execution is requesting or holding a lock. Cuando se deshabilita Multiple Active Result Sets (MARTE), el valor de BatchID es 0.When Multiple Active Result Sets (MARS) is disabled, the BatchID value is 0. Cuando se habilita MART, el valor para los lotes activos es 1 para n.When MARS is enabled, the value for active batches is 1 to n. Si en la sesión no hay lotes activos, BatchID es 0.If there are no active batches in the session, BatchID is 0.

Mode.Mode. Especifica el tipo de bloqueo de un recurso en concreto que un subproceso solicita, concede o espera.Specifies the type of lock for a particular resource that is requested, granted, or waited on by a thread. Mode puede ser IS (Intención compartida), S (Compartido), U (Actualizar), IX (Intención exclusiva), SIX (Intención compartida exclusiva) y X (Exclusiva).Mode can be IS (Intent Shared), S (Shared), U (Update), IX (Intent Exclusive), SIX (Shared with Intent Exclusive), and X (Exclusive).

Line # (line para la marca de seguimiento 1222).Line # (line for trace flag 1222). Indica el número de línea en el lote actual de instrucciones que se estaba ejecutando cuando se produjo el interbloqueo.Lists the line number in the current batch of statements that was being executed when the deadlock occurred.

Input Buf (inputbuf para la marca de seguimiento 1222).Input Buf (inputbuf for trace flag 1222). Indica todas las instrucciones del lote actual.Lists all the statements in the current batch.
Node.Node. Representa el numero de entrada en la cadena de interbloqueo.Represents the entry number in the deadlock chain.

Lists.Lists. El propietario del bloqueo puede formar parte de estas listas:The lock owner can be part of these lists:

Grant List.Grant List. Enumera los propietarios actuales del recurso.Enumerates the current owners of the resource.

Convert List.Convert List. Enumera los propietarios actuales que están intentando convertir sus bloqueos a un nivel más alto.Enumerates the current owners that are trying to convert their locks to a higher level.

Wait List.Wait List. Enumera las solicitudes actuales del nuevo bloqueo para el recurso.Enumerates current new lock requests for the resource.

Statement Type.Statement Type. Describe el tipo de instrucción DML (SELECT, INSERT, UPDATE o DELETE) en que los subprocesos tienen permisos.Describes the type of DML statement (SELECT, INSERT, UPDATE, or DELETE) on which the threads have permissions.

Victim Resource Owner.Victim Resource Owner. Especifica el subproceso participante que SQL ServerSQL Server elige como sujeto para interrumpir el ciclo de interbloqueo.Specifies the participating thread that SQL ServerSQL Server chooses as the victim to break the deadlock cycle. El subproceso elegido y todos los subprocesos secundarios finalizan.The chosen thread and all existing sub-threads are terminated.

Next Branch.Next Branch. Representa los dos o más subprocesos secundarios del mismo SPID que están implicados en el ciclo de interbloqueo.Represents the two or more sub-threads from the same SPID that are involved in the deadlock cycle.
deadlock victim.deadlock victim. Representa la dirección de la memoria física de la tarea (vea sys.dm_os_tasks (Transact-SQL)) que se seleccionó como sujeto del interbloqueo.Represents the physical memory address of the task (see sys.dm_os_tasks (Transact-SQL)) that was selected as a deadlock victim. Puede ser 0 (cero) en caso de un interbloqueo no resuelto.It may be 0 (zero) in the case of an unresolved deadlock. Una tarea que se está revirtiendo no se puede seleccionar como sujeto del interbloqueo.A task that is rolling back cannot be chosen as a deadlock victim.

executionstack.executionstack. Representa el código Transact-SQLTransact-SQL que se está ejecutando en el momento en que se produce el interbloqueo.Represents Transact-SQLTransact-SQL code that is being executed at the time the deadlock occurs.

priority.priority. Representa la prioridad de interbloqueo.Represents deadlock priority. En ciertos casos, el Motor de base de datos de SQL ServerSQL Server Database Engine puede optar por modificar la prioridad de interbloqueo durante una breve duración para conseguir una mejor simultaneidad.In certain cases, the Motor de base de datos de SQL ServerSQL Server Database Engine may opt to alter the deadlock priority for a short duration to achieve better concurrency.

logused.logused. Espacio de registro utilizado por la tarea.Log space used by the task.

owner id. El Id. de la transacción que tiene el control de la solicitud.owner id. The ID of the transaction that has control of the request.

status.status. Estado de la tarea.State of the task. Es uno de los siguientes valores:It is one of the following values:

>> pending.>> pending. Esperando un subproceso de trabajo.Waiting for a worker thread.

>> runnable.>> runnable. Preparado para ejecutarse pero esperando un cuanto.Ready to run but waiting for a quantum.

>> running.>> running. Ejecutándose actualmente en el programador.Currently running on the scheduler.

>> suspended.>> suspended. La ejecución se ha suspendido.Execution is suspended.

>> done.>> done. La tarea se ha completado.Task has completed.

>> spinloop.>> spinloop. Esperando que un bloqueo por bucle esté disponible.Waiting for a spinlock to become free.

waitresource.waitresource. El recurso que la tarea necesita.The resource needed by the task.

waittime.waittime. Tiempo en milisegundos de espera del recurso.Time in milliseconds waiting for the resource.

schedulerid.schedulerid. Programador asociado a esta tarea.Scheduler associated with this task. Consulte sys.dm_os_schedulers (Transact-SQL).See sys.dm_os_schedulers (Transact-SQL).

hostname.hostname. El nombre de la estación de trabajo.The name of the workstation.

isolationlevel.isolationlevel. El nivel de aislamiento de transacción actual.The current transaction isolation level.

Xactid.Xactid. El Id. de la transacción que tiene el control de la solicitud.The ID of the transaction that has control of the request.

currentdb.currentdb. El Id. de la base de datos.The ID of the database.

lastbatchstarted.lastbatchstarted. La última vez que un proceso de cliente inició la ejecución de lotes.The last time a client process started batch execution.

lastbatchcompleted.lastbatchcompleted. La última vez que un proceso de cliente completó la ejecución de lotes.The last time a client process completed batch execution.

clientoption1 y clientoption2.clientoption1 and clientoption2. Opciones establecidas en esta conexión de cliente.Set options on this client connection. Se trata de una máscara de bits que incluye información acerca de las opciones controladas normalmente por instrucciones SET, como SET NOCOUNT y SET XACTABORT.This is a bitmask that includes information about options usually controlled by SET statements such as SET NOCOUNT and SET XACTABORT.

associatedObjectId.associatedObjectId. Representa el Id. del árbol b o montón (HoBT).Represents the HoBT (heap or b-tree) ID.
Atributos del recursoResource attributes RID.RID. Identifica la única fila de una tabla en la que se mantiene o se solicita un bloqueo.Identifies the single row within a table on which a lock is held or requested. RID se representa como RID: db_id:file_id:page_no:row_no.RID is represented as RID: db_id:file_id:page_no:row_no. Por ejemplo, RID: 6:1:20789:0.For example, RID: 6:1:20789:0.

OBJECT.OBJECT. Identifica la tabla en la que se mantiene o se solicita un bloqueo.Identifies the table on which a lock is held or requested. OBJECT se representa como OBJECT: db_id:object_id.OBJECT is represented as OBJECT: db_id:object_id. Por ejemplo, TAB: 6:2009058193.For example, TAB: 6:2009058193.

KEY.KEY. Identifica el intervalo de clave de un índice en el que se mantiene o se solicita un bloqueo.Identifies the key range within an index on which a lock is held or requested. KEY se representa como KEY: db_id:hobt_id (valor de hash de clave de índice).KEY is represented as KEY: db_id:hobt_id (index key hash value). Por ejemplo, KEY: 6:72057594057457664 (350007a4d329).For example, KEY: 6:72057594057457664 (350007a4d329).

PAG.PAG. Identifica el recurso de página en el que se mantiene o se solicita un bloqueo.Identifies the page resource on which a lock is held or requested. PAG se representa como PAG: db_id:file_id:page_no.PAG is represented as PAG: db_id:file_id:page_no. Por ejemplo, PAG: 6:1:20789.For example, PAG: 6:1:20789.

EXT.EXT. Identifica la estructura de extensión.Identifies the extent structure. EXT se representa como EXT: db_id:file_id:extent_no.EXT is represented as EXT: db_id:file_id:extent_no. Por ejemplo, EXT: 6:1:9.For example, EXT: 6:1:9.

DB.DB. Identifica el bloqueo de la base de datos.Identifies the database lock. DB se representa de una de las siguientes maneras:DB is represented in one of the following ways:

DB: db_idDB: db_id

DB: db_id[BULK-OP-DB], que identifica el bloqueo de base de datos realizado por la copia de seguridad de la base de datos.DB: db_id[BULK-OP-DB], which identifies the database lock taken by the backup database.

DB: db_id[BULK-OP-LOG], que identifica el bloqueo realizado por el registro de copia de seguridad de esa base de datos en concreto.DB: db_id[BULK-OP-LOG], which identifies the lock taken by the backup log for that particular database.

APP.APP. Identifica el bloqueo realizado por un recurso de la aplicación.Identifies the lock taken by an application resource. Se representa como APP: lock_resource.APP is represented as APP: lock_resource. Por ejemplo, APP: Formf370f478.For example, APP: Formf370f478.

METADATA.METADATA. Representa los recursos de metadatos implicados en un interbloqueo.Represents metadata resources involved in a deadlock. Debido a que METADATA tiene muchos recursos secundarios, el valor devuelto depende del recurso secundario que se haya interbloqueado.Because METADATA has many subresources, the value returned depends upon the subresource that has deadlocked. Por ejemplo, METADATA.USER_TYPE devuelve user_type_id = <integer_value>.For example, METADATA.USER_TYPE returns user_type_id = <integer_value>. Para obtener más información acerca de los recursos y recursos secundarios de METADATA, vea sys.dm_tran_locks (Transact-SQL).For more information about METADATA resources and subresources, see sys.dm_tran_locks (Transact-SQL).

HOBT.HOBT. Representa al montón o árbol b implicados en un interbloqueo.Represents a heap or b-tree involved in a deadlock.
Nada exclusivo de esta marca de seguimiento.None exclusive to this trace flag. Nada exclusivo de esta marca de seguimiento.None exclusive to this trace flag.
Ejemplo de marca de seguimiento 1204Trace Flag 1204 Example

En el siguiente ejemplo se muestra el resultado que se obtiene cuando se activa una marca de seguimiento 1204.The following example shows the output when trace flag 1204 is turned on. En este caso, la tabla de Node 1 es un montón sin índices, y la tabla de Node 2 es un montón con un índice no clúster.In this case, the table in Node 1 is a heap with no indexes, and the table in Node 2 is a heap with a nonclustered index. La clave de índice de Node 2 se está actualizando cuando se produce el interbloqueo.The index key in Node 2 is being updated when the deadlock occurs.

Deadlock encountered .... Printing deadlock information  
Wait-for graph  
  
Node:1  
  
RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2  
 Grant List 0:  
   Owner:0x0315D6A0 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C  
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
BEGIN TRANSACTION  
   EXEC usp_p2  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0   
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)  
  
Node:2  
  
KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0  
 Grant List 0:  
   Owner:0x0315D140 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4  
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
     BEGIN TRANSACTION  
       EXEC usp_p1  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  
  
Victim Resource Owner:  
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  
Ejemplo de marca de seguimiento 1222Trace Flag 1222 Example

En el siguiente ejemplo se muestra el resultado que se obtiene cuando se activa una marca de seguimiento 1222.The following example shows the output when trace flag 1222 is turned on. En este caso, una tabla es un montón sin índices y la otra tabla es un montón con un índice no clúster.In this case, one table is a heap with no indexes, and the other table is a heap with a nonclustered index. En la segunda tabla, la clave de índice se está actualizando cuando se produce el interbloqueo.In the second table, the index key is being updated when the deadlock occurs.

deadlock-list  
 deadlock victim=process689978  
  process-list  
   process id=process6891f8 taskpriority=0 logused=868   
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444   
   transactionname=user_transaction   
   lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0   
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54   
   sbid=0 ecid=0 priority=0 transcount=2   
   lastbatchstarted=2005-09-05T11:22:42.733   
   lastbatchcompleted=2005-09-05T11:22:42.733   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310444 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2016.dbo.usp_p1 line=6 stmtstart=202   
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000  
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000  
     EXEC usp_p1       
    inputbuf  
      BEGIN TRANSACTION  
       EXEC usp_p1  
   process id=process689978 taskpriority=0 logused=380   
   waitresource=KEY: 6:72057594057457664 (350007a4d329)     
   waittime=5015 ownerId=310462 transactionname=user_transaction   
   lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U   
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0   
   priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077   
   lastbatchcompleted=2005-09-05T11:22:44.077   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310462 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2016.dbo.usp_p2 line=6 stmtstart=200   
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000  
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000  
     EXEC usp_p2       
    inputbuf  
      BEGIN TRANSACTION  
        EXEC usp_p2      
  resource-list  
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2016.dbo.T2   
   id=lock3136940 mode=X associatedObjectId=72057594057392128  
    owner-list  
     owner id=process689978 mode=X  
    waiter-list  
     waiter id=process6891f8 mode=U requestType=wait  
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2016.dbo.T1   
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X   
   associatedObjectId=72057594057457664  
    owner-list  
     owner id=process6891f8 mode=X  
    waiter-list  
     waiter id=process689978 mode=U requestType=wait  

Evento Deadlock Graph del AnalizadorProfiler Deadlock Graph Event

Este es un evento de SQL Profiler que presenta una descripción gráfica de las tareas y los recursos implicados en un interbloqueo.This is an event in SQL Profiler that presents a graphical depiction of the tasks and resources involved in a deadlock. En el siguiente ejemplo se muestra el resultado de SQL Profiler cuando se ha activado el evento del grafo de interbloqueo.The following example shows the output from SQL Profiler when the deadlock graph event is turned on.

ProfilerDeadlockGraphc

Para obtener más información sobre el evento de interbloqueo, consulte Lock:Deadlock Event Class (Clase de evento Lock:Deadlock).For more information about the deadlock event, see Lock:Deadlock Event Class.

Para obtener más información sobre cómo ejecutar el grafo de interbloqueo de SQL Profiler, vea Guardar grafos de interbloqueo (SQL Server Profiler).For more information about running the SQL Profiler deadlock graph, see Save Deadlock Graphs (SQL Server Profiler).

Controlar interbloqueosHandling Deadlocks

Cuando una instancia del Motor de base de datos de SQL ServerSQL Server Database Engine elige una transacción como elemento afectado por un interbloqueo, finaliza el lote actual, revierte la transacción y devuelve el mensaje de error 1205 a la aplicación.When an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine chooses a transaction as a deadlock victim, it terminates the current batch, rolls back the transaction, and returns error message 1205 to the application.

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Dado que cualquier aplicación que envía consultas Transact-SQLTransact-SQL puede elegirse como sujeto de un interbloqueo, las aplicaciones deben tener un controlador de errores que pueda interceptar el mensaje de error 1205.Because any application submitting Transact-SQLTransact-SQL queries can be chosen as the deadlock victim, applications should have an error handler that can trap error message 1205. Si una aplicación no intercepta el error, puede continuar sin ser consciente de que se ha revertido la transacción y de que se pueden producir errores.If an application does not trap the error, the application can proceed unaware that its transaction has been rolled back and errors can occur.

La implementación de un controlador de errores que intercepte el mensaje 1205 permite a una aplicación controlar la situación de interbloqueo y realizar una acción apropiada para solucionarla, por ejemplo, volver a enviar automáticamente la consulta implicada en el interbloqueo.Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation and take remedial action (for example, automatically resubmitting the query that was involved in the deadlock). Si se vuelve a enviar la consulta de forma automática, no es necesario que el usuario sepa que se ha producido un interbloqueo.By resubmitting the query automatically, the user does not need to know that a deadlock occurred.

La aplicación debería realizar una pausa breve antes de volver a enviar su consulta.The application should pause briefly before resubmitting its query. Esto ofrece a la otra transacción implicada en el interbloqueo una oportunidad de completarse y liberar sus bloqueos que formaban parte del ciclo de interbloqueo.This gives the other transaction involved in the deadlock a chance to complete and release its locks that formed part of the deadlock cycle. Así se minimiza la probabilidad de que el interbloqueo vuelva a ocurrir cuando la consulta que se ha vuelto a enviar solicite sus bloqueos.This minimizes the likelihood of the deadlock reoccurring when the resubmitted query requests its locks.

Minimizar los interbloqueosMinimizing Deadlocks

A pesar de que los interbloqueos no se pueden evitar totalmente, si se siguen ciertas convenciones de codificación se puede reducir su generación.Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. La minimización de los interbloqueos puede aumentar el rendimiento de las transacciones y reducir la sobrecarga del sistema, debido a que:Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

  • Se revierten menos transacciones, al deshacer todo el trabajo que realiza la transacción.Rolled back, undoing all the work performed by the transaction.
  • Las aplicaciones vuelven a enviar menos transacciones debido a que se revirtieron cuando se produjo el interbloqueo.Resubmitted by applications because they were rolled back when deadlocked.

Para ayudar a reducir los interbloqueos:To help minimize deadlocks:

  • Obtenga acceso a los objetos en el mismo orden.Access objects in the same order.
  • Evite la interacción con los usuarios en las transacciones.Avoid user interaction in transactions.
  • Mantenga transacciones cortas y en un proceso por lotes.Keep transactions short and in one batch.
  • Utilice un nivel de aislamiento inferior.Use a lower isolation level.
  • Utilice un nivel de aislamiento basado en versiones de fila.Use a row versioning-based isolation level.
    • Establezca la opción de base de datos READ_COMMITTED_SNAPSHOT en ON para habilitar las transacciones de lectura confirmada y que usen las versiones de fila.Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
    • Utilice el aislamiento de instantánea.Use snapshot isolation.
  • Utilice conexiones enlazadas.Use bound connections.

Acceder a los objetos en el mismo ordenAccess Objects in the same order

Si todas las transacciones simultáneas tienen acceso a los objetos en el mismo orden, es menos probable que se produzcan interbloqueos.If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. Por ejemplo, si dos transacciones simultáneas obtienen un bloqueo en la tabla Supplier y después en la tabla Part, una transacción se bloquea en la tabla Supplier hasta que finalice la otra transacción.For example, if two concurrent transactions obtain a lock on the Supplier table and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. Una vez confirmada o revertida la primera transacción, continúa la segunda, por lo que no se produce un interbloqueo.After the first transaction commits or rolls back, the second continues, and a deadlock does not occur. La utilización de procedimientos almacenados para todas las modificaciones de datos puede normalizar el orden de acceso a los objetos.Using stored procedures for all data modifications can standardize the order of accessing objects.

deadlock2

Evitar la interacción con los usuarios en las transaccionesAvoid user interaction in Transactions

Evite escribir transacciones que incluyan la intervención del usuario, ya que la velocidad de ejecución de los lotes que no requieren esta intervención es mucho mayor que la velocidad con la que el usuario debe responder manualmente a las consultas como, por ejemplo, contestar a la solicitud de un parámetro por parte de una aplicación.Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application. Por ejemplo, si una transacción espera una entrada del usuario y éste sale a comer o no vuelve hasta pasado el fin de semana, dicho usuario retrasa la finalización de la transacción.For example, if a transaction is waiting for user input and the user goes to lunch or even home for the weekend, the user delays the transaction from completing. De esta forma, se degrada el rendimiento del sistema, ya que los bloqueos que mantiene la transacción solo se liberan cuando se confirma o se revierte la transacción.This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. Aunque no surja una situación de interbloqueo, las demás transacciones que obtienen acceso a los mismos recursos se bloquean mientras esperan a que la transacción finalice.Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked while waiting for the transaction to complete.

Mantener transacciones cortas y en un proceso por lotesKeep Transactions short and in one batch

Normalmente, los interbloqueos se producen cuando varias transacciones de larga duración se ejecutan simultáneamente en la misma base de datos.A deadlock typically occurs when several long-running transactions execute concurrently in the same database. Cuanto más dure la transacción, más tiempo se mantendrán los bloqueos exclusivos o de actualización, con lo cual se bloquean otras actividades y se originan posibles situaciones de interbloqueo.The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.

Al mantener las transacciones en un proceso por lotes, se minimizan los viajes de ida y vuelta en la red durante una transacción y se reducen los posibles retrasos al completar la transacción y liberar los bloqueos.Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.

Utilizar un nivel de aislamiento inferiorUse a lower Isolation Level

Determine si una transacción se puede ejecutar con un nivel de aislamiento inferior.Determine whether a transaction can run at a lower isolation level. Al implementar la lectura confirmada, se permite a una transacción leer los datos previamente leídos (no modificados) por otra transacción, sin tener que esperar a que la primera transacción finalice.Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. Utilizar un nivel inferior de aislamiento, como la lectura confirmada, mantiene los bloqueos compartidos durante menos tiempo que un nivel superior de aislamiento, como el nivel serializable.Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. De esta forma, se reduce la contención de bloqueos.This reduces locking contention.

Utilizar un nivel de aislamiento basado en versiones de filaUse a Row Versioning-based Isolation Level

Si la opción de base de datos READ_COMMITTED_SNAPSHOT se ha establecido en ON, la transacción que se ejecuta con el nivel de aislamiento de lectura confirmada utiliza las versiones de fila en lugar de bloqueos compartidos durante las operaciones de lectura.When the READ_COMMITTED_SNAPSHOT database option is set ON, a transaction running under read committed isolation level uses row versioning rather than shared locks during read operations.

Nota

Algunas aplicaciones dependen del comportamiento de los bloqueos en el aislamiento de lectura confirmada.Some applications rely upon locking and blocking behavior of read committed isolation. En estas aplicaciones, es preciso efectuar algunos cambios antes de habilitar esta opción.For these applications, some change is required before this option can be enabled.

El aislamiento de instantánea también utiliza las versiones de fila, que no emplean bloqueos compartidos en las operaciones de lectura.Snapshot isolation also uses row versioning, which does not use shared locks during read operations. Antes de ejecutar una transacción con aislamiento de instantánea, debe establecerse en ON la opción de base de datos ALLOW_SNAPSHOT_ISOLATION.Before a transaction can run under snapshot isolation, the ALLOW_SNAPSHOT_ISOLATION database option must be set ON.

Implemente estos niveles de aislamiento para reducir los interbloqueos que se pueden producir entre operaciones de lectura y escritura.Implement these isolation levels to minimize deadlocks that can occur between read and write operations.

Utilizar conexiones enlazadasUse bound connections

Al utilizar conexiones enlazadas, dos o más conexiones abiertas por la misma aplicación pueden cooperar entre sí.Using bound connections, two or more connections opened by the same application can cooperate with each other. Los bloqueos adquiridos por las conexiones secundarias se mantienen como si los adquiriera la conexión principal y viceversa.Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. Por lo tanto, no se bloquean entre sí.Therefore they do not block each other.

Partición de bloqueosLock Partitioning

Para los grandes sistemas, los bloqueos en los objetos a los que se hace referencia asiduamente pueden convertirse en un cuello de botella para el rendimiento, puesto que la adquisición y liberación de los bloqueos genera contención en los recursos de bloqueo internos.For large computer systems, locks on frequently referenced objects can become a performance bottleneck as acquiring and releasing locks place contention on internal locking resources. La partición de bloqueos mejora el rendimiento porque divide un solo recurso de bloqueo entre varios recursos de bloqueo más.Lock partitioning enhances locking performance by splitting a single lock resource into multiple lock resources. Esta característica solo está disponible para los sistemas con 16 o más CPU, se habilita automáticamente y no se puede deshabilitar.This feature is only available for systems with 16 or more CPUs, and is automatically enabled and cannot be disabled. Solo los bloqueos de objetos pueden tener particiones. Los bloqueos de objetos que tengan un subtipo no pueden tener particiones.Only object locks can be partitioned.Object locks that have a subtype are not partitioned. Para obtener más información, vea sys.dm_tran_locks (Transact-SQL).For more information, see sys.dm_tran_locks (Transact-SQL).

Descripción de partición de bloqueosUnderstanding Lock Partitioning

Las tareas de bloqueo obtienen acceso a varios recursos compartidos, dos de los cuales se optimizan mediante la partición de bloqueos:Locking tasks access several shared resources, two of which are optimized by lock partitioning:

  • Spinlock.Spinlock. Controla el acceso a un recuso de bloqueo, como una fila o una tabla.This controls access to a lock resource, such as a row or a table.

    Sin la partición de bloqueos, un spinlock administra todas las solicitudes de bloqueo para un solo recurso de bloqueo.Without lock partitioning, one spinlock manages all lock requests for a single lock resource. En los sistemas con un gran volumen de actividad, puede producirse contención a medida que las solicitudes de bloqueo esperan a que un spinlock esté disponible.On systems that experience a large volume of activity, contention can occur as lock requests wait for the spinlock to become available. En esta situación, la adquisición de bloqueos puede generar un cuello de botella que puede afectar negativamente al rendimiento.Under this situation, acquiring locks can become a bottleneck and can negatively impact performance.

    Para reducir la contención en un solo recurso de bloqueo, la partición de bloqueos divide un recurso de bloqueo en varios recursos de bloqueo para repartir la carga entre varios spinlock.To reduce contention on a single lock resource, lock partitioning splits a single lock resource into multiple lock resources to distribute the load across multiple spinlocks.

  • Memoria.Memory. Se utiliza para almacenar las estructuras de los recursos de bloqueo.This is used to store the lock resource structures.

    Cuando ya se ha adquirido el spinlock, las estructuras de bloqueo se almacenan en memoria para que, a continuación, estén disponibles para el acceso y realizar modificaciones.Once the spinlock is acquired, lock structures are stored in memory and then accessed and possibly modified. La distribución del acceso a los bloqueos entre varios recursos ayuda a eliminar la necesidad de transferir bloqueos de memoria entre CPU, lo que ayuda a mejorar el rendimiento.Distributing lock access across multiple resources helps to eliminate the need to transfer memory blocks between CPUs, which will help to improve performance.

Implementar y supervisar las particiones de bloqueosImplementing and Monitoring Lock Partitioning

La partición de bloqueos está activada de forma predeterminada para los sistemas con 16 CPU o más.Lock partitioning is turned on by default for systems with 16 or more CPUs. Cuando la partición de bloqueos está habilitada, se registra un mensaje informativo en el registro de errores de SQL ServerSQL Server.When lock partitioning is enabled, an informational message is recorded in the SQL ServerSQL Server error log.

Al adquirir bloqueos en un recurso con particiones:When acquiring locks on a partitioned resource:

  • Solo los modos de bloqueo NL, SCH-S, IS, IU e IX se adquieren en una sola partición.Only NL, SCH-S, IS, IU, and IX lock modes are acquired on a single partition.

  • Los bloqueos compartidos (S), exclusivos (X) y otros bloqueos en modos que no sean NL, SCH-S, IS, IU e IX deben adquirirse en todas las particiones empezando por el Id. de partición 0 seguido del resto de Id. en orden.Shared (S), exclusive (X), and other locks in modes other than NL, SCH-S, IS, IU, and IX must be acquired on all partitions starting with partition ID 0 and following in partition ID order. Estos bloqueos en un recurso con particiones utilizarán más memoria que los bloqueos del mismo modo en un recurso sin particiones puesto que cada partición es de hecho un bloqueo independiente.These locks on a partitioned resource will use more memory than locks in the same mode on a non-partitioned resource since each partition is effectively a separate lock. El número de particiones determina los incrementos de memoria.The memory increase is determined by the number of partitions. Los contadores de bloqueo de SQL ServerSQL Server en el Monitor de rendimiento de Windows mostrarán información acerca de la memoria utilizada por los bloqueos con y sin particiones.The SQL ServerSQL Server lock counters in the Windows Performance Monitor will display information about memory used by partitioned and non-partitioned locks.

Una transacción se asigna a una partición cuando se inicia la transacción.A transaction is assigned to a partition when the transaction starts. Para la transacción, todas las solicitudes de bloqueo que pueden dividirse utilizan la partición asignada a esa transacción.For the transaction, all lock requests that can be partitioned use the partition assigned to that transaction. Con este método, el acceso por parte de diferentes transacciones a los recursos de bloqueo del mismo objeto se distribuye a través de diferentes particiones.By this method, access to lock resources of the same object by different transactions is distributed across different partitions.

La columna resource_lock_partition de la vista de administración dinámica sys.dm_tran_locks proporciona el identificador de la partición de bloqueo para un recurso con particiones de bloqueo.The resource_lock_partition column in the sys.dm_tran_locks Dynamic Management View provides the lock partition ID for a lock partitioned resource. Para obtener más información, vea sys.dm_tran_locks (Transact-SQL).For more information, see sys.dm_tran_locks (Transact-SQL).

Trabajar con la partición de bloqueosWorking with Lock Partitioning

En los siguientes ejemplos de código se muestra la partición de bloqueos.The following code examples illustrate lock partitioning. En estos ejemplos se ejecutan dos transacciones en dos sesiones diferentes para mostrar el comportamiento de la partición de bloqueos en sistemas grandes con 16 CPU.In the examples, two transactions are executed in two different sessions in order to show lock partitioning behavior on a computer system with 16 CPUs.

Estas instrucciones Transact-SQLTransact-SQL crean objetos de prueba que se utilizan en los siguientes ejemplos.These Transact-SQLTransact-SQL statements create test objects that are used in the examples that follow.

-- Create a test table.  
CREATE TABLE TestTable  (col1 int);  
GO  
  
-- Create a clustered index on the table.  
CREATE CLUSTERED INDEX ci_TestTable   
    ON TestTable (col1);  
GO  
  
-- Populate the table.  
INSERT INTO TestTable VALUES (1);  
GO  

Ejemplo AExample A

Sesión 1:Session 1:

Una instrucción SELECT se ejecuta en una transacción.A SELECT statement is executed under a transaction. Debido a la sugerencia de bloqueo HOLDLOCK, esta instrucción adquirirá y retendrá un bloqueo Intención compartida (IS) en una tabla (en esta ilustración, los bloqueos de fila y página se pasan por alto).Because of the HOLDLOCK lock hint, this statement will acquire and retain an Intent shared (IS) lock on the table (for this illustration, row and page locks are ignored). El bloqueo IS solo se adquirirá en la partición asignada a la transacción.The IS lock will be acquired only on the partition assigned to the transaction. Para este ejemplo, se supone que el bloqueo IS se adquiere en el id. 7 de la partición.For this example, it is assumed that the IS lock is acquired on partition ID 7.

-- Start a transaction.  
BEGIN TRANSACTION  
    -- This SELECT statement will acquire an IS lock on the table.  
    SELECT col1  
    FROM TestTable  
    WITH (HOLDLOCK);  

Sesión 2:Session 2:

Se inicia una transacción y la instrucción SELECT que se ejecuta bajo esta transacción adquirirá y retendrá un bloqueo compartido (S) en la tabla.A transaction is started, and the SELECT statement running under this transaction will acquire and retain a shared (S) lock on the table. El bloqueo S se adquirirá en todas las particiones que tengan como resultado varios bloqueos de tabla, uno para cada partición.The S lock will be acquired on all partitions which results in multiple table locks, one for each partition. Por ejemplo, en un sistema de 16 cpu, 16 bloqueos S se emitirán por el bloqueo en los id. 0-15 de la partición.For example, on a 16-cpu system, 16 S locks will be issued across lock partition IDs 0-15. Dado que el bloqueo S es compatible con el bloqueo IS que se retiene en el id. 7 de la partición por la transacción de la sesión 1, no hay ningún bloqueo entre las transacciones.Because the S lock is compatible with the IS lock being held on partition ID 7 by the transaction in session 1, there is no blocking between transactions.

BEGIN TRANSACTION  
    SELECT col1  
    FROM TestTable  
    WITH (TABLOCK, HOLDLOCK);  

Sesión 1:Session 1:

La siguiente instrucción SELECT se ejecuta bajo la transacción que todavía está activa bajo la sesión 1.The following SELECT statement is executed under the transaction that is still active under session 1. Debido a la sugerencia de bloqueo de tabla (X) exclusiva, la transacción intentará adquirir un bloqueo X en la tabla.Because of the exclusive (X) table lock hint, the transaction will attempt to acquire an X lock on the table. Sin embargo, el bloqueo S que retiene la transacción en la sesión 2 bloqueará el bloqueo X en el id. 0 de la partición.However, the S lock that is being held by the transaction in session 2 will block the X lock at partition ID 0.

SELECT col1  
FROM TestTable  
WITH (TABLOCKX);  

Ejemplo BExample B

Sesión 1:Session 1:

Una instrucción SELECT se ejecuta en una transacción.A SELECT statement is executed under a transaction. Debido a la sugerencia de bloqueo HOLDLOCK, esta instrucción adquirirá y retendrá un bloqueo Intención compartida (IS) en una tabla (en esta ilustración, los bloqueos de fila y página se pasan por alto).Because of the HOLDLOCK lock hint, this statement will acquire and retain an Intent shared (IS) lock on the table (for this illustration, row and page locks are ignored). El bloqueo IS solo se adquirirá en la partición asignada a la transacción.The IS lock will be acquired only on the partition assigned to the transaction. Para este ejemplo, se supone que el bloqueo IS se adquiere en el id. 6 de la partición.For this example, it is assumed that the IS lock is acquired on partition ID 6.

-- Start a transaction.  
BEGIN TRANSACTION  
    -- This SELECT statement will acquire an IS lock on the table.  
    SELECT col1  
    FROM TestTable  
    WITH (HOLDLOCK);  

Sesión 2:Session 2:

Una instrucción SELECT se ejecuta en una transacción.A SELECT statement is executed under a transaction. Debido a la sugerencia de bloqueo de TABLOCKX, la transacción intenta adquirir un bloqueo exclusivo (X) en la tabla.Because of the TABLOCKX lock hint, the transaction tries to acquire an exclusive (X) lock on the table. Recuerde que el bloqueo X se debe adquirir en todas las particiones comenzando en el id. 0 de la partición.Remember that the X lock must be acquired on all partitions starting with partition ID 0. El bloqueo X se adquirirá en los id. 0-5 de todas las particiones pero se bloqueará por el bloqueo IS adquirido por el id. 6 de la partición.The X lock will be acquired on all partitions IDs 0-5 but will be blocked by the IS lock that is acquired on partition ID 6.

En los Id. 7 a 15 de la partición que el bloqueo X aún no ha alcanzado, otras transacciones puede seguir adquiriendo bloqueos.On partition IDs 7-15 that the X lock has not yet reached, other transactions can continue to acquire locks.

BEGIN TRANSACTION  
    SELECT col1  
    FROM TestTable  
    WITH (TABLOCKX, HOLDLOCK);  

Niveles de aislamiento basados en versiones de fila en el Motor de base de datos de SQL ServerSQL Server Database EngineRow Versioning-based Isolation Levels in the Motor de base de datos de SQL ServerSQL Server Database Engine

A partir de SQL Server 2005 (9.x)SQL Server 2005 (9.x), el Motor de base de datos de SQL ServerSQL Server Database Engine ofrece una implementación de un nivel de aislamiento de transacciones existente, con confirmación de lectura, que proporciona una instantánea de nivel de instrucción con versiones de fila.Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x), the Motor de base de datos de SQL ServerSQL Server Database Engine offers an implementation of an existing transaction isolation level, read committed, that provides a statement level snapshot using row versioning. El Motor de base de datos de SQL ServerSQL Server Database Engine también ofrece un nivel de aislamiento de transacciones, instantánea, que proporciona una instantánea de nivel de transacción que también usa versiones de fila.Motor de base de datos de SQL ServerSQL Server Database Engine also offers a transaction isolation level, snapshot, that provides a transaction level snapshot also using row versioning.

Las versiones de fila es un marco general en SQL ServerSQL Server que invoca un mecanismo de copia por escritura cuando se modifica o elimina una fila.Row versioning is a general framework in SQL ServerSQL Server that invokes a copy-on-write mechanism when a row is modified or deleted. Esto requiere que, mientras se ejecuta la transacción, la versión anterior de la fila debe estar disponible para las transacciones que requieran un estado anterior transaccionalmente coherente.This requires that while the transaction is running, the old version of the row must be available for transactions that require an earlier transactionally consistent state. Las versiones de fila hacen lo siguiente:Row versioning is used to do the following:

  • Crear las tablas inserted y deleted en desencadenadores.Build the inserted and deleted tables in triggers. Se crean versiones de las filas modificadas por el desencadenador.Any rows modified by the trigger are versioned. Esto incluye las filas modificadas por la instrucción que activó el desencadenador, así como las modificaciones de datos realizadas por el desencadenador.This includes the rows modified by the statement that launched the trigger, as well as any data modifications made by the trigger.
  • Compatibilidad con los conjuntos de resultados activos múltiples (MARS).Support Multiple Active Result Sets (MARS). Si una sesión MARS emite una instrucción de modificación de datos (como INSERT, UPDATE o DELETE) en un momento en el que hay un conjunto de resultados activos, se crean versiones de las filas afectadas por la instrucción de modificación.If a MARS session issues a data modification statement (such as INSERT, UPDATE, or DELETE) at a time there is an active result set, the rows affected by the modification statement are versioned.
  • Compatibilidad con las operaciones de índice que especifican la opción ONLINE.Support index operations that specify the ONLINE option.
  • Compatibilidad con los niveles de aislamiento de transacción basados en versiones de fila:Support row versioning-based transaction isolation levels:
    • Nueva implementación del nivel de aislamiento de lectura confirmada que utiliza las versiones de fila para proporcionar una coherencia de lectura en las instrucciones.A new implementation of read committed isolation level that uses row versioning to provide statement-level read consistency.
    • Nuevo nivel de aislamiento de instantánea que proporciona una coherencia de lectura en las transacciones.A new isolation level, snapshot, to provide transaction-level read consistency.

La base de datos tempdb debe tener espacio suficiente para el almacén de versiones.The tempdb database must have enough space for the version store. Cuando tempdb esté llena, las operaciones de actualización dejarán de generar versiones y se continuarán funcionando correctamente, pero es posible que las operaciones de lectura provoquen errores porque es necesaria una determinada versión de fila que ya no existe.When tempdb is full, update operations will stop generating versions and continue to succeed, but read operations might fail because a particular row version that is needed no longer exists. Esto afecta a las operaciones como los desencadenadores, MARS y los índices en línea.This affects operations like triggers, MARS, and online indexing.

La utilización de versiones de fila para las transacciones de lectura confirmada e instantáneas es un proceso de dos pasos:Using row versioning for read-committed and snapshot transactions is a two-step process:

  1. Establezca una o dos de las opciones de base de datos (READ_COMMITTED_SNAPSHOT y ALLOW_SNAPSHOT_ISOLATION) en ON.Set either or both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options ON.

  2. Seleccione el nivel de aislamiento de transacción apropiado en una aplicación:Set the appropriate transaction isolation level in an application:

    • Cuando el valor de la opción de base de datos READ_COMMITTED_SNAPSHOT sea ON, las transacciones que establezcan el nivel de aislamiento de lectura confirmada utilizarán las versiones de fila.When the READ_COMMITTED_SNAPSHOT database option is ON, transactions setting the read committed isolation level use row versioning.
    • Cuando el valor de la opción de base de datos ALLOW_SNAPSHOT_ISOLATION sea ON, las transacciones podrán establecer el nivel de aislamiento de instantánea.When the ALLOW_SNAPSHOT_ISOLATION database option is ON, transactions can set the snapshot isolation level.

Cuando el valor de la opción de base de datos READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION está establecido en ON, el Motor de base de datos de SQL ServerSQL Server Database Engine asignará un número de secuencia de la transacción (XSN) a cada transacción que manipule datos que utilicen las versiones de fila.When either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is set ON, the Motor de base de datos de SQL ServerSQL Server Database Engine assigns a transaction sequence number (XSN) to each transaction that manipulates data using row versioning. Las transacciones empiezan en el momento en que se ejecuta una instrucción BEGIN TRANSACTION.Transactions start at the time a BEGIN TRANSACTION statement is executed. No obstante, el número de secuencia de la transacción empieza con la primera operación de lectura/escritura después de la instrucción BEGIN TRANSACTION.However, the transaction sequence number starts with the first read or write operation after the BEGIN TRANSACTION statement. El número de secuencia de la transacción aumenta en incrementos de uno cada vez que se asigna.The transaction sequence number is incremented by one each time it is assigned.

Cuando el valor de las opciones de base de datos READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION, se mantienen las copias lógicas (versiones) para todas las modificaciones de datos realizadas en la base de datos.When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Cada vez que se modifica una fila mediante una transacción determinada, la instancia del Motor de base de datos de SQL ServerSQL Server Database Engine almacena una versión de la imagen previamente confirmada de la fila en tempdb.Every time a row is modified by a specific transaction, the instance of the Motor de base de datos de SQL ServerSQL Server Database Engine stores a version of the previously committed image of the row in tempdb. Cada versión se marca con el número de secuencia de la transacción que realizó el cambio.Each version is marked with the transaction sequence number of the transaction that made the change. Las versiones de filas modificadas se encadenan mediante una lista de vínculos.The versions of modified rows are chained using a link list. El valor de fila más reciente se almacena siempre en la base de datos actual y se encadena a las filas de versiones almacenadas en tempdb.The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

Nota

En los casos de modificación de objetos grandes (LOB), solo se copia el fragmento cambiado al almacén de versiones de tempdb.For modification of large objects (LOBs), only the changed fragment is copied to the version store in tempdb.

Las versiones de fila se conservan durante un tiempo suficiente para cumplir los requisitos de las transacciones ejecutadas con niveles de aislamiento basados en versiones de fila.Row versions are held long enough to satisfy the requirements of transactions running under row versioning-based isolation levels. El Motor de base de datos de SQL ServerSQL Server Database Engine realiza un seguimiento del número de secuencia de la transacción útil más antiguo y elimina periódicamente todas las versiones de filas marcadas con números de secuencia de la transacción anteriores al número de secuencia útil más antiguo.The Motor de base de datos de SQL ServerSQL Server Database Engine tracks the earliest useful transaction sequence number and periodically deletes all row versions stamped with transaction sequence numbers that are lower than the earliest useful sequence number.

Cuando el valor de ambas opciones de base de datos es OFF, solo se crean versiones de las filas modificadas por desencadenadores o sesiones MARS, o bien leídas por operaciones de índice ONLINE.When both database options are set to OFF, only rows modified by triggers or MARS sessions, or read by ONLINE index operations, are versioned. Estas versiones de filas se liberan cuando dejan de ser necesarias.Those row versions are released when no longer needed. Un subproceso en segundo plano se ejecuta periódicamente para eliminar las versiones de filas obsoletas.A background thread periodically executes to remove stale row versions.

Nota

En el caso de las transacciones de ejecución breve, puede que se almacene en caché una versión de una fila modificada en el grupo de búferes sin que se escriba en los archivos de disco de la base de datos tempdb.For short-running transactions, a version of a modified row may get cached in the buffer pool without getting written into the disk files of the tempdb database. Si la fila versionada no va a ser necesaria durante mucho tiempo, simplemente se eliminará del grupo de búferes y puede que no provoque una sobrecarga de E/S.If the need for the versioned row is short-lived, it will simply get dropped from the buffer pool and may not necessarily incur I/O overhead.

Comportamiento durante la lectura de datosBehavior when reading data

Cuando las transacciones que se ejecutan con niveles de aislamiento basados en versiones de fila leen datos, las operaciones de lectura no adquieren bloqueos compartidos (S) para los datos que se leen, por lo que no bloquean las transacciones que están modificando datos.When transactions running under row versioning-based isolation read data, the read operations do not acquire shared (S) locks on the data being read, and therefore do not block transactions that are modifying data. Asimismo, se minimiza la sobrecarga de los recursos de bloqueo, ya que se reduce el número de bloqueos adquiridos.Also, the overhead of locking resources is minimized as the number of locks acquired is reduced. El aislamiento de lectura confirmada mediante las versiones de fila y el aislamiento de instantánea están diseñados para proporcionar una coherencia de lectura de datos con versiones en las instrucciones o las transacciones.Read committed isolation using row versioning and snapshot isolation are designed to provide statement-level or transaction-level read consistencies of versioned data.

Todas las consultas, incluidas las transacciones que se ejecutan en niveles de aislamiento basados en versiones de fila, adquieren bloqueos de estabilidad del esquema (Sch-S) durante la compilación y la ejecución.All queries, including transactions running under row versioning-based isolation levels, acquire Sch-S (schema stability) locks during compilation and execution. Debido a ello, las consultas se bloquean cuando una transacción simultánea aloja un bloqueo de modificación del esquema (Sch-M) en la tabla.Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. Por ejemplo, una operación de lenguaje de definición de datos (DDL) adquiere un bloqueo Sch-M antes de modificar la información del esquema de la tabla.For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Las transacciones de consulta, incluidas las que se ejecutan en un nivel de aislamiento basado en versiones de fila, se bloquean cuando se intenta adquirir un bloqueo Sch-S.Query transactions, including those running under a row versioning-based isolation level, are blocked when attempting to acquire a Sch-S lock. A la inversa, una consulta que mantiene un bloqueo Sch-S bloquea una transacción simultánea que intenta adquirir un bloqueo Sch-M.Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.

Cuando se inicia una transacción con el nivel de aislamiento de instantánea, la instancia del Motor de base de datos de SQL ServerSQL Server Database Engine registra todas las transacciones actualmente activas.When a transaction using the snapshot isolation level starts, the instance of the Motor de base de datos de SQL ServerSQL Server Database Engine records all of the currently active transactions. Cuando la transacción de instantánea lee una fila que tiene una cadena de versiones, el Motor de base de datos de SQL ServerSQL Server Database Engine sigue la cadena y recupera la fila en la que el número de secuencia de la transacción cumple las condiciones siguientes:When the snapshot transaction reads a row that has a version chain, the Motor de base de datos de SQL ServerSQL Server Database Engine follows the chain and retrieves the row where the transaction sequence number is:

  • Es el más cercano al número de secuencia de la transacción de instantánea que lee la fila, pero inferior al mismo.Closest to but lower than the sequence number of the snapshot transaction reading the row.

  • No se encuentra en la lista de transacciones activas cuando se inició la transacción de instantánea.Not in the list of the transactions active when the snapshot transaction started.

Las operaciones de lectura realizadas por una transacción de instantánea recuperan la versión más reciente de cada fila confirmada en el momento en el que empezó la transacción de instantánea.Read operations performed by a snapshot transaction retrieve the last version of each row that had been committed at the time the snapshot transaction started. De este modo se consigue una instantánea coherente con las transacciones de los datos tal como existían en el momento de inicio de la transacción.This provides a transactionally consistent snapshot of the data as it existed at the start of the transaction.

Las transacciones de lectura confirmada que utilizan versiones de fila funcionan de forma muy parecida.Read-committed transactions using row versioning operate in much the same way. La diferencia es que las transacciones de lectura confirmada no utilizan su propio número de secuencia de la transacción cuando eligen versiones de filas.The difference is that the read-committed transaction does not use its own transaction sequence number when choosing row versions. Cada vez que se inicia una instrucción, la transacción de lectura confirmada lee el número de secuencia de la transacción más reciente emitido para esa instancia del Motor de base de datos de SQL ServerSQL Server Database Engine.Each time a statement is started, the read-committed transaction reads the latest transaction sequence number issued for that instance of the Motor de base de datos de SQL ServerSQL Server Database Engine. Éste es el número de secuencia de la transacción utilizado para seleccionar las versiones de filas correctas para esa instrucción.This is the transaction sequence number used to select the correct row versions for that statement. Esto permite a las transacciones de lectura confirmada ver una instantánea de los datos tal como existían en el momento de inicio de cada instrucción.This allows read-committed transactions to see a snapshot of the data as it exists at the start of each statement.

Nota

Aunque las transacciones de lectura confirmada que utilizan las versiones de fila proporcionan una vista de los datos en el nivel de instrucciones coherente desde el punto de vista de las transacciones, las versiones de fila que se generan o a las que se tiene acceso con este tipo de transacción se mantienen hasta que la transacción finaliza.Even though read-committed transactions using row versioning provides a transactionally consistent view of the data at a statement level, row versions generated or accessed by this type of transaction are maintained until the transaction completes.

Comportamiento durante la modificación de datosBehavior when modifying data

En las transacciones de lectura confirmada que utilizan las versiones de fila, la selección de las filas que se deben actualizar se realiza mediante un recorrido de bloqueo en el que se obtiene un bloqueo de actualización (U) en la fila de datos cuando se leen los valores de datos.In a read-committed transaction using row versioning, the selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read. Es lo mismo que una transacción de lectura confirmada que no utiliza las versiones de fila.This is the same as a read-committed transaction that does not use row versioning. Si la fila de datos no cumple los criterios de actualización, se liberará el bloqueo de actualización en esa fila y se bloqueará y recorrerá la siguiente.If the data row does not meet the update criteria, the update lock is released on that row and the next row is locked and scanned.

Las transacciones que se ejecutan con aislamiento de instantánea obtienen un enfoque optimista de la modificación de datos mediante la adquisición de bloqueos de datos antes de realizar la modificación solo para forzar restricciones.Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. De lo contrario, los bloqueos no se adquieren en los datos hasta que se van a modificar los datos.Otherwise, locks are not acquired on data until the data is to be modified. Cuando una fila de datos cumple los criterios de actualización, la transacción de instantánea comprueba que la fila de datos no haya sido modificada por una transacción simultánea confirmada antes de que empezara la transacción de instantánea.When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. Si se ha modificado la fila de datos fuera de la transacción de instantánea, se producirá un conflicto de actualizaciones y se finalizará la transacción de instantánea.If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. El Motor de base de datos de SQL ServerSQL Server Database Engine controla el conflicto de actualizaciones; no se puede deshabilitar su detección.The update conflict is handled by the Motor de base de datos de SQL ServerSQL Server Database Engine and there is no way to disable the update conflict detection.

Nota

Las operaciones de actualización que se ejecutan con aislamiento de instantánea se ejecutan internamente con aislamiento de lectura confirmada cuando la transacción de instantánea tiene acceso a cualquiera de los elementos siguientes:Update operations running under snapshot isolation internally execute under read committed isolation when the snapshot transaction accesses any of the following:

Una tabla con una restricción FOREIGN KEY.A table with a FOREIGN KEY constraint.

Una tabla a la que se hace referencia en la restricción FOREIGN KEY de otra tabla.A table that is referenced in the FOREIGN KEY constraint of another table.

Una vista indizada que hace referencia a más de una tabla.An indexed view referencing more than one table.

No obstante, incluso en estas condiciones, la operación de actualización seguirá comprobando que los datos no hayan sido modificados por otra transacción.However, even under these conditions the update operation will continue to verify that the data has not been modified by another transaction. Si se han modificado, la transacción de instantánea detectará un conflicto de actualización y terminará.If data has been modified by another transaction, the snapshot transaction encounters an update conflict and is terminated.

Resumen del comportamientoBehavior in summary

En la tabla siguiente se resumen las diferencias entre el aislamiento de instantánea y el aislamiento de lectura confirmada mediante las versiones de fila.The following table summarizes the differences between snapshot isolation and read committed isolation using row versioning.

PropiedadProperty Nivel de aislamiento de lectura confirmada mediante las versiones de filaRead-committed isolation level using row versioning Nivel de aislamiento de instantáneaSnapshot isolation level
La opción de base de datos cuyo valor debe ser ON para habilitar la compatibilidad necesaria.The database option that must be set to ON to enable the required support. READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION
Forma en la que una sesión solicita el tipo específico de versiones de fila.How a session requests the specific type of row versioning. Utilice el nivel de aislamiento de lectura confirmada predeterminado o ejecute la instrucción SET TRANSACTION ISOLATION LEVEL para especificar el nivel de aislamiento READ COMMITTED.Use the default read-committed isolation level, or run the SET TRANSACTION ISOLATION LEVEL statement to specify the READ COMMITTED isolation level. Se puede hacer una vez iniciada la transacción.This can be done after the transaction starts. Requiere la ejecución de SET TRANSACTION ISOLATION LEVEL para especificar el nivel de aislamiento SNAPSHOT antes de que se inicie otra transacción.Requires the execution of SET TRANSACTION ISOLATION LEVEL to specify the SNAPSHOT isolation level before the start of the transaction.
La versión de los datos leídos por las instrucciones.The version of data read by statements. Todos los datos confirmados antes del inicio de cada instrucción.All data that was committed before the start of each statement. Todos los datos confirmados antes del inicio de cada transacción.All data that was committed before the start of each transaction.
Modo de control de las actualizaciones.How updates are handled. Vuelve desde las versiones de filas a los datos reales para seleccionar las filas que se actualizarán y utiliza bloqueos de actualización en las filas de datos seleccionadas.Reverts from row versions to actual data to select rows to update and uses update locks on the data rows selected. Adquiere bloqueos exclusivos en las filas de datos reales que se modificarán.Acquires exclusive locks on actual data rows to be modified. Sin detección de conflictos de actualizaciones.No update conflict detection. Utiliza las versiones de filas para seleccionar las filas que se actualizarán.Uses row versions to select rows to update. Intenta adquirir un bloqueo exclusivo en la fila de datos real que se modificará y, si otra transacción ha modificado los datos, se producirá un conflicto de actualizaciones y se finalizará la transacción de instantánea.Tries to acquire an exclusive lock on the actual data row to be modified, and if the data has been modified by another transaction, an update conflict occurs and the snapshot transaction is terminated.
Detección de conflictos de actualizaciones.Update conflict detection. Ninguno.None. Compatibilidad integrada.Integrated support. No se puede deshabilitar.Cannot be disabled.

Uso de recursos de versiones de filaRow Versioning resource usage

El marco de las versiones de fila admite las siguientes características disponibles en SQL ServerSQL Server:The row versioning framework supports the following features available in SQL ServerSQL Server:

  • DesencadenadoresTriggers
  • Conjuntos de resultados activos múltiples (MARS)Multiple Active Results Sets (MARS)
  • Índices en líneaOnline indexing

El marco de las versiones de fila también admite los siguientes niveles de aislamiento de transacción basado en las versiones de fila que, de forma predeterminada, no se habilitan:The row versioning framework also supports the following row versioning-based transaction isolation levels, which by default are not enabled:

  • Cuando la opción de base de datos READ_COMMITTED_SNAPSHOT es ON, las transacciones READ_COMMITTED proporcionan coherencia de lectura de nivel de instrucciones con versiones de fila.When the READ_COMMITTED_SNAPSHOT database option is ON, READ_COMMITTED transactions provide statement-level read consistency using row versioning.
  • Cuando la opción de base de datos ALLOW_SNAPSHOT_ISOLATION es ON, las transacciones SNAPSHOT proporcionan coherencia de lectura de nivel de transacciones con versiones de fila.When the ALLOW_SNAPSHOT_ISOLATION database option is ON, SNAPSHOT transactions provide transaction-level read consistency using row versioning.

Los niveles de aislamiento basado en las versiones de fila reducen el número de bloqueos adquiridos por transacción mediante la eliminación del uso de bloqueos compartidos en operaciones de lectura.Row versioning-based isolation levels reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. Esto aumenta el rendimiento del sistema al reducir los recursos utilizados para administrar bloqueos.This increases system performance by reducing the resources used to manage locks. El rendimiento también aumenta al reducir el número de veces que una transacción se bloquea mediante bloqueos adquiridos por otras transacciones.Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions.

Los niveles de aislamiento basados en las versiones de fila aumentan los recursos necesarios para la modificación de datos.Row versioning-based isolation levels increase the resources needed by data modifications. Al habilitar estas opciones se crean versiones de filas de todas las modificaciones de datos para la base de datos.Enabling these options causes all data modifications for the database to be versioned. Se guarda una copia de los datos sin modificar en tempdb aunque no haya ninguna transacción activa que utilice el aislamiento basado en las versiones de fila.A copy of the data before modification is stored in tempdb even when there are no active transactions using row versioning-based isolation. Los datos modificados incluyen un puntero a los datos con versiones almacenados en tempdb.The data after modification includes a pointer to the versioned data stored in tempdb. En el caso de objetos grandes, solo se copia en tempdb la parte del objeto modificada.For large objects, only part of the object that changed is copied to tempdb.

Espacio utilizado en TempDBSpace used in TempDB

En cada instancia de Motor de base de datos de SQL ServerSQL Server Database Engine, tempdb debe disponer de espacio suficiente para contener las versiones de fila generadas por todas las bases de datos de la instancia.For each instance of the Motor de base de datos de SQL ServerSQL Server Database Engine, tempdb must have enough space to hold the row versions generated for every database in the instance. El administrador de la base de datos debe asegurarse de que TempDB cuenta con espacio más que suficiente para dar cabida al almacén de versiones.The database administrator must ensure that TempDB has ample space to support the version store. Existen dos almacenes de versiones en TempDB:There are two version stores in TempDB:

  • El almacén de versiones de generación de índices en línea se utiliza para generar índices en línea en todas las bases de datos.The online index build version store is used for online index builds in all databases.
  • El almacén de versiones común se utiliza en las demás operaciones de modificación de datos de todas las bases de datos.The common version store is used for all other data modification operations in all databases.

Las versiones de filas deben estar almacenadas mientras una transacción activa necesite tener acceso a ella.Row versions must be stored for as long as an active transaction needs to access it. Cada minuto, un subproceso en segundo plano elimina las versiones de filas que ya no se necesitan y libera el espacio de versiones en TempDB.Once every minute, a background thread removes row versions that are no longer needed and frees up the version space in TempDB. Una transacción de larga duración impide que se libere el espacio del almacén de versiones si se cumple alguna de las siguientes condiciones:A long-running transaction prevents space in the version store from being released if it meets any of the following conditions:

  • Se utiliza el aislamiento basado en las versiones de fila.It uses row versioning-based isolation.
  • Se utilizan desencadenadores, MARS u operaciones de generación de índices en línea.It uses triggers, MARS, or online index build operations.
  • Se generan versiones de filas.It generates row versions.

Nota

Cuando se invoca un desencadenador dentro de una transacción, las versiones de filas creadas por el desencadenador se mantienen hasta el final de la transacción, incluso cuando las versiones de filas dejan de necesitarse una vez completado el desencadenador.When a trigger is invoked inside a transaction, the row versions created by the trigger are maintained until the end of the transaction, even though the row versions are no longer needed after the trigger completes. Esto también se aplica a las transacciones de lectura confirmada que usan las versiones de fila.This also applies to read-committed transactions that use row versioning. Con este tipo de transacción, solo se necesita una vista de la base de datos transaccionalmente coherente para cada instrucción de la transacción.With this type of transaction, a transactionally consistent view of the database is needed only for each statement in the transaction. De este modo, las versiones de filas creadas para una instrucción de la transacción dejan de necesitarse una vez completada la instrucción.This means that the row versions created for a statement in the transaction are no longer needed after the statement completes. No obstante, las versiones de filas creadas por cada instrucción de la transacción se mantienen hasta que se complete la transacción.However, row versions created by each statement in the transaction are maintained until the transaction completes.

Cuando TempDB se queda sin espacio, Motor de base de datos de SQL ServerSQL Server Database Engine fuerza la reducción de los almacenes de versiones.When TempDB runs out of space, the Motor de base de datos de SQL ServerSQL Server Database Engine forces the version stores to shrink. Durante el proceso de reducción, las transacciones de mayor duración que todavía no han generado versiones de filas se marcan como sujetos.During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. Se genera el mensaje 3967 en el registro de errores para cada transacción marcada como sujeto.A message 3967 is generated in the error log for each victim transaction. Si una traducción se marca como sujeto, no podrá leer las versiones de filas del almacén de versiones.If a transaction is marked as a victim, it can no longer read the row versions in the version store. Cuando intenta leer versiones de filas, se genera el mensaje 3966 y la transacción se revierte.When it attempts to read row versions, message 3966 is generated and the transaction is rolled back. Si el proceso de reducción se realiza correctamente, pasa a quedar espacio disponible en tempdb.If the shrinking process succeeds, space becomes available in tempdb. Si no, tempdb se queda sin espacio y se produce lo siguiente:Otherwise, tempdb runs out of space and the following occurs:

  • Las operaciones de escritura continúan, pero no generan versiones.Write operations continue to execute but do not generate versions. Aparece un mensaje informativo (3959) en el registro de errores, pero la transacción que escribe datos no se ve afectada.An information message (3959) appears in the error log, but the transaction that writes data is not affected.

  • Las transacciones que intentan obtener acceso a versiones de filas que no se generaron debido a una reversión completa de tempdb terminan en un error 3958.Transactions that attempt to access row versions that were not generated because of a tempdb full rollback terminate with an error 3958.

Espacio utilizado en filas de datosSpace used in data rows

Cada fila de base de datos puede utilizar hasta 14 bytes al final de la fila para información de las versiones de fila.Each database row may use up to 14 bytes at the end of the row for row versioning information. La información de versiones de fila contiene el número de secuencia de la transacción que confirmó la versión y el puntero a la fila cuya versión se ha creado.The row versioning information contains the transaction sequence number of the transaction that committed the version and the pointer to the versioned row. Estos 14 bytes se agregan la primera vez que se modifica una fila o se inserta una nueva fila, si se cumple alguna de las siguientes condiciones:These 14 bytes are added the first time the row is modified, or when a new row is inserted, under any of these conditions:

  • Las opciones READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION están en ON.READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION options are ON.
  • La tabla tiene un desencadenador.The table has a trigger.
  • Se utilizan conjuntos de resultados activos múltiples (MARS)Multiple Active Results Sets (MARS) is being used.
  • En la actualidad, se ejecutan en la tabla operaciones de compilación de índices en línea.Online index build operations are currently running on the table.

Estos 14 bytes se eliminan de la fila de base de datos la primera vez que se modifica la fila si se cumplen todas estas condiciones:These 14 bytes are removed from the database row the first time the row is modified under all of these conditions:

  • Las opciones READ_COMMITTED_SNAPSHOT y ALLOW_SNAPSHOT_ISOLATION están en OFF.READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options are OFF.
  • El desencadenador ya no existe en la tabla.The trigger no longer exists on the table.
  • No se utiliza MARS.MARS is not being used.
  • No se ejecutan en ese momento operaciones de generación de índices en línea.Online index build operations are not currently running.

Si se utiliza alguna de las características de versiones de fila, puede que sea necesario asignar espacio de disco adicional para que la base de datos dé cabida a los 14 bytes por fila de base de datos.If you use any of the row versioning features, you might need to allocate additional disk space for the database to accommodate the 14 bytes per database row. Al agregar información de versiones de fila puede provocarse la división de la página de índices o la asignación de una nueva página de datos si no hay suficiente especio disponible en la página actual.Adding the row versioning information can cause index page splits or the allocation of a new data page if there is not enough space available on the current page. Por ejemplo, si la longitud media de fila es 100 bytes, los 14 bytes adicionales hacen que una tabla existente crezca hasta un 14 por ciento.For example, if the average row length is 100 bytes, the additional 14 bytes cause an existing table to grow up to 14 percent.

Si se reduce el factor de relleno, se puede impedir o reducir la fragmentación de las páginas de índice.Decreasing the fill factor might help to prevent or decrease fragmentation of index pages. Para ver información sobre la fragmentación de los datos e índices de una tabla o vista, puede usar sys.dm_db_index_physical_stats.To view fragmentation information for the data and indexes of a table or view, you can use sys.dm_db_index_physical_stats.

Espacio utilizado en objetos grandesSpace used in Large Objects

Motor de base de datos de SQL ServerSQL Server Database Engine admite seis tipos de datos que pueden contener cadenas grandes de hasta dos gigabytes (GB) de longitud: nvarchar(max), varchar(max), varbinary(max), ntext, text e image.The Motor de base de datos de SQL ServerSQL Server Database Engine supports six data types that can hold large strings up to 2 gigabytes (GB) in length: nvarchar(max), varchar(max), varbinary(max), ntext, text, and image. Las cadenas grandes almacenadas con estos tipos de datos se almacenan en una serie de fragmentos de datos que se vinculan a la fila de datos.Large strings stored using these data types are stored in a series of data fragments that are linked to the data row. La información de versiones de fila se almacena en cada uno de los fragmentos utilizados para almacenar estas cadenas grandes.Row versioning information is stored in each fragment used to store these large strings. Los fragmentos de datos son una colección de páginas dedicadas a objetos grandes en una tabla.Data fragments are a collection of pages dedicated to large objects in a table.

A medida que se agregan nuevos valores grandes a una base de datos, se asignan utilizando un máximo de 8.040 bytes de datos por fragmento.As new large values are added to a database, they are allocated using a maximum of 8040 bytes of data per fragment. En versiones anteriores del Motor de base de datos de SQL ServerSQL Server Database Engine se almacenaban hasta 8.080 bytes de datos ntext, text o image por fragmento.Earlier versions of the Motor de base de datos de SQL ServerSQL Server Database Engine stored up to 8080 bytes of ntext, text, or image data per fragment.

Los datos de objetos grandes (LOB) ntext, text e image existentes no se actualizan para dejar espacio para la información de versiones de fila cuando una base de datos se actualiza a SQL ServerSQL Server desde una versión anterior de SQL ServerSQL Server.Existing ntext, text, and image large object (LOB) data is not updated to make space for the row versioning information when a database is upgraded to SQL ServerSQL Server from an earlier version of SQL ServerSQL Server. Sin embargo, la primera vez que se modifican los datos de LOB, se actualizan dinámicamente para habilitar el almacenamiento de información del control de versiones.However, the first time the LOB data is modified, it is dynamically upgraded to enable storage of versioning information. Esto sucederá aunque no se generen versiones de filas.This will happen even if row versions are not generated. Una vez actualizados los datos de LOB, el número máximo de bytes almacenados por fragmento se reduce de 8.080 bytes a 8.040 bytes.After the LOB data is upgraded, the maximum number of bytes stored per fragment is reduced from 8080 bytes to 8040 bytes. El proceso de actualización es equivalente a eliminar el valor de LOB y volver a insertar el mismo valor.The upgrade process is equivalent to deleting the LOB value and reinserting the same value. Los datos de LOB se actualizan aunque solo se haya modificado un solo byte.The LOB data is upgraded even if only one byte is modified. Esta operación se realiza una sola vez para cada columna ntext, text o image, pero, dependiendo del tamaño de los datos de LOB, puede que cada operación genere gran cantidad de asignaciones de página y actividad de E/S.This is a one-time operation for each ntext, text, or image column, but each operation may generate a large amount of page allocations and I/O activity depending upon the size of the LOB data. Puede que también se genere gran cantidad de actividad de registro si la modificación se registra por completo.It may also generate a large amount of logging activity if the modification is fully logged. Las operaciones WRITETEXT y UPDATETEXT se registran mínimamente si el modo de recuperación de la base de datos no se establece en FULL.WRITETEXT and UPDATETEXT operations are minimally logged if database recovery mode is not set to FULL.

Los tipos de datos nvarchar(max), varchar(max) y varbinary(max) no están disponibles en versiones anteriores de SQL ServerSQL Server.The nvarchar(max), varchar(max), and varbinary(max) data types are not available in earlier versions of SQL ServerSQL Server. Por lo tanto, no presentan problemas de actualización.Therefore, they have no upgrade issues.

Debe asignarse suficiente espacio de disco para dar cabida a este requisito.Enough disk space should be allocated to accommodate this requirement.

Supervisar las versiones de fila y el almacén de versionesMonitoring Row Versioning and the Version Store

Para los procesos de supervisión de versiones de fila, almacén de versiones y aislamiento de instantánea en cuanto al rendimiento y otros problemas, SQL ServerSQL Server proporciona herramientas en forma de Vistas de administración dinámica (DMV) y contadores de rendimiento del Monitor de sistema de Windows.For monitoring row versioning, version store, and snapshot isolation processes for performance and problems, SQL ServerSQL Server provides tools in the form of Dynamic Management Views (DMVs) and performance counters in Windows System Monitor.

DMVDMVs

Las siguientes DMV proporcionan información sobre el estado actual del sistema de tempdb y el almacén de versiones, así como de las transacciones que utilizan las versiones de fila.The following DMVs provide information about the current system state of tempdb and the version store, as well as transactions using row versioning.

sys.dm_db_file_space_usage.sys.dm_db_file_space_usage. Devuelve información de uso del espacio para cada fila de la base de datos.Returns space usage information for each file in the database. Para obtener más información, consulte sys.dm_db_file_space_usage (Transact-SQL).For more information, see sys.dm_db_file_space_usage (Transact-SQL).

sys.dm_db_session_space_usage.sys.dm_db_session_space_usage. Devuelve la actividad de asignación y desasignación de páginas por sesión de la base de datos.Returns page allocation and deallocation activity by session for the database. Para obtener más información, consulte sys.dm_db_session_space_usage (Transact-SQL).For more information, see sys.dm_db_session_space_usage (Transact-SQL).

sys.dm_db_task_space_usage.sys.dm_db_task_space_usage. Devuelve la actividad de asignación y desasignación de páginas por tarea de la base de datos.Returns page allocation and deallocation activity by task for the database. Para obtener más información, consulte sys.dm_db_task_space_usage (Transact-SQL).For more information, see sys.dm_db_task_space_usage (Transact-SQL).

sys.dm_tran_top_version_generators.sys.dm_tran_top_version_generators. Devuelve una tabla virtual para los objetos que producen la mayoría de las versiones del almacén de versiones.Returns a virtual table for the objects producing the most versions in the version store. Agrupa las 256 longitudes de registro principales agregadas mediante su database_id y rowset_id.It groups the top 256 aggregated record lengths by database_id and rowset_id. Use esta función para encontrar los principales consumidores del almacén de versiones.Use this function to find the largest consumers of the version store. Para obtener más información, consulte sys.dm_tran_top_version_generators (Transact-SQL).For more information, see sys.dm_tran_top_version_generators (Transact-SQL).

sys.dm_tran_version_store.sys.dm_tran_version_store. Devuelve una tabla virtual que muestra todos los registros de versión del almacén de versiones común.Returns a virtual table that displays all version records in the common version store. Para obtener más información, consulte sys.dm_tran_version_store (Transact-SQL).For more information, see sys.dm_tran_version_store (Transact-SQL).

sys.dm_tran_version_store_space_usage.sys.dm_tran_version_store_space_usage. Devuelve una tabla virtual que muestra el espacio total en tempdb usado por los registros del almacén de versiones para cada base de datos.Returns a virtual table that displays the total space in tempdb used by version store records for each database. Para obtener más información, consulte sys.dm_tran_version_store_space_usage (Transact-SQL).For more information, see sys.dm_tran_version_store_space_usage (Transact-SQL).

Nota

La ejecución de las funciones sys.dm_tran_top_version_generators y sys.dm_tran_version_store puede ser muy costosa, puesto que ambas consultan todo el almacén de versiones, que puede ser muy grande.sys.dm_tran_top_version_generators and sys.dm_tran_version_store are potentially very expensive functions to run, since both query the entire version store, which could be very large.
La función sys.dm_tran_version_store_space_usage es eficaz y económica de ejecutar, ya que no navega por registros individuales del almacén de versiones y devuelve el espacio agregado del almacén de versiones que se ha utilizado en tempdb por base de datos.sys.dm_tran_version_store_space_usage is efficient and not expensive to run, as it does not navigate through individual version store records and returns aggregated version store space consumed in tempdb per database

sys.dm_tran_active_snapshot_database_transactions.sys.dm_tran_active_snapshot_database_transactions. Devuelve una tabla virtual para todas las transacciones activas de todas las bases de datos en la instancia de SQL ServerSQL Server que utiliza versiones de fila.Returns a virtual table for all active transactions in all databases within the SQL ServerSQL Server instance that use row versioning. Las transacciones del sistema no aparecen en esta DMV.System transactions do not appear in this DMV. Para obtener más información, consulte sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).For more information, see sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

sys.dm_tran_transactions_snapshot.sys.dm_tran_transactions_snapshot. Devuelve una tabla virtual que muestra las instantáneas tomadas por cada transacción.Returns a virtual table that displays snapshots taken by each transaction. La instantánea contiene el número de secuencia de las transacciones activas que utilizan versiones de fila.The snapshot contains the sequence number of the active transactions that use row versioning. Para obtener más información, consulte sys.dm_tran_transactions_snapshot (Transact-SQL).For more information, see sys.dm_tran_transactions_snapshot (Transact-SQL).

sys.dm_tran_current_transaction.sys.dm_tran_current_transaction. Devuelve una sola fila que muestra información de estado relacionada con las versiones de fila de la transacción de la sesión actual.Returns a single row that displays row versioning-related state information of the transaction in the current session. Para obtener más información, consulte sys.dm_tran_current_transaction (Transact-SQL).For more information, see sys.dm_tran_current_transaction (Transact-SQL).

sys.dm_tran_current_snapshot.sys.dm_tran_current_snapshot. Devuelve una tabla virtual que muestra todas las transacciones activas en el momento en que se inicia la transacción actual de aislamiento de instantánea.Returns a virtual table that displays all active transactions at the time the current snapshot isolation transaction starts. Si la transacción actual está usando un aislamiento de instantáneas, esta función no devuelve filas.If the current transaction is using snapshot isolation, this function returns no rows. La función sys.dm_tran_current_snapshot es similar a sys.dm_tran_transactions_snapshot, excepto en que solo devuelve las transacciones activas de la instantánea actual.sys.dm_tran_current_snapshot is similar to sys.dm_tran_transactions_snapshot, except that it returns only the active transactions for the current snapshot. Para obtener más información, consulte sys.dm_tran_current_snapshot (Transact-SQL).For more information, see sys.dm_tran_current_snapshot (Transact-SQL).

Contadores de rendimientoPerformance Counters

Los contadores de rendimiento de SQL ServerSQL Server proporcionan información sobre el rendimiento del sistema afectado por los procesos de SQL ServerSQL Server.SQL ServerSQL Server performance counters provide information about the system performance impacted by SQL ServerSQL Server processes. Los siguientes contadores de rendimiento supervisan tempdb y el almacén de versiones, así como las transacciones que utilizan versiones de fila.The following performance counters monitor tempdb and the version store, as well as transactions using row versioning. Los contadores de rendimiento se encuentran en el objeto de rendimiento SQLServer:Transactions.The performance counters are contained in the SQLServer:Transactions performance object.

Espacio disponible en tempdb (KB) .Free Space in tempdb (KB). Supervisa la cantidad, en kilobytes (KB), de espacio libre en la base de datos tempdb.Monitors the amount, in kilobytes (KB), of free space in the tempdb database. Debe haber suficiente espacio libre en tempdb para controlar el almacén de versiones que admite aislamiento de instantánea.There must be enough free space in tempdb to handle the version store that supports snapshot isolation.

La fórmula siguiente ofrece una estimación aproximada del tamaño del almacén de versiones.The following formula provides a rough estimate of the size of the version store. En el caso de transacciones de larga duración, puede que sea conveniente supervisar la velocidad de generación y limpieza para estimar el tamaño máximo del almacén de versiones.For long-running transactions, it may be useful to monitor the generation and cleanup rate to estimate the maximum size of the version store.

[tamaño del almacén de versiones común] = 2 * [datos del almacén de versiones generados por minuto] * [mayor tiempo de ejecución (minutos) de la transacción][size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

El mayor tiempo de ejecución de transacciones no debe incluir generaciones de índices en línea.The longest running time of transactions should not include online index builds. Dado que estas operaciones pueden llevar mucho tiempo en tablas muy grandes, las generaciones de índices en línea utilizan un almacén de versiones independiente.Because these operations may take a long time on very large tables, online index builds use a separate version store. El tamaño aproximado del almacén de versiones de generaciones de índices en línea equivale a la cantidad de datos modificados en la tabla, incluidos todos los índices, mientras la generación de índices en línea esté activa.The approximate size of the online index build version store equals the amount of data modified in the table, including all indexes, while the online index build is active.

Tamaño de almacén de versiones (KB) .Version Store Size (KB). Supervisa el tamaño en KB de todos los almacenes de versiones.Monitors the size in KB of all version stores. Esta información ayuda a determinar el espacio necesario para el almacén de versiones en la base de datos tempdb.This information helps determine the amount of space needed in the tempdb database for the version store. La supervisión de este contador durante cierto tiempo proporciona una estimación útil del espacio adicional necesario para tempdb.Monitoring this counter over a period of time provides a useful estimate of additional space needed for tempdb.

Version Generation rate (KB/s).Version Generation rate (KB/s). Supervisa la velocidad de generación de versión en KB por segundo en todos los almacenes de versiones.Monitors the version generation rate in KB per second in all version stores.

Version Cleanup rate (KB/s).Version Cleanup rate (KB/s). Supervisa la velocidad de limpieza de versión en KB por segundo en todos los almacenes de versiones.Monitors the version cleanup rate in KB per second in all version stores.

Nota

La información procedente de Velocidad de generación de versión (KB/seg.) y Velocidad de limpieza de versión (KB/seg.) se puede utilizar para predecir los requisitos de espacio de tempdb.Information from Version Generation rate (KB/s) and Version Cleanup rate (KB/s) can be used to predict tempdb space requirements.

Recuento de unidad de almacén de versiones.Version Store unit count. Supervisa el recuento de unidades del almacén de versiones.Monitors the count of version store units.

Creación de unidad de almacén de versiones.Version Store unit creation. Supervisa el número total de unidades del almacén de versiones creadas para almacenar versiones de filas desde que se inició la instancia.Monitors the total number of version store units created to store row versions since the instance was started.

Truncamiento de unidad de almacén de versiones.Version Store unit truncation. Supervisa el número total de unidades del almacén de versiones truncadas desde que se inició la instancia.Monitors the total number of version store units truncated since the instance was started. Una unidad del almacén de versiones se trunca cuando SQL ServerSQL Server determina que no se necesita ninguna de las filas de versiones almacenadas en la unidad del almacén de versiones para ejecutar transacciones activas.A version store unit is truncated when SQL ServerSQL Server determines that none of the version rows stored in the version store unit are needed to run active transactions.

Frecuencia de conflictos de actualización.Update conflict ratio. Supervisa la frecuencia de las transacciones de instantánea que tienen conflictos de actualización con respecto al número total de transacciones de instantánea de actualización.Monitors the ratio of update snapshot transaction that have update conflicts to the total number of update snapshot transactions.

Tiempo mayor de ejecución de transacción.Longest Transaction Running Time. Supervisa el tiempo mayor de ejecución en segundos de cualquier transacción que utilice versiones de fila.Monitors the longest running time in seconds of any transaction using row versioning. Esto permite determinar si una transacción se ejecuta durante una cantidad de tiempo desproporcionada.This can be used to determine if any transaction is running for an unreasonable amount of time.

Transacciones.Transactions. Supervisa el número total de transacciones activas.Monitors the total number of active transactions. No incluye las transacciones del sistema.This does not include system transactions.

Snapshot Transactions.Snapshot Transactions. Supervisa el número total de transacciones de instantáneas activas.Monitors the total number of active snapshot transactions.

Update Snapshot Transactions.Update Snapshot Transactions. Supervisa el número total de transacciones de instantáneas activas que realizan operaciones de actualización.Monitors the total number of active snapshot transactions that perform update operations.

NonSnapshot Version Transactions.NonSnapshot Version Transactions. Supervisa el número total de transacciones que no son instantáneas activas que generan registros de versión.Monitors the total number of active non-snapshot transactions that generate version records.

Nota

La suma de Transacciones de instantáneas de actualización y Transacciones de versión que no son instantáneas representa el número total de transacciones que participan en la generación de versiones.The sum of Update Snapshot Transactions and NonSnapshot Version Transactions represents the total number of transactions that participate in version generation. La diferencia entre Transacciones de instantáneas y Transacciones de instantáneas de actualización notifica el número de transacciones de instantáneas de solo lectura.The difference of Snapshot Transactions and Update Snapshot Transactions reports the number of read-only snapshot transactions.

Ejemplo de nivel de aislamiento basado en versiones de filaRow Versioning-based Isolation Level Example

En los siguientes ejemplos se muestran las diferencias de comportamiento entre transacciones de aislamiento de instantánea y transacciones de lectura confirmada que usan las versiones de fila.The following examples show the differences in behavior between snapshot isolation transactions and read-committed transactions that use row versioning.

A.A. Trabajar con aislamiento de instantáneaWorking with snapshot isolation

En este ejemplo, una transacción que se ejecuta con aislamiento de instantánea lee los datos que a continuación modifica otra transacción.In this example, a transaction running under snapshot isolation reads data that is then modified by another transaction. La transacción de instantáneas no bloquea la operación de actualización ejecutada por la otra transacción y sigue leyendo datos de la fila con versiones, omitiendo la modificación de datos.The snapshot transaction does not block the update operation executed by the other transaction, and it continues to read data from the versioned row, ignoring the data modification. No obstante, cuando la transacción de instantáneas intenta modificar los datos que ya han sido modificados por la otra transacción, genera un error y finaliza.However, when the snapshot transaction attempts to modify the data that has already been modified by the other transaction, the snapshot transaction generates an error and is terminated.

En la sesión 1:On session 1:

USE AdventureWorks2016;  
GO  
  
-- Enable snapshot isolation on the database.  
ALTER DATABASE AdventureWorks2016  
    SET ALLOW_SNAPSHOT_ISOLATION ON;  
GO  
  
-- Start a snapshot transaction  
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
GO  
  
BEGIN TRANSACTION;  
    -- This SELECT statement will return  
    -- 48 vacation hours for the employee.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

En la sesión 2:On session 2:

USE AdventureWorks2016;  
GO  
  
-- Start a transaction.  
BEGIN TRANSACTION;  
    -- Subtract a vacation day from employee 4.  
    -- Update is not blocked by session 1 since  
    -- under snapshot isolation shared locks are  
    -- not requested.  
    UPDATE HumanResources.Employee  
        SET VacationHours = VacationHours - 8  
        WHERE BusinessEntityID = 4;  
  
    -- Verify that the employee now has 40 vacation hours.  
    SELECT VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

En la sesión 1:On session 1:

    -- Reissue the SELECT statement - this shows  
    -- the employee having 48 vacation hours.  The  
    -- snapshot transaction is still reading data from  
    -- the versioned row.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

En la sesión 2:On session 2:

-- Commit the transaction; this commits the data  
-- modification.  
COMMIT TRANSACTION;  
GO  

En la sesión 1:On session 1:

    -- Reissue the SELECT statement - this still   
    -- shows the employee having 48 vacation hours  
    -- even after the other transaction has committed  
    -- the data modification.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  
  
    -- Because the data has been modified outside of the  
    -- snapshot transaction, any further data changes to   
    -- that data by the snapshot transaction will cause   
    -- the snapshot transaction to fail. This statement   
    -- will generate a 3960 error and the transaction will   
    -- terminate.  
    UPDATE HumanResources.Employee  
        SET SickLeaveHours = SickLeaveHours - 8  
        WHERE BusinessEntityID = 4;  
  
-- Undo the changes to the database from session 1.   
-- This will not undo the change from session 2.  
ROLLBACK TRANSACTION  
GO  

B.B. Trabajar con transacciones de lectura confirmada utilizando las versiones de filaWorking with read-committed using row versioning

En este ejemplo, una transacción de lectura confirmada que utiliza las versiones de fila se ejecuta simultáneamente con otra transacción.In this example, a read-committed transaction using row versioning runs concurrently with another transaction. La transacción de lectura confirmada se comporta de diferente manera que una transacción de instantáneas.The read-committed transaction behaves differently than a snapshot transaction. Al igual que una transacción de instantáneas, la transacción de lectura confirmada lee filas con versiones incluso después de que la otra transacción haya modificado los datos.Like a snapshot transaction, the read-committed transaction will read versioned rows even after the other transaction has modified data. Sin embargo, a diferencia de una transacción de instantáneas, la transacción de lectura confirmada:However, unlike a snapshot transaction, the read-committed transaction will:

  • Leerá los datos modificados después de que la otra transacción confirme los cambios en los datos.Read the modified data after the other transaction commits the data changes.
  • Podrá actualizar los datos modificados por la otra transacción cuando la transacción de instantáneas no pueda.Be able to update the data modified by the other transaction where the snapshot transaction could not.

En la sesión 1:On session 1:

USE AdventureWorks2016;  -- Or any earlier version of the AdventureWorks database.  
GO  
  
-- Enable READ_COMMITTED_SNAPSHOT on the database.  
-- For this statement to succeed, this session  
-- must be the only connection to the AdventureWorks2016  
-- database.  
ALTER DATABASE AdventureWorks2016  
    SET READ_COMMITTED_SNAPSHOT ON;  
GO  
  
-- Start a read-committed transaction  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  
  
BEGIN TRANSACTION;  
    -- This SELECT statement will return  
    -- 48 vacation hours for the employee.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

En la sesión 2:On session 2:

USE AdventureWorks2016;  
GO  
  
-- Start a transaction.  
BEGIN TRANSACTION;  
    -- Subtract a vacation day from employee 4.  
    -- Update is not blocked by session 1 since  
    -- under read-committed using row versioning shared locks are  
    -- not requested.  
    UPDATE HumanResources.Employee  
        SET VacationHours = VacationHours - 8  
        WHERE BusinessEntityID = 4;  
  
    -- Verify that the employee now has 40 vacation hours.  
    SELECT VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

En la sesión 1:On session 1:

    -- Reissue the SELECT statement - this still shows  
    -- the employee having 48 vacation hours.  The  
    -- read-committed transaction is still reading data   
    -- from the versioned row and the other transaction   
    -- has not committed the data changes yet.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

En la sesión 2:On session 2:

-- Commit the transaction.  
COMMIT TRANSACTION;  
GO  

En la sesión 1:On session 1:

    -- Reissue the SELECT statement which now shows the   
    -- employee having 40 vacation hours.  Being   
    -- read-committed, this transaction is reading the   
    -- committed data. This is different from snapshot  
    -- isolation which reads from the versioned row.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  
  
    -- This statement, which caused the snapshot transaction   
    -- to fail, will succeed with read-committed using row versioning.  
    UPDATE HumanResources.Employee  
        SET SickLeaveHours = SickLeaveHours - 8  
        WHERE BusinessEntityID = 4;  
  
-- Undo the changes to the database from session 1.   
-- This will not undo the change from session 2.  
ROLLBACK TRANSACTION;  
GO  

Habilitar niveles de aislamiento basado en versiones de filaEnabling Row Versioning-Based Isolation Levels

Los administradores de bases de datos determinan la configuración de la base de datos para versiones de fila mediante las opciones de base de datos READ_COMMITTED_SNAPSHOT y ALLOW_SNAPSHOT_ISOLATION de la instrucción ALTER DATABASE.Database administrators control the database-level settings for row versioning by using the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options in the ALTER DATABASE statement.

Cuando se establece la opción de base de datos READ_COMMITTED_SNAPSHOT en ON, se activan inmediatamente los mecanismos utilizados para admitir la opción.When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. Al establecer la opción READ_COMMITTED_SNAPSHOT, solo se permite en la base de datos la conexión que ejecuta el comando ALTER DATABASE.When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. No debe haber ninguna otra conexión de base de datos abierta hasta que ALTER DATABASE haya finalizado.There must be no other open connection in the database until ALTER DATABASE is complete. La base de datos no tiene que estar en modo de usuario único.The database does not have to be in single-user mode.

La instrucción Transact-SQLTransact-SQL siguiente permite READ_COMMITTED_SNAPSHOT:The following Transact-SQLTransact-SQL statement enables READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2016  
    SET READ_COMMITTED_SNAPSHOT ON;  

Si la opción de base de datos ALLOW_SNAPSHOT_ISOLATION se establece en ON, la instancia del Motor de base de datos de SQL ServerSQL Server Database Engine no genera versiones de filas para datos modificados hasta que finalicen todas las transacciones activas que han modificado los datos en la base de datos.When the ALLOW_SNAPSHOT_ISOLATION database option is set ON, the instance of the Motor de base de datos de SQL ServerSQL Server Database Engine does not generate row versions for modified data until all active transactions that have modified data in the database complete. Si hay transacciones de modificación activas, SQL ServerSQL Server establece el estado de la opción en PENDING_ON.If there are active modification transactions, SQL ServerSQL Server sets the state of the option to PENDING_ON. Una vez finalizadas todas las transacciones de modificación, el estado de la opción cambia a ON.After all of the modification transactions complete, the state of the option is changed to ON. Los usuarios no pueden iniciar una transacción de instantáneas en la base de datos hasta que la opción esté completamente en ON.Users cannot start a snapshot transaction in that database until the option is fully ON. La base de datos pasa a un estado PENDING_OFF cuando el administrador de la base de datos establece la opción ALLOW_SNAPSHOT_ISOLATION en OFF.The database passes through a PENDING_OFF state when the database administrator sets the ALLOW_SNAPSHOT_ISOLATION option to OFF.

La siguiente instrucción Transact-SQLTransact-SQL habilita ALLOW_SNAPSHOT_ISOLATION:The following Transact-SQLTransact-SQL statement will enable ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2016  
    SET ALLOW_SNAPSHOT_ISOLATION ON;  

En la tabla siguiente se enumeran y describen los estados de la opción ALLOW_SNAPSHOT_ISOLATION.The following table lists and describes the states of the ALLOW_SNAPSHOT_ISOLATION option. El uso de ALTER DATABASE con la opción ALLOW_SNAPSHOT_ISOLATION no bloquea a los usuarios que actualmente tienen acceso a la base de datos.Using ALTER DATABASE with the ALLOW_SNAPSHOT_ISOLATION option does not block users who are currently accessing the database data.

Estado del marco de aislamiento de instantánea para la base de datos actualState of snapshot isolation framework for current database DescripciónDescription
ApagadoOFF La compatibilidad de transacciones de aislamiento de instantánea no está activada.The support for snapshot isolation transactions is not activated. No se permiten transacciones de aislamiento de instantánea.No snapshot isolation transactions are allowed.
PENDING_ONPENDING_ON La compatibilidad de transacciones de aislamiento de instantánea se encuentra en estado de transición (de OFF a ON).The support for snapshot isolation transactions is in transition state (from OFF to ON). Las operaciones abiertas deben finalizar.Open transactions must complete.

No se permiten transacciones de aislamiento de instantánea.No snapshot isolation transactions are allowed.
ACTIVARON La compatibilidad de transacciones de aislamiento de instantánea está activada.The support for snapshot isolation transactions is activated.

Se permiten transacciones de instantáneas.Snapshot transactions are allowed.
PENDING_OFFPENDING_OFF La compatibilidad de transacciones de aislamiento de instantánea se encuentra en estado de transición (de ON a OFF).The support for snapshot isolation transactions is in transition state (from ON to OFF).

Las transacciones de instantáneas iniciadas con posterioridad no tienen acceso a esta base de datos.Snapshot transactions started after this time cannot access this database. La actualización de transacciones sigue pagando el costo de crear versiones en esta base de datos.Update transactions still pay the cost of versioning in this database. Las transacciones de instantáneas existentes siguen teniendo acceso a la base de datos sin problema.Existing snapshot transactions can still access this database without a problem. El estado PENDING_OFF no pasa a OFF hasta que finalicen las transacciones de instantáneas que estaban activas cuando el estado de aislamiento de instantánea de base de datos estaba en ON.The state PENDING_OFF does not become OFF until all snapshot transactions that were active when the database snapshot isolation state was ON finish.

Use la vista de catálogo sys.databases para determinar el estado de ambas opciones de base de datos para las versiones de fila.Use the sys.databases catalog view to determine the state of both row versioning database options.

Todas las actualizaciones de tablas de usuario y algunas tablas de sistema almacenadas en la base de datos maestra y msdb generan versiones de fila.All updates to user tables and some system tables stored in master and msdb generate row versions.

La opción ALLOW_SNAPSHOT_ISOLATION se establece automáticamente en ON en las bases de datos maestra y msdb, y no puede deshabilitarse.The ALLOW_SNAPSHOT_ISOLATION option is automatically set ON in the master and msdb databases, and cannot be disabled.

Los usuarios no pueden establecer en ON la opción READ_COMMITTED_SNAPSHOT en la bases de datos maestra, tempdb ni msdb.Users cannot set the READ_COMMITTED_SNAPSHOT option ON in master, tempdb, or msdb.

Usar niveles de aislamiento basados en versiones de filaUsing Row Versioning-based Isolation Levels

El marco de versiones de fila está siempre habilitado en SQL ServerSQL Server y lo utilizan varias características.The row versioning framework is always enabled in SQL ServerSQL Server, and is used by multiple features. Además de proporcionar niveles de aislamiento basados en versiones de fila, se utiliza para admitir las modificaciones efectuadas en desencadenadores y sesiones de conjuntos de resultados activos múltiples (MARS), así como para admitir lecturas de datos en operaciones de índice ONLINE.Besides providing row versioning-based isolation levels, it is used to support modifications made in triggers and multiple active result sets (MARS) sessions, and to support data reads for ONLINE index operations.

Los niveles de aislamiento basados en versiones de fila se habilitan en la base de datos.Row versioning-based isolation levels are enabled at the database level. Cualquier aplicación que tenga acceso a objetos de bases de datos habilitadas puede ejecutar consultas con los siguientes niveles de aislamiento:Any application accessing objects from enabled databases can run queries using the following isolation levels:

  • Lectura de confirmadas, que utiliza versiones de fila al establecer la opción de base de datos READ_COMMITTED_SNAPSHOT en ON, como se muestra en el siguiente ejemplo de código:Read-committed that uses row versioning by setting the READ_COMMITTED_SNAPSHOT database option to ON as shown in the following code example:

    ALTER DATABASE AdventureWorks2016  
        SET READ_COMMITTED_SNAPSHOT ON;  
    

    Cuando la base de datos se habilita para READ_COMMITTED_SNAPSHOT, todas las consultas que se ejecutan en el nivel de aislamiento de lectura de confirmadas utilizan versiones de fila, lo que significa que las operaciones de lectura no bloquean las operaciones de actualización.When the database is enabled for READ_COMMITTED_SNAPSHOT, all queries running under the read committed isolation level use row versioning, which means that read operations do not block update operations.

  • Puede habilitar el aislamiento de instantáneas configurando la opción de base de datos ALLOW_SNAPSHOT_ISOLATION en ON, como se muestra en el ejemplo de código siguiente:Snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option to ON as shown in the following code example:

    ALTER DATABASE AdventureWorks2016  
        SET ALLOW_SNAPSHOT_ISOLATION ON;  
    

    Una transacción que se ejecute en aislamiento de instantánea puede tener acceso a tablas de la base de datos que se hayan habilitado para instantáneas.A transaction running under snapshot isolation can access tables in the database that have been enabled for snapshot. Para obtener acceso a tablas que no se han habilitado para instantáneas, debe cambiarse el nivel de aislamiento.To access tables that have not been enabled for snapshot, the isolation level must be changed. El siguiente ejemplo de código muestra una instrucción SELECT que une dos tablas mientras se ejecuta en una transacción de instantáneas.For example, the following code example shows a SELECT statement that joins two tables while running under a snapshot transaction. Una tabla pertenece a una base de datos en la que no se habilitó el aislamiento de instantánea.One table belongs to a database in which snapshot isolation is not enabled. Cuando la instrucción SELECT se ejecuta en aislamiento de instantánea, no lo hace correctamente.When the SELECT statement runs under snapshot isolation, it fails to execute successfully.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
    BEGIN TRAN  
        SELECT t1.col5, t2.col5  
            FROM Table1 as t1  
            INNER JOIN SecondDB.dbo.Table2 as t2  
                ON t1.col1 = t2.col2;  
    

    El siguiente ejemplo de código muestra la misma instrucción SELECT modificada para cambiar el nivel de aislamiento de transacción a lectura de confirmadas.The following code example shows the same SELECT statement that has been modified to change the transaction isolation level to read-committed. Gracias a este cambio, la instrucción SELECT se ejecuta correctamente.Because of this change, the SELECT statement executes successfully.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
    BEGIN TRAN  
        SELECT t1.col5, t2.col5  
            FROM Table1 as t1  
            WITH (READCOMMITTED)  
            INNER JOIN SecondDB.dbo.Table2 as t2  
                ON t1.col1 = t2.col2;  
    

Limitaciones de transacciones con niveles de aislamiento basados en versiones de filaLimitations of Transactions Using Row Versioning-based Isolation Levels

Tenga en cuenta las siguientes limitaciones cuando trabaje con niveles de aislamiento basados en versiones de fila:Consider the following limitations when working with row versioning-based isolation levels:

  • No se puede habilitar READ_COMMITTED_SNAPSHOT en tempdb, msdb ni master.READ_COMMITTED_SNAPSHOT cannot be enabled in tempdb, msdb, or master.

  • Las tablas temporales globales se almacenan en tempdb.Global temp tables are stored in tempdb. Cuando se obtiene acceso a tablas temporales globales en una transacción de instantáneas, debe realizarse alguna de las siguientes acciones:When accessing global temp tables inside a snapshot transaction, one of the following must happen:

    • Establezca la opción de base de datos ALLOW_SNAPSHOT_ISOLATION en ON en tempdb.Set the ALLOW_SNAPSHOT_ISOLATION database option ON in tempdb.
    • Usar una sugerencia de aislamiento para cambiar el nivel de aislamiento de la instrucción.Use an isolation hint to change the isolation level for the statement.
  • Las transacciones de instantáneas provocan errores cuando:Snapshot transactions fail when:

    • Una base de datos pasa a ser de solo lectura una vez iniciada la transacción de instantáneas, pero antes de que ésta obtenga acceso a la base de datos.A database is made read-only after the snapshot transaction starts, but before the snapshot transaction accesses the database.
    • Si al tener acceso a objetos de varias bases de datos, el estado de una base de datos se modificó de forma que la recuperación de la base de datos se produjo después del inicio de la transacción, pero antes del acceso de la transacción de instantáneas a la base de datos.If accessing objects from multiple databases, a database state was changed in such a way that database recovery occurred after a snapshot transaction starts, but before the snapshot transaction accesses the database. Por ejemplo, la base de datos se estableció en OFFLINE y luego en ONLINE, cierre automático y apertura de base de datos, o adjuntar y separar una base de datos.For example: the database was set to OFFLINE and then to ONLINE, database autoclose and open, or database detach and attach.
  • Las transacciones distribuidas, incluidas las consultas de bases de datos con particiones distribuidas, no se admiten en aislamiento de instantánea.Distributed transactions, including queries in distributed partitioned databases, are not supported under snapshot isolation.

  • SQL ServerSQL Server no mantiene varias versiones de los metadatos del sistema.does not keep multiple versions of system metadata. Las instrucciones del lenguaje de definición de datos (DDL) de tablas y otros objetos de base de datos (índices, vistas, tipos de datos, procedimientos almacenados y funciones de CLR (Common Language Runtime)) cambian los metadatos.Data definition language (DDL) statements on tables and other database objects (indexes, views, data types, stored procedures, and common language runtime functions) change metadata. Si una instrucción DDL modifica un objeto, cualquier referencia simultánea al objeto en aislamiento de instantánea provoca errores en la transacción de instantáneas.If a DDL statement modifies an object, any concurrent reference to the object under snapshot isolation causes the snapshot transaction to fail. Las transacciones de lectura de confirmadas no tienen esta limitación cuando la opción de base de datos READ_COMMITTED_SNAPSHOT es ON.Read-committed transactions do not have this limitation when the READ_COMMITTED_SNAPSHOT database option is ON.

    Por ejemplo, el administrador de la base de datos ejecuta la siguiente instrucción ALTER INDEX.For example, a database administrator executes the following ALTER INDEX statement.

    USE AdventureWorks2016;  
    GO  
    ALTER INDEX AK_Employee_LoginID  
        ON HumanResources.Employee REBUILD;  
    GO  
    

    Cualquier transacción de instantáneas que esté activa cuando se ejecuta la instrucción ALTER INDEX recibirá un error si intenta hacer referencia a la tabla HumanResources.Employee una vez ejecutada la instrucción ALTER INDEX.Any snapshot transaction that is active when the ALTER INDEX statement is executed receives an error if it attempts to reference the HumanResources.Employee table after the ALTER INDEX statement is executed. Las transacciones de lectura de confirmadas que utilicen versiones de fila no se verán afectadas.Read-committed transactions using row versioning are not affected.

    Nota

    Las operaciones BULK INSERT pueden provocar cambios en los metadatos de la tabla de destino (por ejemplo, al deshabilitar las comprobaciones de restricciones).BULK INSERT operations may cause changes to target table metadata (for example, when disabling constraint checks). Cuando esto sucede, las transacciones simultáneas de aislamiento de instantánea que obtengan acceso a tablas de inserciones masivas generarán un error.When this happens, concurrent snapshot isolation transactions accessing bulk inserted tables fail.

Personalizar las versiones de fila y bloqueoCustomizing Locking and Row Versioning

Personalizar el tiempo de espera de bloqueoCustomizing the Lock Time-Out

Cuando una instancia del MicrosoftMicrosoft de Motor de base de datos de SQL ServerSQL Server Database Engine no puede conceder un bloqueo a una transacción porque otra transacción ya posee un bloqueo en conflicto para el recurso, la primera transacción queda bloqueada a la espera de que se libere el bloqueo existente.When an instance of the MicrosoftMicrosoft Motor de base de datos de SQL ServerSQL Server Database Engine cannot grant a lock to a transaction because another transaction already owns a conflicting lock on the resource, the first transaction becomes blocked waiting for the existing lock to be released. De forma predeterminada no hay un tiempo de espera obligatorio, ni tampoco existe ningún modo de comprobar si un recurso está bloqueado antes de intentar bloquearlo, excepto intentar tener acceso a los datos (con el riesgo de quedar bloqueado indefinidamente).By default, there is no mandatory time-out period and no way to test whether a resource is locked before locking it, except to attempt to access the data (and potentially get blocked indefinitely).

Nota

En SQL ServerSQL Server, use la vista de administración dinámica sys.dm_os_waiting_tasks para determinar si un proceso está bloqueado y quién lo bloquea.In SQL ServerSQL Server, use the sys.dm_os_waiting_tasks dynamic management view to determine whether a process is being blocked and who is blocking it. En versiones anteriores de SQL ServerSQL Server, use el procedimiento almacenado del sistema sp_who.In earlier versions of SQL ServerSQL Server, use the sp_who system stored procedure.

El parámetro LOCK_TIMEOUT permite a una aplicación establecer el tiempo máximo que una instrucción esperará en un recurso bloqueado.The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource. Cuando una instrucción ha esperado más tiempo del indicado en LOCK_TIMEOUT, la instrucción bloqueada se cancela automáticamente y se devuelve el mensaje de error 1222 (Lock request time-out period exceeded) a la aplicación.When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and error message 1222 (Lock request time-out period exceeded) is returned to the application. Sin embargo, SQL ServerSQL Server no cancela ni revierte ninguna transacción que contenga la instrucción.Any transaction containing the statement, however, is not rolled back or canceled by SQL ServerSQL Server. Por consiguiente, la aplicación debe tener un controlador de errores que pueda interceptar el mensaje de error 1222.Therefore, the application must have an error handler that can trap error message 1222. Si una aplicación no intercepta el error, puede continuar sin ser consciente de que se ha cancelado una instrucción individual de la transacción y de que esto puede producir errores, ya que las instrucciones posteriores de la transacción podrían depender de la instrucción que nunca se ejecutó.If an application does not trap the error, the application can proceed unaware that an individual statement within a transaction has been canceled, and errors can occur because statements later in the transaction might depend on the statement that was never executed.

Al implementar un controlador de errores que intercepte el mensaje de error 1222 se permite a una aplicación controlar la situación de tiempo de espera y realizar una acción apropiada para solucionarla, como volver a enviar automáticamente la instrucción bloqueada o revertir la transacción completa.Implementing an error handler that traps error message 1222 allows an application to handle the time-out situation and take remedial action, such as: automatically resubmitting the statement that was blocked or rolling back the entire transaction.

Para determinar el valor LOCK_TIMEOUT actual, ejecute la función @@LOCK_TIMEOUT:To determine the current LOCK_TIMEOUT setting, execute the @@LOCK_TIMEOUT function:

SELECT @@lock_timeout;  
GO  

Personalizar el nivel de aislamiento de transacciónCustomizing Transaction Isolation Level

READ COMMITTED es el nivel de aislamiento predeterminado del Motor de base de datos de SQL ServerSQL Server Database Engine de MicrosoftMicrosoft.READ COMMITTED is the default isolation level for the MicrosoftMicrosoft Motor de base de datos de SQL ServerSQL Server Database Engine. Cuando es necesario utilizar una aplicación en un nivel de aislamiento distinto, se pueden utilizar los métodos que se indican a continuación para configurar el nivel de aislamiento:If an application must operate at a different isolation level, it can use the following methods to set the isolation level:

  • Ejecute la instrucción SET TRANSACTION ISOLATION LEVEL.Run the SET TRANSACTION ISOLATION LEVEL statement.
  • En las aplicaciones ADO.NET que utilizan el espacio de nombres administrado System.Data.SqlClient, especifique una opción IsolationLevel mediante el método SqlConnection.BeginTransaction.ADO.NET applications that use the System.Data.SqlClient managed namespace can specify an IsolationLevel option by using the SqlConnection.BeginTransaction method.
  • Las aplicaciones que usan ADO pueden establecer la propiedad Autocommit Isolation Levels.Applications that use ADO can set the Autocommit Isolation Levels property.
  • Al iniciar una transacción, las aplicaciones que utilicen OLE DB pueden llamar a ITransactionLocal::StartTransaction con la propiedad isoLevel establecida en el nivel de aislamiento de transacción deseado.When starting a transaction, applications using OLE DB can call ITransactionLocal::StartTransaction with isoLevel set to the desired transaction isolation level. Si se especifica el nivel de aislamiento en el modo de confirmación automática, para las aplicaciones que utilicen OLE DB se puede establecer la propiedad DBPROPSET_SESSION, DBPROP_SESS_AUTOCOMMITISOLEVELS en el nivel de aislamiento de transacción deseado.When specifying the isolation level in autocommit mode, applications that use OLE DB can set the DBPROPSET_SESSION property DBPROP_SESS_AUTOCOMMITISOLEVELS to the desired transaction isolation level.
  • Las aplicaciones que utilizan ODBC pueden establecer el atributo SQL_COPT_SS_TXN_ISOLATION mediante SQLSetConnectAttr.Applications that use ODBC can set the SQL_COPT_SS_TXN_ISOLATION attribute by using SQLSetConnectAttr.

Si se especifica el nivel de aislamiento, el bloqueo de todas las consultas e instrucciones del lenguaje de manipulación de datos (DML) en la sesión de SQL ServerSQL Server se aplica en ese nivel de aislamiento.When the isolation level is specified, the locking behavior for all queries and data manipulation language (DML) statements in the SQL ServerSQL Server session operates at that isolation level. El nivel de aislamiento permanece vigente hasta que finaliza la sesión o hasta que se cambia la configuración del nivel de aislamiento.The isolation level remains in effect until the session terminates or until the isolation level is set to another level.

En el siguiente ejemplo, se configura el nivel de aislamiento SERIALIZABLE:The following example sets the SERIALIZABLE isolation level:

USE AdventureWorks2016;  
GO  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
GO  
BEGIN TRANSACTION;  
SELECT BusinessEntityID  
    FROM HumanResources.Employee;  
GO  

El nivel de aislamiento se puede pasar por alto, si es necesario, para consultas o instrucciones DML individuales. Para ello debe especificarse una sugerencia de tabla.The isolation level can be overridden for individual query or DML statements, if necessary, by specifying a table-level hint. El hecho de especificar una sugerencia de tabla no afecta a las demás instrucciones de la sesión.Specifying a table-level hint does not affect other statements in the session. Se recomienda que las sugerencias de tabla solo se utilicen para modificar el comportamiento predeterminado en los casos estrictamente necesarios.We recommend that table-level hints be used to change the default behavior only when absolutely necessary.

Es posible que Motor de base de datos de SQL ServerSQL Server Database Engine tenga que adquirir bloqueos al leer los metadatos incluso si el nivel de aislamiento se ha establecido en un nivel en el que no se soliciten bloqueos compartidos al leer los datos.The Motor de base de datos de SQL ServerSQL Server Database Engine might have to acquire locks when reading metadata even when the isolation level is set to a level where share locks are not requested when reading data. Por ejemplo, una transacción que se ejecute en el nivel de aislamiento de lectura sin confirmar no adquiere bloqueos compartidos al leer datos, pero es probable que tenga que solicitar bloqueos en alguna ocasión al leer una vista de catálogo del sistema.For example, a transaction running at the read-uncommitted isolation level does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. Esto significa que es posible que una transacción con lectura sin confirmar provoque un bloqueo cuando ejecute consultas en una tabla mientras otra transacción simultánea modifica los metadatos de la tabla.This means it is possible for a read uncommitted transaction to cause blocking when querying a table when a concurrent transaction is modifying the metadata of that table.

Para determinar el nivel de aislamiento de transacción que está establecido actualmente, utilice la instrucción DBCC USEROPTIONS como se indica en el siguiente ejemplo.To determine the transaction isolation level currently set, use the DBCC USEROPTIONS statement as shown in the following example. El conjunto de resultados puede variar según el conjunto de resultados del sistema.The result set may vary from the result set on your system.

USE AdventureWorks2016;  
GO  
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
GO  
DBCC USEROPTIONS;  
GO  

El conjunto de resultados es el siguiente:Here is the result set.

Set Option                   Value  
---------------------------- -------------------------------------------  
textsize                     2147483647  
language                     us_english  
dateformat                   mdy  
datefirst                    7  
...                          ...  
Isolation level              repeatable read  
 
(14 row(s) affected)   
 
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Sugerencias de bloqueoLocking Hints

Se pueden especificar sugerencias de bloqueo para referencias de tablas individuales en las instrucciones SELECT, INSERT, UPDATE y DELETE.Locking hints can be specified for individual table references in the SELECT, INSERT, UPDATE, and DELETE statements. Las sugerencias especifican el tipo de bloqueo o las versiones de fila que utiliza la instancia de Motor de base de datos de SQL ServerSQL Server Database Engine para los datos de la tabla.The hints specify the type of locking or row versioning the instance of the Motor de base de datos de SQL ServerSQL Server Database Engine uses for the table data. Se pueden utilizar las sugerencias de bloqueo de tabla cuando se requiere un control más ajustado de los tipos de bloqueo adquiridos en un objeto.Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. Estas sugerencias de bloqueo suplantan el nivel de aislamiento de la transacción actual durante la sesión.These locking hints override the current transaction isolation level for the session.

Para obtener más información sobre las sugerencias de bloqueo específicas y sus comportamientos, vea Sugerencias (Transact-SQL) - tabla.For more information about the specific locking hints and their behaviors, see Table Hints (Transact-SQL).

Nota

Motor de base de datos de SQL ServerSQL Server Database Engine suele elegir el nivel de bloqueo correcto casi siempre.The Motor de base de datos de SQL ServerSQL Server Database Engine almost always chooses the correct locking level. Se recomienda utilizar las sugerencias de bloqueo de tabla para modificar el comportamiento de bloqueo predeterminado en los casos estrictamente necesarios.We recommend that table-level locking hints be used to change the default locking behavior only when necessary. Impedir un nivel de bloqueo puede ir en detrimento de la simultaneidad.Disallowing a locking level can adversely affect concurrency.

El Motor de base de datos de SQL ServerSQL Server Database Engine podría verse forzado a adquirir bloqueos al leer los metadatos, incluso cuando procese una instrucción SELECT con una sugerencia de bloqueo que impida a las solicitudes compartir bloqueos al leer los datos.The Motor de base de datos de SQL ServerSQL Server Database Engine might have to acquire locks when reading metadata, even when processing a select with a locking hint that prevents requests for share locks when reading data. Por ejemplo, una instrucción SELECT que utiliza la sugerencia NOLOCK no adquiere bloqueos compartidos al leer los datos, pero podría solicitar bloqueos en alguna oportunidad al leer la vista de catálogo del sistema.For example, a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. Esto significa que es posible que una instrucción SELECT que utilice NOLOCK esté bloqueada.This means it is possible for a SELECT statement using NOLOCK to be blocked.

Según se muestra en el siguiente ejemplo, si se establece el nivel de aislamiento de la transacción en SERIALIZABLE y se utiliza la sugerencia de bloqueo de nivel de tabla NOLOCK con la instrucción SELECT, no se aplican los bloqueos de intervalos de claves usados normalmente para mantener las transacciones serializables.As shown in the following example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.

USE AdventureWorks2016;  
GO  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
GO  
BEGIN TRANSACTION;  
GO  
SELECT JobTitle  
    FROM HumanResources.Employee WITH (NOLOCK);  
GO  
  
-- Get information about the locks held by   
-- the transaction.  
SELECT    
        resource_type,   
        resource_subtype,   
        request_mode  
    FROM sys.dm_tran_locks  
    WHERE request_session_id = @@spid;  
  
-- End the transaction.  
ROLLBACK;  
GO  

El único bloqueo utilizado que hace referencia a HumanResources.Employee es un bloqueo de estabilidad de esquema (Sch-S).The only lock taken that references HumanResources.Employee is a schema stability (Sch-S) lock. En este caso, ya no se garantiza la serialidad.In this case, serializability is no longer guaranteed.

En SQL Server 2019 (15.x)SQL Server 2019 (15.x), la opción LOCK_ESCALATION de ALTER TABLE puede penalizar los bloqueos de tabla y habilitar los bloqueos HoBT en tablas con particiones.In SQL Server 2019 (15.x)SQL Server 2019 (15.x), the LOCK_ESCALATION option of ALTER TABLE can disfavor table locks, and enable HoBT locks on partitioned tables. Esta opción no es una sugerencia de bloqueo, pero puede utilizarse para reducir la extensión de los bloqueos.This option is not a locking hint, but can be used to reduce lock escalation. Para obtener más información, vea ALTER TABLE (Transact-SQL).For more information, see ALTER TABLE (Transact-SQL).

Personalizar el bloqueo de un índiceCustomizing Locking for an Index

Motor de base de datos de SQL ServerSQL Server Database Engine utiliza una estrategia de bloqueo dinámico que, en la mayoría de casos, elige automáticamente la mejor granularidad de bloqueo para las consultas.The Motor de base de datos de SQL ServerSQL Server Database Engine uses a dynamic locking strategy that automatically chooses the best locking granularity for queries in most cases. Se recomienda no invalidar los niveles de bloqueo predeterminados, que tienen el bloqueo de página y fila habilitados, a menos que se tenga un conocimiento claro de los patrones de acceso a tablas o índices y de que estos sean coherentes, y de que se surja un problema de contención de recursos que haya que resolver.We recommend that you do not override the default locking levels, which have page and row locking on, unless table or index access patterns are well understood and consistent, and there is a resource contention problem to solve. Si se invalida un nivel de bloqueo, se puede obstaculizar considerablemente el acceso simultáneo a una tabla o índice.Overriding a locking level can significantly impede concurrent access to a table or index. Por ejemplo, si se especifican bloqueos solamente para el nivel de tabla en una tabla de gran tamaño a la que los usuarios obtienen acceso frecuentemente, se pueden producir cuellos de botella, ya que los usuarios deben esperar a que se libere el bloqueo de la tabla para tener acceso a ella.For example, specifying only table-level locks on a large table that users access heavily can cause bottlenecks because users must wait for the table-level lock to be released before accessing the table.

Hay algunos casos en lo que puede ser conveniente impedir los bloqueos de página o fila, siempre y cuando se comprendan perfectamente los patrones y estos sean coherentes.There are a few cases where disallowing page or row locking can be beneficial, if the access patterns are well understood and consistent. Por ejemplo, una aplicación de bases de datos utiliza una tabla de búsqueda que se actualiza semanalmente en un proceso por lotes.For example, a database application uses a lookup table that is updated weekly in a batch process. Los lectores simultáneos tienen acceso a la tabla con un bloqueo compartido (S) y las actualizaciones por lotes semanales obtienen acceso a la tabla con un bloqueo exclusivo (X).Concurrent readers access the table with a shared (S) lock and the weekly batch update accesses the table with an exclusive (X) lock. Al desactivar el bloqueo de página y fila en la tabla se reduce la sobrecarga de bloqueo durante la semana, ya que los lectores pueden tener acceso simultáneo a la tabla a través de bloqueos de tabla compartidos.Turning off page and row locking on the table reduces the locking overhead throughout the week by allowing readers to concurrently access the table through shared table locks. Cuando se ejecuta el trabajo por lotes, este puede completar la actualización de forma eficaz porque obtiene un bloqueo de tabla exclusivo.When the batch job runs, it can complete the update efficiently because it obtains an exclusive table lock.

La desactivación del bloqueo de página y fila podría o no ser aceptable, ya que la actualización por lotes semanal impedirá que los lectores simultáneos tengan acceso a la tabla mientras se ejecuta la actualización.Turning off page and row locking might or might not be acceptable because the weekly batch update will block the concurrent readers from accessing the table while the update runs. Si el trabajo por lotes solamente cambia unas cuantas filas o páginas, puede modificar el nivel de bloqueo para permitir el bloqueo de nivel de fila o página y, de esta forma, permitir que otras sesiones lean la tabla sin bloqueos.If the batch job only changes a few rows or pages, you can change the locking level to allow row or page level locking, which will enable other sessions to read from the table without blocking. Si el trabajo por lotes tiene un gran número de actualizaciones, la obtención de un bloqueo exclusivo sobre la tabla puede ser la forma más eficaz de asegurarse de que el trabajo por lotes finaliza de modo eficaz.If the batch job has a large number of updates, obtaining an exclusive lock on the table may be the best way to ensure the batch job finishes efficiently.

En ocasiones se puede producir un interbloqueo si dos operaciones simultáneas adquieren bloqueos de fila en la misma tabla y se bloquean porque necesitan bloquear la página.Occasionally a deadlock occurs when two concurrent operations acquire row locks on the same table and then block because they both need to lock the page. Al impedir los bloqueos de fila se obliga a una de las operaciones a que espere y se evita el interbloqueo.Disallowing row locks forces one of the operations to wait, avoiding the deadlock.

La granularidad del bloqueo utilizado en un índice se puede establecer mediante las instrucciones CREATE INDEX y ALTER INDEX.The granularity of locking used on an index can be set using the CREATE INDEX and ALTER INDEX statements. La configuración del bloqueo se aplica a las páginas de índice y a las páginas de tabla.The lock settings apply to both the index pages and the table pages. Además, las instrucciones CREATE TABLE y ALTER TABLE se pueden usar para establecer la granularidad del bloqueo de en las restricciones PRIMARY KEY y UNIQUE.In addition, the CREATE TABLE and ALTER TABLE statements can be used to set locking granularity on PRIMARY KEY and UNIQUE constraints. Para mantener la compatibilidad con versiones anteriores, el procedimiento almacenado del sistema sp_indexoption también puede establecer la granularidad.For backwards compatibility, the sp_indexoption system stored procedure can also set the granularity. Para mostrar la opción de bloqueo actual de un determinado índice, utilice la función INDEXPROPERTY.To display the current locking option for a given index, use the INDEXPROPERTY function. Se pueden impedir los bloqueos de página, los de fila o una combinación de bloqueos de página y fila para un determinado índice.Page-level locks, row-level locks, or a combination of page-level and row-level locks can be disallowed for a given index.

Bloqueos no permitidosDisallowed locks Tienen acceso al índiceIndex accessed by
De páginaPage level Bloqueos de fila y tablaRow-level and table-level locks
De filaRow level Bloqueos de página y tablaPage-level and table-level locks
De página y filaPage level and row level Bloqueos de tablaTable-level locks

Información avanzada sobre transaccionesAdvanced Transaction Information

Anidar transaccionesNesting Transactions

Las transacciones explícitas se pueden anidar.Explicit transactions can be nested. El objetivo principal de esto es aceptar transacciones en procedimientos almacenados a los que se puede llamar desde un proceso que ya esté en una transacción o desde procesos que no tengan transacciones activas.This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

En el ejemplo siguiente se muestra el uso para el que están diseñadas las transacciones anidadas.The following example shows the intended use of nested transactions. El procedimiento TransProc exige su transacción, sin tener en cuenta el modo de transacción del proceso que lo ejecute.The procedure TransProc enforces its transaction regardless of the transaction mode of any process that executes it. Si se llama a TransProc cuando una transacción está activa, la transacción anidada en TransProc se pasará por alto y se confirmarán o revertirán sus instrucciones INSERT basándose en la acción final adoptada para la transacción externa.If TransProc is called when a transaction is active, the nested transaction in TransProc is largely ignored, and its INSERT statements are committed or rolled back based on the final action taken for the outer transaction. Si un proceso que no tiene ninguna transacción pendiente ejecuta TransProc, la instrucción COMMIT TRANSACTION al final del procedimiento confirmará de manera efectiva las instrucciones INSERT.If TransProc is executed by a process that does not have an outstanding transaction, the COMMIT TRANSACTION at the end of the procedure effectively commits the INSERT statements.

SET QUOTED_IDENTIFIER OFF;  
GO  
SET NOCOUNT OFF;  
GO  
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,  
               Colb CHAR(3) NOT NULL);  
GO  
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS  
BEGIN TRANSACTION InProc  
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)  
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)  
COMMIT TRANSACTION InProc;  
GO  
/* Start a transaction and execute TransProc. */  
BEGIN TRANSACTION OutOfProc;  
GO  
EXEC TransProc 1, 'aaa';  
GO  
/* Roll back the outer transaction, this will  
   roll back TransProc's nested transaction. */  
ROLLBACK TRANSACTION OutOfProc;  
GO  
EXECUTE TransProc 3,'bbb';  
GO  
/* The following SELECT statement shows only rows 3 and 4 are   
   still in the table. This indicates that the commit  
   of the inner transaction from the first EXECUTE statement of  
   TransProc was overridden by the subsequent rollback. */  
SELECT * FROM TestTrans;  
GO  

Motor de base de datos de SQL ServerSQL Server Database Engine omite la confirmación de las transacciones internas.Committing inner transactions is ignored by the Motor de base de datos de SQL ServerSQL Server Database Engine. La transacción se confirma o se revierte basándose en la acción realizada al final de la transacción más externa.The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. Si se confirma la transacción externa, también se confirmarán las transacciones anidadas internas.If the outer transaction is committed, the inner nested transactions are also committed. Si se revierte la transacción externa, también se revertirán todas las transacciones internas, independientemente de si se confirmaron individualmente o no.If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

Cada llamada a COMMIT TRANSACTION o a COMMIT WORK se aplica a la última instrucción BEGIN TRANSACTION ejecutada.Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. Si las instrucciones BEGIN TRANSACTION están anidadas, la instrucción COMMIT solo se aplica a la última transacción anidada, que es la más interna.If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. Aunque una instrucción COMMIT TRANSACTION transaction_name de una transacción anidada haga referencia al nombre de la transacción externa, la confirmación solo se aplicará a la transacción más interna.Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.

No es válido que el parámetro transaction_name de una instrucción ROLLBACK TRANSACTION haga referencia a las transacciones internas de un conjunto de transacciones anidadas con nombre.It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name solo puede hacer referencia al nombre de la transacción más externa.transaction_name can refer only to the transaction name of the outermost transaction. Si se ejecuta una instrucción ROLLBACK TRANSACTION transaction_name con el nombre de la transacción externa en cualquier nivel de un conjunto de transacciones anidadas, se revertirán todas las transacciones anidadas.If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. Si se ejecuta una instrucción ROLLBACK WORK o ROLLBACK TRANSACTION sin el parámetro transaction_name en cualquier nivel de un conjunto de transacciones anidadas, se revertirán todas las transacciones anidadas, incluida la más externa.If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

La función @@TRANCOUNT registra el nivel de anidamiento de la transacción actual.The @@TRANCOUNT function records the current transaction nesting level. Cada instrucción BEGIN TRANSACTION incrementa @@TRANCOUNT en uno.Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Cada instrucción COMMIT TRANSACTION o COMMIT WORK disminuye @@TRANCOUNT en uno.Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one. Una instrucción ROLLBACK WORK o ROLLBACK TRANSACTION que no tiene un nombre de la transacción revierte todas las transacciones anidadas y reduce @@TRANCOUNT a 0.A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. Un ROLLBACK TRANSACTION que usa el nombre de transacción de la transacción más extrema en un conjunto de transacciones anidadas revierte todas las transacciones anidadas y reduce @@TRANCOUNT a 0.A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all of the nested transactions and decrements @@TRANCOUNT to 0. Si no está seguro de si se encuentra en una transacción, use SELECT @@TRANCOUNT para determinar si es 1 o más.When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. Si @@TRANCOUNT es 0, no está en una transacción.If @@TRANCOUNT is 0, you are not in a transaction.

Usar sesiones enlazadasUsing Bound Sessions

Las sesiones enlazadas facilitan la coordinación de las acciones entre varias sesiones iniciadas en un mismo servidor.Bound sessions ease the coordination of actions across multiple sessions on the same server. Permiten que dos o más sesiones compartan la misma transacción y los mismos bloqueos, además de trabajar con los mismos datos sin que surjan conflictos de bloqueo.Bound sessions allow two or more sessions to share the same transaction and locks, and can work on the same data without lock conflicts. Se pueden crear sesiones enlazadas a partir de varias sesiones con la misma aplicación o desde varias aplicaciones con sesiones independientes.Bound sessions can be created from multiple sessions within the same application or from multiple applications with separate sessions.

Para participar en una sesión enlazada, una sesión llama a sp_getbindtoken o srv_getbindtoken (mediante Servicios de datos abiertos) para obtener un token de enlace.To participate in a bound session, a session calls sp_getbindtoken or srv_getbindtoken (through Open Data Services) to get a bind token. Un token de enlace es una cadena de caracteres que identifica de forma única cada transacción enlazada.A bind token is a character string that uniquely identifies each bound transaction. El token de enlace se envía a las otras sesiones que se van a enlazar a la sesión actual.The bind token is then sent to the other sessions to be bound with the current session. Las demás sesiones se enlazan con la transacción llamando a sp_bindsession con el token de enlace recibido de la primera sesión.The other sessions bind to the transaction by calling sp_bindsession, using the bind token received from the first session.

Nota

Las sesiones deben tener una transacción de usuario activa para que sp_getbindtoken o srv_getbindtoken funcionen correctamente.A session must have an active user transaction in order for sp_getbindtoken or srv_getbindtoken to succeed.

Los tokens de enlace deben transmitirse desde el código de la aplicación que establece la primera sesión al código de la aplicación que enlaza posteriormente sus sesiones a la primera sesión.Bind tokens must be transmitted from the application code that makes the first session to the application code that subsequently binds their sessions to the first session. No existe ninguna una instrucción Transact-SQLTransact-SQL o función de API que pueda utilizar una aplicación para obtener el token de enlace de una transacción iniciada por otro proceso.There is no Transact-SQLTransact-SQL statement or API function that an application can use to get the bind token for a transaction started by another process. A continuación se indican algunos métodos que se pueden utilizar para transmitir un token de enlace:Some of the methods that can be used to transmit a bind token include the following:

  • Si se han iniciado todas las sesiones desde el mismo proceso de aplicación, se pueden guardar los tokens de enlace en la memoria global, o bien se pueden pasar como un parámetro a las funciones.If the sessions are all initiated from the same application process, bind tokens can be stored in global memory or passed into functions as a parameter.

  • Si se han establecido las sesiones desde procesos de aplicación independientes, los tokens de enlace se pueden transmitir mediante la comunicación entre procesos (IPC), como una llamada a un procedimiento remoto (RPC) o el intercambio dinámico de datos (DDE).If the sessions are made from separate application processes, bind tokens can be transmitted using interprocess communication (IPC), such as a remote procedure call (RPC) or dynamic data exchange (DDE).

  • Los tokens de enlace se pueden guardar en una tabla de una instancia del Motor de base de datos de SQL ServerSQL Server Database Engine que puedan leer los procesos que deseen enlazar a la primera sesión.Bind tokens can be stored in a table in an instance of the Motor de base de datos de SQL ServerSQL Server Database Engine that can be read by processes wanting to bind to the first session.

Solo puede haber una sesión activa a la vez en un conjunto de sesiones enlazadas.Only one session in a set of bound sessions can be active at any time. Si una sesión ejecuta una instrucción en la instancia o tiene resultados pendientes de la instancia, ninguna otra sesión enlazada podrá tener acceso a la instancia hasta que la sesión actual finalice el procesamiento o cancele la instrucción actual.If one session is executing a statement on the instance or has results pending from the instance, no other session bound to it can access the instance until the current session finishes processing or cancels the current statement. Si la instancia está ocupada procesando una instrucción de otra de las sesiones enlazadas, se producirá un error que indica que el espacio de la transacción está en uso y que la sesión debería volver a intentarlo más tarde.If the instance is busy processing a statement from another of the bound sessions, an error occurs indicating that the transaction space is in use and the session should retry later.

Cuando se enlazan sesiones, cada una de ellas mantiene su nivel de aislamiento.When you bind sessions, each session retains its isolation level setting. Si se utiliza SET TRANSACTION ISOLATION LEVEL para cambiar el valor del nivel de aislamiento de una sesión, no se verán afectados los valores de las sesiones enlazadas a ella.Using SET TRANSACTION ISOLATION LEVEL to change the isolation level setting of one session does not affect the setting of any other session bound to it.

Tipos de sesiones enlazadasTypes of Bound Sessions

Los dos tipos de sesiones enlazadas son local y distribuido.The two types of bound sessions are local and distributed.

  • Sesión enlazada localLocal bound session
    Permite enlazar sesiones para compartir el espacio de transacciones de una sola transacción en una única instancia del Motor de base de datos de SQL ServerSQL Server Database Engine.Allows bound sessions to share the transaction space of a single transaction in a single instance of the Motor de base de datos de SQL ServerSQL Server Database Engine.

  • Sesión enlazada distribuidaDistributed bound session
    Permite que las sesiones enlazadas compartan la misma transacción entre dos o más instancias hasta que toda la transacción se haya confirmado o revertido mediante el Coordinador de transacciones distribuidas de MicrosoftMicrosoft (MS DTC).Allows bound sessions to share the same transaction across two or more instances until the entire transaction is either committed or rolled back by using MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC).

Las sesiones enlazadas distribuidas no se identifican mediante el token de enlace de una cadena de caracteres, sino mediante números de identificación de transacciones distribuidas.Distributed bound sessions are not identified by a character string bind token; they are identified by distributed transaction identification numbers. Si una sesión enlazada está implicada en una transacción local y ejecuta un RPC en un servidor remoto con SET REMOTE_PROC_TRANSACTIONS ON, MS DTC promueve automáticamente el nivel de la transacción enlazada local a transacción enlazada distribuida y se inicia una sesión de MS DTC.If a bound session is involved in a local transaction and executes an RPC on a remote server with SET REMOTE_PROC_TRANSACTIONS ON, the local bound transaction is automatically promoted to a distributed bound transaction by MS DTC and an MS DTC session is started.

Cuándo utilizar sesiones enlazadasWhen to use Bound Sessions

En versiones anteriores de SQL ServerSQL Server, las sesiones enlazadas se utilizaban básicamente para desarrollar procedimientos almacenados extendidos que tenían que ejecutar instrucciones Transact-SQLTransact-SQL en nombre del proceso que los llamaba.In earlier versions of SQL ServerSQL Server, bound sessions were primarily used in developing extended stored procedures that must execute Transact-SQLTransact-SQL statements on behalf of the process that calls them. Pasar el proceso que realiza la llamada en un token de enlace como un parámetro del procedimiento almacenado extendido permite al procedimiento combinar el espacio de transacciones del proceso que realiza la llamada y, por ello, integrar el procedimiento almacenado extendido con el proceso que realiza la llamada.Having the calling process pass in a bind token as one parameter of the extended stored procedure allows the procedure to join the transaction space of the calling process, thereby integrating the extended stored procedure with the calling process.

En el Motor de base de datos de SQL ServerSQL Server Database Engine, los procedimientos almacenados escritos mediante CLR son más seguros, escalables y estables que los procedimientos almacenados extendidos.In the Motor de base de datos de SQL ServerSQL Server Database Engine, stored procedures written using CLR are more secure, scalable, and stable than extended stored procedures. Los procedimientos almacenados CLR utilizan el objeto SqlContext para combinar el contexto de la sesión de llamada en lugar de sp_bindsession.CLR-stored procedures use the SqlContext object to join the context of the calling session, not sp_bindsession.

Se pueden utilizar sesiones enlazadas para desarrollar aplicaciones de tres niveles en las que la lógica comercial se incorpora mediante programas independientes que funcionan en colaboración en una sola transacción comercial.Bound sessions can be used to develop three-tier applications in which business logic is incorporated into separate programs that work cooperatively on a single business transaction. Estos programas deben codificarse de forma que coordinen con precisión su acceso a la base de datos.These programs must be coded to carefully coordinate their access to a database. Dado que las dos sesiones comparten los mismos bloqueos, ambos programas deben evitar intentar modificar los mismos datos a la vez.Because the two sessions share the same locks, the two programs must not try to modify the same data at the same time. En cualquier momento solo puede haber una sesión que realice el trabajo como parte de la transacción. No se permiten ejecuciones en paralelo.At any point in time, only one session can be doing work as part of the transaction; there can be no parallel execution. La transacción solo se puede cambiar entre sesiones y en puntos de rendimiento bien definidos, como el momento en que han finalizado todas las instrucciones DML y se han recuperado todos los resultados.The transaction can only be switched between sessions at well-defined yield points, such as when all DML statements have completed and their results have been retrieved.

Codificar transacciones eficacesCoding efficient transactions

Es importante que las transacciones sean tan cortas como sea posible.It is important to keep transactions as short as possible. Cuando se inicia una transacción, un sistema de administración de bases de datos (DBMS) debe contener muchos recursos hasta el final de la transacción para proteger las propiedades de atomicidad, coherencia, aislamiento y durabilidad (ACID) de la transacción.When a transaction is started, a database management system (DBMS) must hold many resources until the end of the transaction to protect the atomicity, consistency, isolation, and durability (ACID) properties of the transaction. Si se modifican datos, se deben proteger las filas modificadas con bloqueos exclusivos que impidan que otra transacción lea las filas, y se deben mantener bloqueos exclusivos hasta que se confirme o se revierta la transacción.If data is modified, the modified rows must be protected with exclusive locks that prevent any other transaction from reading the rows, and exclusive locks must be held until the transaction is committed or rolled back. Dependiendo de la configuración del nivel de aislamiento de la transacción, las instrucciones SELECT pueden adquirir bloqueos que deben mantenerse hasta que la transacción se confirme o se revierta.Depending on transaction isolation level settings, SELECT statements may acquire locks that must be held until the transaction is committed or rolled back. Especialmente en sistemas con muchos usuarios, las transacciones deben ser tan cortas como sea posible para reducir el conflicto de bloqueos de recursos entre conexiones simultáneas.Especially in systems with many users, transactions must be kept as short as possible to reduce locking contention for resources between concurrent connections. Es posible que las transacciones de larga duración y poco eficaces no constituyan un problema cuando hay un pequeño número de usuarios, pero son intolerables en sistemas con miles de usuarios.Long-running, inefficient transactions may not be a problem with small numbers of users, but they are intolerable in a system with thousands of users. A partir de SQL Server 2014 (12.x)SQL Server 2014 (12.x), SQL ServerSQL Server admite las transacciones durables diferidas.Beginning with SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL ServerSQL Server supports delayed durable transactions. Las transacciones durables diferidas no garantizan la durabilidad.Delayed durable transactions do not guarantee durability. Vea el tema Durabilidad de las transacciones para obtener más información.See the topic Transaction Durability for more information.

Instrucciones de codificaciónCoding Guidelines

A continuación se muestran las instrucciones para codificar transacciones eficaces:These are guidelines for coding efficient transactions:

  • No pida entradas de los usuarios durante una transacción.Do not require input from users during a transaction.
    Obtenga todas las entradas necesarias de los usuarios antes de iniciar la transacción.Get all required input from users before a transaction is started. Si se necesita una entrada adicional del usuario durante una transacción, revierta la transacción actual y reinicie la transacción después de que el usuario proporcione la entrada.If additional user input is required during a transaction, roll back the current transaction and restart the transaction after the user input is supplied. Aunque los usuarios respondan inmediatamente, los tiempos de reacción de las personas son mucho menores que la velocidad del equipo.Even if users respond immediately, human reaction times are vastly slower than computer speeds. Todos los recursos que mantiene la transacción se conservan durante un tiempo extremadamente largo que, en potencia, puede provocar problemas de bloqueos.All resources held by the transaction are held for an extremely long time, which has the potential to cause blocking problems. Si los usuarios no responden, la transacción permanece activa y bloquea recursos críticos hasta que lo hagan, lo que puede tardar en suceder varios minutos o incluso horas.If users do not respond, the transaction remains active, locking critical resources until they respond, which may not happen for several minutes or even hours.

  • No abra una transacción mientras examina los datos si es posible.Do not open a transaction while browsing through data, if at all possible.
    No puede iniciar las transacciones hasta que haya completado todos los análisis preliminares de datos.Transactions should not be started until all preliminary data analysis has been completed.

  • Haga la transacción lo más corta posible.Keep the transaction as short as possible.
    Una vez que sepa las modificaciones que debe realizar, inicie una transacción, ejecute las instrucciones de modificación e, inmediatamente, confirme o revierta las operaciones.After you know the modifications that have to be made, start a transaction, execute the modification statements, and then immediately commit or roll back. No abra la transacción antes de que sea necesario.Do not open the transaction before it is required.

  • Para reducir el bloqueo, considere la posibilidad de utilizar un nivel de aislamiento basado en versiones de fila para las consultas de solo lectura.To reduce blocking, consider using a row versioning-based isolation level for read-only queries.

  • Haga un uso inteligente de los niveles más bajos de aislamiento de las transacciones.Make intelligent use of lower transaction isolation levels.
    Se pueden codificar rápidamente muchas aplicaciones para que utilicen un nivel de aislamiento de lectura confirmada de las transacciones.Many applications can be readily coded to use a read-committed transaction isolation level. No todas las transacciones necesitan el nivel de aislamiento serializable de las transacciones.Not all transactions require the serializable transaction isolation level.

  • Haga un uso inteligente de las opciones de simultaneidad más bajas de los cursores, como las opciones de simultaneidad optimista.Make intelligent use of lower cursor concurrency options, such as optimistic concurrency options.
    En un sistema que tenga pocas probabilidades de que se produzcan actualizaciones simultáneas, la sobrecarga que produce encontrar el error ocasional "alguien cambió los datos después de su lectura" puede ser mucho menor que la sobrecarga que produce bloquear siempre las filas a medida que se leen.In a system with a low probability of concurrent updates, the overhead of dealing with an occasional "somebody else changed your data after you read it" error can be much lower than the overhead of always locking rows as they are read.

  • Tenga acceso a la menor cantidad de datos posible en una transacción.Access the least amount of data possible while in a transaction.
    Así reduce el número de filas bloqueadas y, por lo tanto, disminuye el conflicto entre transacciones.This lessens the number of locked rows, thereby reducing contention between transactions.

  • Evite las sugerencias de bloqueo pesimistas, como "holdlock", siempre que sea posible.Avoid pessimistic locking hints such as holdlock whenever possible. Las sugerencias como HOLDLOCK o el nivel de aislamiento SERIALIZABLE pueden provocar que los procesos esperen incluso en bloqueos compartidos y que se reduzca la simultaneidad.Hints like HOLDLOCK or SERIALIZABLE isolation level can cause processes to wait even on shared locks and reduce concurrency

  • Evite el uso de transacciones implícitas cuando tales transacciones implícitas puedan presentar un comportamiento imprevisible debido a su naturaleza.Avoid using Implicit transactions when possible Implicit transactions can introduce unpredictable behavior due to their nature. Consulte Transacciones implícitas y prevención de problemas de simultaneidad y de recursos.See Implicit Transactions and concurrency problems

  • Diseñe índices con un factor de relleno reducido. El hecho de reducir el factor de relleno puede ayudarle a evitar o disminuir la fragmentación de las páginas de índice y, por tanto, reducir los tiempos de búsqueda de índices, especialmente cuando se recuperan del disco.Design indexes with a reduced fill factor Decreasing the fill factor may help you prevent or decrease fragmentation of index pages and thus reduce index seek times especially when retrieved from disk. Para ver información sobre la fragmentación de los datos e índices de una tabla o vista, puede usar sys.dm_db_index_physical_stats.To view fragmentation information for the data and indexes of a table or view, you can usesys.dm_db_index_physical_stats.

Transacciones implícitas y prevención de problemas de simultaneidad y de recursosImplicit transactions and avoiding concurrency and resource problems

Para evitar los problemas de simultaneidad y de recursos, administre cuidadosamente las transacciones implícitas.To prevent concurrency and resource problems, manage implicit transactions carefully. Cuando utilice transacciones implícitas, la siguiente instrucción Transact-SQLTransact-SQL después de COMMIT o ROLLBACK inicia automáticamente una nueva transacción.When using implicit transactions, the next Transact-SQLTransact-SQL statement after COMMIT or ROLLBACK automatically starts a new transaction. Esto puede hacer que se abra una nueva transacción mientras la aplicación examina los datos o, incluso, cuando pide una entrada del usuario.This can cause a new transaction to be opened while the application browses through data, or even when it requires input from the user. Tras concluir la última transacción necesaria para proteger las modificaciones de los datos, desactive las transacciones implícitas hasta que se necesite de nuevo una transacción para proteger las modificaciones de los datos.After completing the last transaction required to protect data modifications, turn off implicit transactions until a transaction is once again required to protect data modifications. Este proceso permite que el Motor de base de datos de SQL ServerSQL Server Database Engine utilice el modo de confirmación automática mientras la aplicación examina los datos y obtiene la entrada del usuario.This process lets the Motor de base de datos de SQL ServerSQL Server Database Engine use autocommit mode while the application is browsing data and getting input from the user.

Además, cuando el nivel de aislamiento de instantánea está habilitado, aunque una transacción nueva no mantenga bloqueos, una transacción de larga duración impedirá que las versiones anteriores se eliminen de tempdb.In addition, when the snapshot isolation level is enabled, although a new transaction will not hold locks, a long-running transaction will prevent the old versions from being removed from tempdb.

Administrar las transacciones de ejecución prolongadaManaging long-running transactions

Una transacción de larga duración es una transacción activa que no se ha confirmado ni revertido puntualmente.A long-running transaction is an active transaction that has not been committed or roll backed the transaction in a timely manner. Por ejemplo, si el usuario controla el inicio y la finalización de una transacción, una causa frecuente de las transacciones de ejecución prolongada es que un usuario inicie una transacción y se ausente mientras la transacción queda en espera de una respuesta suya.For example, if the beginning and end of a transaction is controlled by the user, a typical cause of a long-running transaction is a user starting a transaction and then leaving while the transaction waits for a response from the user.

Una transacción de larga duración puede provocar graves problemas para una base de datos de la siguiente manera:A long running transaction can cause serious problems for a database, as follows:

Descubrir transacciones de ejecución prolongadaDiscovering long-running transactions

Para buscar las transacciones de ejecución prolongada, use una de las opciones siguientes:To look for long-running transactions, use one of the following:

  • sys.dm_tran_database_transactionssys.dm_tran_database_transactions

    Esta vista de administración dinámica devuelve información sobre las transacciones en la base de datos.This dynamic management view returns information about transactions at the database level. En una transacción de ejecución prolongada, las columnas de especial interés incluyen la hora de la primera entrada del registro (database_transaction_begin_time), el estado actual de la transacción (database_transaction_state) y el número de flujo de registro (LSN) del registro inicial del registro de transacciones (database_transaction_begin_lsn).For a long-running transaction, columns of particular interest include the time of the first log record (database_transaction_begin_time), the current state of the transaction (database_transaction_state), and the log sequence number (LSN) of the begin record in the transaction log (database_transaction_begin_lsn).

    Para obtener más información, consulte sys.dm_tran_database_transactions (Transact-SQL).For more information, see sys.dm_tran_database_transactions (Transact-SQL).

  • DBCC OPENTRAN

    Esta instrucción permite identificar el Id. de usuario del propietario de la transacción, por lo que se puede realizar un seguimiento del origen de la misma para terminarla de forma más ordenada (confirmándola en lugar de revirtiéndola).This statement lets you identify the user ID of the owner of the transaction, so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back). Para obtener más información, vea DBCC OPENTRAN (Transact-SQL).For more information, see DBCC OPENTRAN (Transact-SQL).

Detener una transacciónStopping a Transaction

Puede que deba utilizar la instrucción KILL.You may have to use the KILL statement. Sin embargo, utilice esta instrucción con sumo cuidado, especialmente cuando se estén ejecutando procesos críticos.Use this statement very carefully, however, especially when critical processes are running. Para obtener más información, consulte KILL (Transact-SQL).For more information, see KILL (Transact-SQL).

Lecturas adicionalesAdditional Reading

Overhead of Row Versioning (Sobrecarga de las versiones de fila)Overhead of Row Versioning
Eventos extendidos Extended Events
sys.dm_tran_locks (Transact-SQL) sys.dm_tran_locks (Transact-SQL)
Funciones y vistas de administración dinámica (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
Funciones y vistas de administración dinámica relacionadas con transacciones (Transact-SQL)Transaction Related Dynamic Management Views and Functions (Transact-SQL)