sql - los - que es un deadlock en base de datos

¿Cómo simular un interbloqueo en SQL Server en un solo proceso?

Question

Nuestro código del lado del cliente detecta puntos muertos, espera un intervalo y luego reintenta la solicitud hasta 5 veces. La lógica de reintento detecta los puntos muertos basándose en el número de error 1205.

Mi objetivo es probar la lógica de reintento de interbloqueo y el manejo de interbloqueo dentro de varios procedimientos almacenados. Puedo crear un interbloqueo usando dos conexiones diferentes. Sin embargo, me gustaría simular un interbloqueo dentro de un solo procedimiento almacenado.

Un interbloqueo genera el siguiente mensaje de error:

Msg 1205, Nivel 13, Estado 51, Línea 1
La transacción (Id. De proceso 66) se bloqueó en los recursos de bloqueo con otro proceso y se eligió como la víctima del punto muerto. Vuelva a ejecutar la transacción.

Veo que este mensaje de error está en sys.messages :

select * from sys.messages where message_id = 1205 and language_id = 1033
message_id language_id severity is_event_logged text
1205 1033 13 0 Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

No puedo levantar este error usando RAISERROR :

raiserror(1205, 13, 51)

Mensaje 2732, Nivel 16, Estado 1, Línea 1
El número de error 1205 no es válido. El número debe ser desde 13000 hasta 2147483647 y no puede ser 50000.

Nuestra lógica de reintento de interbloqueo comprueba si el número de error es 1205. El interbloqueo debe tener el mismo ID de mensaje, nivel y estado como un interbloqueo normal.

¿Hay una manera de simular un interbloqueo (con RAISERROR o por cualquier otro medio) y obtener el mismo número de mensaje con un solo proceso ?

Nuestras bases de datos utilizan la compatibilidad con SQL 2005, aunque nuestros servidores varían de 2005 a 2008 R2.

Answer 1

(Aparentemente no tengo la reputación suficiente para agregar un comentario. Por lo tanto, publicar como respuesta).

Un punto muerto requiere al menos dos procesos. La única excepción es que se producen interbloqueos paralelos paralelos que son imposibles de reproducir.

Sin embargo, puede simular un interbloqueo en dos procesos que ejecutan exactamente la misma consulta (o sp). Algunas ideas here

Answer 2

Como muchos han señalado, la respuesta es no, un solo proceso no se puede bloquear de manera confiable. Se me ocurrió la siguiente solución para simular un punto muerto en un sistema de desarrollo o prueba.

Ejecute el script a continuación en una ventana de SQL Server Management Studio. (Probado solo en 2008 R2). Puede dejarlo funcionando todo el tiempo que sea necesario.

En el lugar donde desee simular un interbloqueo, inserte una llamada a sp_simulatedeadlock . Ejecute su proceso, y el punto muerto debe ocurrir.

Cuando termine la prueba, detenga la consulta SSMS y ejecute el código de limpieza en la parte inferior.

