Transacciones de durabilidad diferida

Esta característica aplicable desde SQL Server 2014, por base de datos, es muy interesante, pero con mucho control.

Por defecto SQL Server confirma las transacciones de un modo sincrónico, notificando el commit como correcto o incorrecto únicamente tras escribir en disco los detalles de la transacción.

Pero si se habilita la durabilidad diferida, esto no tiene porque ser así, lo cual puede hacer que determinadas transacciones se terminen, muchísimo más rápido.

Imagen de Pete Linforth en Pixabay

Vamos a hacer una demo, para ver como funciona, en AdventureWorks2019.

La opción set que hay que configurar, tiene 3 posibilidades.

• Allowed – Permite las transacciones.
• Forced – Todas son con la característica habilitada.
• Disabled – Se desactiva su uso.

-- ## Code 1.0

ALTER DATABASE [AdventureWorks2019]
	SET DELAYED_DURABILITY = Allowed;

Aplicarlo es una simple ejecución inmediata, realizando un alter a la base de datos en cuestión.

Vamos a realizar una update, sobre los registros de la tabla más poblada, mediante un cursor, anotando el tiempo de ejecución. Así simularemos muchas transacciones pequeñitas.

Referencia: ¿Como se implementa un cursor?

-- ## Code 1.1 

Declare @id int;
Declare @detailId int;

Declare elCursor Cursor For
Select sod.SalesOrderID, sod.SalesOrderDetailID
	From Sales.SalesOrderDetail sod
Order by SalesOrderID, SalesOrderDetailID;

Open elCursor;
Fetch next From elCursor Into @id, @detailId;
While (@@FETCH_STATUS=0)
Begin;

	Begin tran;
	-- la sentencia que ejecutará 121.317 veces
	update Sales.SalesOrderDetail
		Set ModifiedDate=GETDATE()
			where SalesOrderID = @id 
				And SalesOrderDetailID = @detailId 

	Commit tran;

Fetch next From elCursor Into @id, @detailId;
End;
Close elCursor;
Deallocate elCursor;

Ahora vamos a hacer lo mismo, pero con la característica. El código es idéntico a excepción del commit.

Nota: La tabla escogida, contiene además un trigger de update, por eso la ejecución es «tan lenta», ya que el mismo tiene código asociado.

-- #Code 1.2


Declare @id int;
Declare @detailId int;

Declare elCursor Cursor For
Select sod.SalesOrderID, sod.SalesOrderDetailID
	From Sales.SalesOrderDetail sod
Order by SalesOrderID, SalesOrderDetailID;

Open elCursor;
Fetch next From elCursor Into @id, @detailId;
While (@@FETCH_STATUS=0)
Begin;
	
	Begin tran;
	
	update Sales.SalesOrderDetail
		Set ModifiedDate=GETDATE()
			where SalesOrderID = @id 
				And SalesOrderDetailID = @detailId 
	-- commit tran aquí la diferencia.
	Commit tran WITH(DELAYED_DURABILITY = ON);;

Fetch next From elCursor Into @id, @detailId;
End;
Close elCursor;
Deallocate elCursor;

Creo que la ganancia es bastante perceptible, pasar de 08 minutos 33 segudos a 7 minutos 20 segundos, contando que la update tiene un trigger, que suma a la cabecera, as cantidades del detalle, además de evaluar joins con otras tablas para modificar datos de tipo geografía en Person.Person …..vamos que hace un montón de código por detrás, y por el hecho de hacer un set, haya una mejora tan importante.

Nota para asegurarse de que las transacciones están en disco y no permanecen en memoria, se puede ejecutar el procedimiento

exec sp_flush_log;

Pero todo esto tiene un agujero, claro. Sino ya vendría activado por defecto.

Si hay un fallo catastrófico, una transacción confirmada, puede no contener los datos.

La documentación oficial establece como casos adecuados para la durabilidad diferida.

• Puede tolerar alguna pérdida de datos.
  • Si puede tolerar cierta pérdida de datos, por ejemplo cuando los registros individuales no son críticos siempre y cuando tenga la mayoría de los datos, puede resultar útil usar la durabilidad diferida. Si no puede tolerar la pérdida de datos, no utilice la durabilidad diferida de transacciones.
• Experimenta cuellos de botella en la escritura del transaction log.
  • Si los problemas de rendimiento se deben a la latencia en la escritura de registros de transacciones, seguramente la aplicación se beneficiará de utilizar la durabilidad diferida de transacciones.
• Las cargas de trabajo conllevan un alto índice de contención.
  • Si el sistema tiene cargas de trabajo con un alto índice de contención, se perderá mucho tiempo esperando a que se liberen los bloqueos. La durabilidad diferida de las transacciones reduce el tiempo de confirmación y, por tanto, libera los bloqueos con mayor rapidez, lo que redunda en un mayor rendimiento.

Puedes informarte mucho más, si te interesa para algún proceso puntual, en la documentación oficial. Controlar la durabilidad de las transacciones.

Espero te ayude conocer esta característica.