¿Es posible cambiar el modo de transacciones explícitas (auto commit) de SQL Server? ¿IMPLICIT_TRANSACTIONS ON or OFF?

Este capítulo explica los comportamiento de transacciones explícitas (explicit transactions ó autocommit) y transacciones implícitas (implicit transactions), ambos disponibles en SQL Server (por defecto se utilizan transacciones explícitas). Se explica la relacción de estos comportamientos con la sentencia BEGIN TRAN y con los modos de aislamiento, así como con las transacciones anidadas (nested transactions). Se explica también como establecer el modo de transacciones explícitas o implícitas (sentencia SET IMPLICIT_TRANSACTIONS), etc.

SQL Server utiliza por defecto el modo de transacciones explícitas (el conocido auto commit), lo cual implica que , que la ejecución de una sentencia DML (ej: UPDATE) se confirma automáticamente. Por ello, si deseamos ejecutar varias sentencias DML (ej: varios UPDATE), será necesario de forma explícita iniciar una transacción (BEGIN TRAN), ejecutar las sentencias DML deseadas, y finalmente confirmar o deshacer la transacción (COMMIT ó ROLLBACK). De aquí la denominación de transacciones explícitas (IMPLICIT_TRANSACTIONS OFF) para el modo de funcionamiento con auto commit. Este modo de comportamiento resulta sorprendente para muchos administradores y programadores de base de datos que llegan a SQL Server desde ORACLE (recordar, que otros motores como INFORMIX o Sybase, funcionan igual que SQL Server). El motivo es queORACLE utiliza transacciones implícitas, es decir, siempre que se inicia una nueva sesión o se confirma o deshace (COMMIT o ROLLBACK) la transacción actual, se inicia una nueva transacción. Resulta de interés observar, que en el modo de transacciones implícitas, NO es necesario iniciar la transacción explícitamente con un BEGIN TRAN, sin embargo, deberemos recordar confirmar o deshacer (COMMIT ó ROLLBACK) la transacción, ya que si la sesión finaliza sin haber confirmado los cambios, se realizará un ROLLBACK y se perderán dichos cambios. En SQL Server es posible utilizar el modo de transacciones implícitas (sin auto commit - IMPLICIT_TRANSACTIONS ON), y disfrutar de éste modo de funcionamiento. Evidentemente, al trabajar en el modo de transacciones implícitas, es posible utilizar BEGIN TRAN para iniciar transacciones anidadas (igual que es posible utilizar transacciones anidadas en modo auto commit). Llegados a éste punto, surge la siguiente pregunta ¿Por qué los motores como SQL Server e Informix utilizan transacciones explícitas mientras que ORACLE utiliza transacciones implícitas? Me alegra que te hagas esta pregunta ! Esto es debido a que el funcionamiento de ORACLE se basa en el versionado de filas (row versioning - y NO en los bloqueos), por lo cual, al iniciar una transacción puede pasar todo el tiempo que sea necesario, que otras transacciones podrán realizar lecturas correctamente (accediendo a la versión correcta de cada filas). Sin embargo, el funcionamiento de SQL Server se basa en los bloqueos, de tal modo que al iniciar la transacción, según se ejecuten las DML (ej: los UPDATES) se crearán bloqueos en las filas correspondientes (corriendo el riesgo de que puedan escalar a bloqueos de página o incluso a bloqueos de tabla) luego las lecturas realizadas por otras transacciones quedarán bloqueadas (excepto que se utilicen el modo de aislamiento de lecturas sucias - READ UNCOMMITTED -, pero por defecto el modo de aislamiento es READ COMMITTED), impactando en el rendimiento y tiempo de respuesta. A todo esto, el hecho de que el funcionamiento de ORACLE se base en el versionado de filas, no implica que NO se puedan generar bloqueos. De hecho, al programar procesos con PL/SQL en ORACLE (ej: regularizaciones de fin de mes, facturaciones, etc.), muchos programadores utilizan sentencias del tipo SELECT FOR UPDATE, por poner un ejemplo. Desde SQL Server 2005, es posible utilizar el modo de transacciones implícitas junto con el versionado de filas, si realmente deseamos éste funcionamiento. Otro detalle a contar es ¿Cómo se puede establecer el modo de transacciones explícitas (auto commit) o el modo de transacciones implícitas? Por defecto, SQL Server utiliza el modo de transacciones explícitas (auto commit) pudiendo cambiar de un modo a otro modo mediante la sentencia SET IMPLICIT_TRANSACTIONS { ON | OFF }. Del mismo modo, puede resultar de utilidad consultar el valor de la variable de sistema @@TRANCOUNT con el fin de conocer el número de transacciones abiertas por nuestra sesión (ej: si es mayor de 1, es debido a que tenemos transacciones anidadas).