/*
This script helps simulate deadlocks. Run the entire script in a SQL query window. It will continue running until stopped.
In the target script, insert a call to sp_simulatedeadlock where you want the deadlock to occur.
This stored procedure, also created below, causes the deadlock.
When you are done, stop the execution of this window and run the code in the cleanup section at the bottom.
*/
set nocount on
if object_id(''DeadlockTest'') is not null
drop table DeadlockTest
create table DeadlockTest
(
Deadlock_Key int primary key clustered,
Deadlock_Count int
)
go
if exists (select * from sysobjects where id = object_id(N''sp_simulatedeadlock'')
AND objectproperty(id, N''IsProcedure'') = 1)
drop procedure sp_simulatedeadlock
GO
create procedure sp_simulatedeadlock
(
@MaxDeadlocks int = -1 -- specify the number of deadlocks you want; -1 = constant deadlocking
)
as begin
set nocount on
if object_id(''DeadlockTest'') is null
return
-- Volunteer to be a deadlock victim.
set deadlock_priority low
declare @DeadlockCount int
select @DeadlockCount = Deadlock_Count -- this starts at 0
from DeadlockTest
where Deadlock_Key = 2
-- Trace the start of each deadlock event.
-- To listen to the trace event, setup a SQL Server Profiler trace with event class "UserConfigurable:0".
-- Note that the user running this proc must have ALTER TRACE permission.
-- Also note that there are only 128 characters allowed in the trace text.
declare @trace nvarchar(128)
if @MaxDeadlocks > 0 AND @DeadlockCount > @MaxDeadlocks
begin
set @trace = N''Deadlock Test @MaxDeadlocks: '' + cast(@MaxDeadlocks as nvarchar) + N'' @DeadlockCount: '' + cast(@DeadlockCount as nvarchar) + N'' Resetting deadlock count. Will not cause deadlock.''
exec sp_trace_generateevent
@eventid = 82, -- 82 = UserConfigurable:0 through 91 = UserConfigurable:9
@userinfo = @trace
-- Reset the number of deadlocks.
-- Hopefully if there is an outer transaction, it will complete and persist this change.
update DeadlockTest
set Deadlock_Count = 0
where Deadlock_Key = 2
return
end
set @trace = N''Deadlock Test @MaxDeadlocks: '' + cast(@MaxDeadlocks as nvarchar) + N'' @DeadlockCount: '' + cast(@DeadlockCount as nvarchar) + N'' Simulating deadlock.''
exec sp_trace_generateevent
@eventid = 82, -- 82 = UserConfigurable:0 through 91 = UserConfigurable:9
@userinfo = @trace
declare @StartedTransaction bit
set @StartedTransaction = 0
if @@trancount = 0
begin
set @StartedTransaction = 1
begin transaction
end
-- lock 2nd record
update DeadlockTest
set Deadlock_Count = Deadlock_Count
from DeadlockTest
where Deadlock_Key = 2
-- lock 1st record to cause deadlock
update DeadlockTest
set Deadlock_Count = Deadlock_Count
from DeadlockTest
where Deadlock_Key = 1
if @StartedTransaction = 1
rollback
end
go
insert into DeadlockTest(Deadlock_Key, Deadlock_Count)
select 1, 0
union select 2, 0
-- Force other processes to be the deadlock victim.
set deadlock_priority high
begin transaction
while 1 = 1
begin
begin try
begin transaction
-- lock 1st record
update DeadlockTest
set Deadlock_Count = Deadlock_Count
from DeadlockTest
where Deadlock_Key = 1
waitfor delay ''00:00:10''
-- lock 2nd record (which will be locked when the target proc calls sp_simulatedeadlock)
update DeadlockTest
set Deadlock_Count = Deadlock_Count
from DeadlockTest
where Deadlock_Key = 2
rollback
end try
begin catch
print ''Error '' + convert(varchar(20), ERROR_NUMBER()) + '': '' + ERROR_MESSAGE()
goto cleanup
end catch
end
cleanup:
if @@trancount > 0
rollback
drop procedure sp_simulatedeadlock
drop table DeadlockTest

Answer 3

La forma más sencilla de reproducir en C # con paralelo, por ejemplo

 var List = ... (add some items with same ids)
Parallel.ForEach(List,
(item) =>
{
ReportsDataContext erdc = null;
try
{
using (TransactionScope scope = new TransactionScope())
{
erdc = new ReportsDataContext("....connection....");
var report = erdc.Report.Where(x => x.id == item.id).Select(x => x);
report.Count++
erdc.SubmitChanges();
scope.Complete();
}
if (erdc != null)
erdc.Dispose();
}
catch (Exception ex)
{
if (erdc != null)
erdc.Dispose();
ErrorLog.LogEx("multi thread victim", ex);
}

¿Más interés cómo prevenir ese error en una situación real de hilos cruzados?

Answer 4

Paul, gracias por tu pregunta y respuesta de seguimiento. Tu publicación me inspiró a unirme a por primera vez.

Tuve algunas dificultades para que su respuesta funcionara, y solo quiero compartir los pequeños cambios que hice para que funcione. Si le salva a alguien un día de su vida, entonces vale la pena. La clave es comenzar y revertir la transacción sp_simulatedeadlock dentro del propio procedimiento. No hice cambios a tu procedimiento mencionado en tu respuesta.

DECLARE @DeadlockCounter INT = NULL
SELECT @DeadlockCounter = 0
WHILE @DeadlockCounter < 10
BEGIN
BEGIN TRY
/* The procedure was leaving uncommitted transactions, I rollback the transaction in the catch block */
BEGIN tran simulate
Exec sp_simulatedeadlock
/* Code you want to deadlock */
SELECT @DeadlockCounter = 10
END TRY
BEGIN CATCH
Rollback tran simulate
PRINT ERROR_MESSAGE()
IF (ERROR_MESSAGE() LIKE ''%deadlock%'' OR ERROR_NUMBER() = 1205) AND @DeadlockCounter < 10
BEGIN
SELECT @DeadlockCounter +=1
PRINT @DeadlockCounter
IF @DeadlockCounter = 10
BEGIN
RAISERROR(''Deadlock limit exceeded or error raised'', 16, 10);
END
END
END CATCH
END

Answer 5

Puedes explotar un error que Microsoft parece no tener prisa en solucionar ejecutando

use tempdb
begin tran
go
CREATE TYPE dbo.IntIntSet AS TABLE(
Value0 Int NOT NULL,
Value1 Int NOT NULL
)
go
declare @myPK dbo.IntIntSet;
go
rollback

Este SQL causará un interbloqueo consigo mismo. Muchos más detalles en el blog de Aaron Bertand http://sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock