{"id":1931,"date":"2020-05-26T19:55:34","date_gmt":"2020-05-26T19:55:34","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=1931"},"modified":"2021-09-22T03:41:39","modified_gmt":"2021-09-22T03:41:39","slug":"transacciones-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=1931","title":{"rendered":"Transacciones en SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"255\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_TransaccionesSQLServer___D.png\" alt=\"\" class=\"wp-image-1932\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Transacciones en SQL Server<\/h2>\n\n\n\n<p style=\"font-size:18px\"><strong>Caracter\u00edsticas de las transacciones<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\"><li><strong>Atomicidad<\/strong>: significa que las instrucciones de la transacci\u00f3n tienen \u00e9xito o fallan juntas. A menos que todos las instrucciones se ejecuten correctamente la transacci\u00f3n ser\u00e1 completada.<\/li><li><strong>Consistencia<\/strong>: significa que las instrucciones en una transacci\u00f3n tiene un estado consistente. La transaccion lleva la base de datos subyacente de un estado estable a otro, sin reglas violadas antes de la comenzando o despu\u00e9s del final de la transacci\u00f3n.<\/li><li><strong>Aislamiento<\/strong>: cada transacci\u00f3n es una entidad independiente. Una transacci\u00f3n no afectar\u00e1 a ninguna otra transacci\u00f3n que se ejecuta al mismo tiempo.<\/li><li><strong>Durabilidad<\/strong>: cada transacci\u00f3n se mantiene en un medio confiable que no se puede deshacer mediante fallas del sistema. Adem\u00e1s, si una falla del sistema ocurre en medio de una transacci\u00f3n,los pasos completados deben deshacerse o los pasos incompletos deben ejecutarse para terminar la transacci\u00f3n. Esto suele ocurrir mediante el uso de un registro que se puede reproducir para volver el sistema a un estado consistente.<\/li><\/ol>\n\n\n\n<!--more-->\n\n\n\n<p style=\"font-size:22px\"><strong>Alcance de las transacciones<\/strong><\/p>\n\n\n\n<p>Dependiendo de la forma de como trabajan las transacciones pueden ser de dos tipos:<br><strong>Transacciones Locales<\/strong>: las que trabajan en una sola base de datos.<br><strong>Transacciones distribuidas<\/strong>: las que trabajan en m\u00faltiples bases de datos.<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Transacciones Locales en SQL Server<\/strong><\/p>\n\n\n\n<p>Las transacciones que trabajan en una sola base de datos se llaman transacciones locales, estas transacciones tienen cuatro modos diferentes:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li><strong>AutoCommit<\/strong><\/li><li><strong>Explicit<\/strong><\/li><li><strong>Implicit<\/strong><\/li><li><strong>Batch-scope<\/strong><\/li><\/ol>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:22px\"><strong>Transacciones en modo AutoCommit<\/strong><\/p>\n\n\n\n<p>El modo AutoCommit, llamado transacci\u00f3n de confirmaci\u00f3n autom\u00e1tica es el modo de transacci\u00f3n predeterminado.En este modo, SQL Server garantiza la seguridad de los datos durante toda la vida \u00fatil de la ejecuci\u00f3n  de la consulta, independientemente de si ha solicitado o no una transacci\u00f3n Por ejemplo, si ejecuta una instrucci\u00f3n de lenguaje de manipulaci\u00f3n de datos (DML) (ACTUALIZAR,INSERTAR, o ELIMINAR), los cambios se confirmar\u00e1n autom\u00e1ticamente (si no se producen errores) o se revertir\u00e1n (deshecho) en caso contrario.<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Transacciones en modo Explicit<\/strong><\/p>\n\n\n\n<p>El modo de transacci\u00f3n AutoCommit permite ejecutar instrucciones  individuales de manera transaccional, pero con frecuencia, se requiere que un lote de instrucciones funcione dentro de una sola transacci\u00f3n. En ese escenario, se debe utilizar transacciones expl\u00edcitas. En el modo de transacci\u00f3n expl\u00edcita, se solicita expl\u00edcitamente los l\u00edmites de una transacci\u00f3n. En otras palabras, se especifica con precisi\u00f3n cu\u00e1ndo comienza la transacci\u00f3n y cu\u00e1ndo termina.<\/p>\n\n\n\n<p>Al terminar el modo Explicit, SQL Server contin\u00faa trabajando en el modo de transacci\u00f3n AutoCommit hasta que solicite una excepci\u00f3n a la regla, por lo que si desea ejecutar una serie de sentencias Transact-SQL (T-SQL) como un solo lote, usa el modo de transacci\u00f3n expl\u00edcito en su lugar. Para iniciar la transacci\u00f3n en modo Explicit se utiliza la instrucci\u00f3n BEGIN TRANSACTION.<\/p>\n\n\n\n<p style=\"font-size:18px\"><strong>Sintaxis para crear transacciones<\/strong><\/p>\n\n\n\n<p>Begin  Transaction  [NombreTransaccion]  [With Mark [&#8216;descripci\u00f3n&#8217;]]<\/p>\n\n\n\n<p><br><strong>Al terminar una transacci\u00f3n en modo Explicit se puede usar:<\/strong><br>Commit  Transaction    [NombreTransaccion]<br><strong>Para terminar de manera exitosa la transacci\u00f3n<\/strong><\/p>\n\n\n\n<p>Rollback  Transaction    [NombreTransaccion]<br><strong>Para anular todas las instrucciones de la transacci\u00f3n<\/strong><\/p>\n\n\n\n<p>Las instrucciones para terminar o anular la transacci\u00f3n se incluyen dentro de estructuras condicionales para comprobar si hubo o no alg\u00fan error al ejecutar el conjunto de instrucciones de la transacci\u00f3n.<\/p>\n\n\n\n<p style=\"font-size:18px\"><strong>Transacciones explicit anidadas<\/strong><\/p>\n\n\n\n<p><strong>El uso de una transacci\u00f3n dentro de otra refiere el uso de transacciones anidadas.<\/strong><br>Ejemplo<br>BEGIN TRANSACTION Primera<br>Insert into MiTabla1 VALUES (&#8216;C09&#8242;,&#8217;Mi Dato&#8217;)<br>BEGIN TRANSACTION Segunda<br>Insert into MiTabla1 VALUES (&#8216;C15&#8242;,&#8217;Otro Dato&#8217;)<br>COMMIT TRANSACTION Segunda<br>ROLLBACK Transaction Primera<br>A pesar de haber terminado de manera exitosa la transacci\u00f3n Segunda, la inserci\u00f3n del registro \u00abC09\u00bb con el valor \u00abMi Dato\u00bb no se realiza porque la transacci\u00f3n Segunda en una transacci\u00f3n anidada en la transacci\u00f3n Primera que es anulada con la instrucci\u00f3n Rollback.<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Transacciones en SQL Server en modo Implicit<\/strong><\/p>\n\n\n\n<p>SQL Server Management Studio (SSMS) o SQL Server Data Tools (SSDT) por defecto tienen conexi\u00f3n en modo de transacci\u00f3n autom\u00e1tica lo que significa que al ejecutar una instrucci\u00f3n DML, los cambios se guardan  autom\u00e1ticamente. El modo Implicit en las transacciones en SQL Server requiere que los cambios sean confirmados usando Commit o anulados usando Rollback.<\/p>\n\n\n\n<p>Para configurar la conexi\u00f3n de la base de datos al modo de transacci\u00f3n impl\u00edcita se utliza la sentencia<br><strong>SET IMPLICIT_TRANSACTIONS {ON | OFF}<br><\/strong>Una transacci\u00f3n se inicia autom\u00e1ticamente cuando se ejecuta cualquiera de los siguientes instrucciones:<br>ALTER table, Create, Delete, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, o UPDATE.<\/p>\n\n\n\n<p>El t\u00e9rmino impl\u00edcito se refiere al hecho de que una transacci\u00f3n se inicia impl\u00edcitamente sin una Declaraci\u00f3n expl\u00edcita BEGIN TRANSACTION. Por lo tanto, siempre es necesario que expl\u00edcitamente confirme la transacci\u00f3n posteriormente para guardar los cambios (o revertirla para descartarlos).<\/p>\n\n\n\n<p>Con el modo de transacci\u00f3n impl\u00edcita, la transacci\u00f3n que comienza impl\u00edcitamente no se termina o revierte a menos que se solicite expl\u00edcitamente. Esto significa que si emite una sentencia UPDATE, SQL Server mantendr\u00e1 un bloqueo en los datos afectados hasta que emita un COMMIT o ROLLBACK. Si no emite una instrucci\u00f3n COMMIT o ROLLBACK, la transacci\u00f3n se anula cuando el usuario desconecta<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Transacciones en SQL en modo Batch-Scope<\/strong><\/p>\n\n\n\n<p>Desde SQL Server 2005, se admiten <strong>m\u00faltiples conjuntos de resultados activos<\/strong> (MARS) en la misma conexi\u00f3n, esto no significa que haya ejecuci\u00f3n paralela de comandos. El comando ejecuci\u00f3n todav\u00eda est\u00e1 intercalado<br>con reglas estrictas que rigen qu\u00e9 declaraciones pueden sobrepasar otras declaraciones.<\/p>\n\n\n\n<p>Las conexiones que utilizan MARS tienen un entorno de ejecuci\u00f3n por lotes asociado. En la ejecuci\u00f3n por lotes el entorno contiene varios componentes, como las opciones SET, el contexto de seguridad, el contexto de la base de datos, y las variables de estado de ejecuci\u00f3n, que definen el entorno en el que se ejecutan los comandos. <\/p>\n\n\n\n<p>Cuando MARS est\u00e1 habilitado, puede tener m\u00faltiples lotes intercalados ejecut\u00e1ndose al mismo tiempo, por lo que todos los cambios realizados en el entorno de ejecuci\u00f3n se aplican al lote espec\u00edfico hasta la ejecuci\u00f3n de ese lote esta completo Una vez finalizada la ejecuci\u00f3n del lote, se copian los ajustes de ejecuci\u00f3n al entorno por defecto. Por lo tanto, se dice que una conexi\u00f3n utiliza el modo de transacci\u00f3n de \u00e1mbito por lotes si est\u00e1 ejecutando una transacci\u00f3n, tiene habilitado MARS en \u00e9l y tiene varios lotes intercalados ejecut\u00e1ndose al mismo tiempo.<\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicios<\/strong><\/p>\n\n\n\n<p>Usando la base de datos Northwind<br>use Northwind<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Insertar un registro en la tabla Region usando transacci\u00f3n. (<a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=294\" target=\"_blank\">Ver Insertar registros<\/a>)<\/strong><br>Begin transaction InsertaRegion<br>insert into Region ([RegionID], [RegionDescription])<br>values (30,&#8217;Lima&#8217;)<br>if @@ERROR &lt;&gt; 0<br>Begin<br>Rollback Transaction InsertaRegion<br>Print &#8216;Anulada\u2026 no se insert\u00f3&#8217;<br>End<br>Else &#8212; No hay error<br>Begin<br>Commit Transaction InsertaRegion<br>Print &#8216;Se insert\u00f3 la regi\u00f3n&#8217;<br>End<br>go<\/p>\n\n\n\n<p><strong>En el ejercicio anterior se han inclu\u00eddo mensajes con Print s\u00f3lo para efectos de comprobar que la transacci\u00f3n se ejecuta o no, o como termina, en producci\u00f3n no use los mensajes con Print.<\/strong><\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Crear un Store Procedure (<a href=\"https:\/\/manualsqlserver.com\/?p=314\" target=\"_blank\" rel=\"noreferrer noopener\">Ver procedimientos almacenados<\/a>) para insertar una regi\u00f3n.<\/strong><br>Create procedure spRegionInserta<br>(<br>@RegionID int,<br>@RegionDescription nvarchar(50)<br>)<br>As<br>Begin transaction InsertaRegion<br>insert into Region ([RegionID], [RegionDescription])<br>values (@RegionID,@RegionDescription)<br>if @@ERROR &lt;&gt; 0<br>Begin<br>Rollback Transaction InsertaRegion<br>End<br>Else &#8212; No hay error<br>Begin<br>Commit Transaction InsertaRegion<br>End<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>Crear un trigger en la tabla Region que no permita insertar un registro con la descripci\u00f3n duplicada. (<a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=385\" target=\"_blank\">Ver Triggers<\/a>)<\/strong><\/p>\n\n\n\n<p>if exists (select * from sys.triggers where name = &#8216;trRegionNoPermiteDuplicados&#8217;)<br>Begin<br>Drop trigger trRegionNoPermiteDuplicados<br>End<br>go<br>Create trigger trRegionNoPermiteDuplicados<br>on Region for Insert, update<br>As<br>Begin<br>if (select count(*) from Region, inserted<br>where Region.RegionDescription = inserted.RegionDescription)&gt;1<br>Begin<br>Rollback Transaction<br>Print &#8216;Ya existe una regi\u00f3n con el nombre insertado&#8217;<br>End<br>Else<br>Begin<br>Print &#8216;Se insert\u00f3 el registro, mensaje desde Trigger&#8217;<br>End<br>End<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 4<\/strong><\/p>\n\n\n\n<p><strong>Este c\u00f3digo muestra transacciones anidadas. Tiene en cuenta el registro de una Factura y el detalle de la misma.<\/strong><\/p>\n\n\n\n<p>Begin transaction GuardaFactura<br>\u2026<br>\u2026<br>\u2026 Instrucciones para guardar la factura<br>Begin Transaction GuardaDetalleFactura<br>Declare @vDetalle nchar(1) = &#8216;S&#8217;<br>\u2026<br>If @@ERROR = 0 &#8212; Error al guardar el detalle<br>Begin<br>Commit tran GuardaDetalleFactura<br>End<br>Else<br>Begin<br>Set @vDetalle = &#8216;E&#8217;<br>Rollback tran GuardaDetalleFactura<br>End<br>&#8212; Comprueba error al guardar el detalle<br>if @vDetalle = &#8216;E&#8217;<br>Begin<br>Rollback tran GuardaFactura<br>End<br>Else<br>Begin<br>Commit tran GuardaFactura<br>End<br>go<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Transacciones en SQL Server Caracter\u00edsticas de las transacciones Atomicidad: significa que las instrucciones de la transacci\u00f3n tienen \u00e9xito o fallan juntas. A menos que todos las instrucciones se ejecuten correctamente la transacci\u00f3n ser\u00e1 completada. Consistencia: significa que las instrucciones en una transacci\u00f3n tiene un estado consistente. La transaccion lleva la base de datos subyacente de &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=1931\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1932,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[211,213,214,212],"class_list":["post-1931","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programacion","tag-begin-transaction","tag-commit","tag-rolback","tag-transaction","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1931","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1931"}],"version-history":[{"count":2,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1931\/revisions"}],"predecessor-version":[{"id":2591,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1931\/revisions\/2591"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1932"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1931"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1931"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1931"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}