Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Deadlock  [new]
maxim Ts
Member

Откуда:
Сообщений: 57
Помогите разобраться с блокировкой
Есть простейшая таблица

CREATE TABLE [g].[HUB_EventProtocol](
	[ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

Кроме одного автоинкрементного поля в табличке ничего нет. Используется просто как генератор ID для основной таблицы HUB_EventProtocol

Есть хранимая процедура, кот возвращает ID новой записи
ALTER procedure [DV].[usp_Generator] @generator varchar (100) as
begin
	-- set nocount on added to prevent extra result sets from
	-- interfering with select statements.
	set nocount on;

	exec ('insert into g.' + @generator + ' default values;');
	exec ('delete from g.' + @generator + ' where ID = @@IDENTITY;');
	return @@IDENTITY;
end;

При одновременном доступе к процедуре, получаю ошибку
Transaction (Process ID 69) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Воспроизвел ошибку без процедуры, одновреиенно запустив код
begin tran t1

	Declare @counter int

	Set @counter = 1

	While @counter < 1000

	Begin
		begin tran t2

			 exec ('insert into g.' + 'HUB_EventProtocol' + ' default values;');
			 exec ('delete from g.' + 'HUB_EventProtocol' + ' where ID = @@IDENTITY;');
			 
	    commit tran t2

		Set @counter = @counter + 1

	End


commit tran t1

Подскажите, как исправить ситуацию, чтобы не было взаимной блокировки
16 ноя 11, 16:16    [11607476]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Приведите граф дедлока.
16 ноя 11, 16:23    [11607556]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
aleks2
Guest
maxim Ts
Подскажите, как исправить ситуацию, чтобы не было взаимной блокировки

1. Дык не делай DELETE. И фсе будет в шоколаде.

2.
CREATE TABLE [g].[HUB_EventProtocol](
	[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
) ON [PRIMARY]


ЗЫ. Если уж чистить невыносимо хочется - делай "from time to time" али джобом.
16 ноя 11, 16:53    [11607833]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
maxim Ts
Member

Откуда:
Сообщений: 57
Граф
2011-11-16 17:06:49.56 spid69 DBCC TRACEON 1204, server process ID (SPID) 69. This is an informational message only; no user action is required.
2011-11-16 17:06:49.56 spid69 DBCC TRACEON 3605, server process ID (SPID) 69. This is an informational message only; no user action is required.
2011-11-16 17:07:01.73 spid5s Deadlock encountered .... Printing deadlock information
2011-11-16 17:07:01.73 spid5s Wait-for graph
2011-11-16 17:07:01.73 spid5s
2011-11-16 17:07:01.73 spid5s Node:1

2011-11-16 17:07:01.73 spid5s RID: 8:1:24092:0 CleanCnt:3 Mode:X Flags: 0x2
2011-11-16 17:07:01.73 spid5s Grant List 2:
2011-11-16 17:07:01.73 spid5s Owner:0x0000000003EE0500 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:75 ECID:0 XactLockInfo: 0x00000000F21D5488
2011-11-16 17:07:01.73 spid5s SPID: 75 ECID: 0 Statement Type: DELETE Line #: 1
2011-11-16 17:07:01.73 spid5s Input Buf: Language Event: begin tran t1

Declare @counter int

Set @counter = 1

While @counter < 1000

Begin
begin tran t2

exec ('insert into g.' + 'HUB_EventProtocol' + ' default values;');
exec ('delete from g.' + 'HUB_EventProtocol' + ' where ID = @@
2011-11-16 17:07:01.73 spid5s Requested By:
2011-11-16 17:07:01.73 spid5s ResType:LockOwner Stype:'OR'Xdes:0x00000000EC54C370 Mode: U SPID:69 BatchID:0 ECID:0 TaskProxy:(0x00000000F1348598) Value:0x4b87f00 Cost:(0/12736)
2011-11-16 17:07:01.73 spid5s
2011-11-16 17:07:01.73 spid5s Node:2

2011-11-16 17:07:01.73 spid5s RID: 8:1:651:0 CleanCnt:2 Mode:X Flags: 0x2
2011-11-16 17:07:01.73 spid5s Grant List 0:
2011-11-16 17:07:01.73 spid5s Owner:0x0000000003F20840 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:69 ECID:0 XactLockInfo: 0x00000000EC54C3A8
2011-11-16 17:07:01.73 spid5s SPID: 69 ECID: 0 Statement Type: DELETE Line #: 1
2011-11-16 17:07:01.73 spid5s Input Buf: Language Event: begin tran t1

Declare @counter int

Set @counter = 1

While @counter < 1000

Begin
begin tran t2

exec ('insert into g.' + 'HUB_EventProtocol' + ' default values;');
exec ('delete from g.' + 'HUB_EventProtocol' + ' where ID = @@
2011-11-16 17:07:01.73 spid5s Requested By:
2011-11-16 17:07:01.73 spid5s ResType:LockOwner Stype:'OR'Xdes:0x00000000F21D5450 Mode: U SPID:75 BatchID:0 ECID:0 TaskProxy:(0x00000000F213A598) Value:0x3ee05c0 Cost:(0/192)
2011-11-16 17:07:01.73 spid5s
2011-11-16 17:07:01.73 spid5s Victim Resource Owner:
2011-11-16 17:07:01.73 spid5s ResType:LockOwner Stype:'OR'Xdes:0x00000000F21D5450 Mode: U SPID:75 BatchID:0 ECID:0 TaskProxy:(0x00000000F213A598) Value:0x3ee05c0 Cost:(0/192)
2011-11-16 17:07:10.56 spid53 SQL Trace ID 2 was started by login "KRISTA2\pogodin".
16 ноя 11, 17:08    [11607954]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
VNVor
Member

Откуда: Ярославская обл.
Сообщений: 101
Есть предложение блокировать таблицу целиком при выполнении первого insert-а с помощью хинта with(tablock)?
Тогда параллельная сессия подождет, и не породит deadlock при delete.
ALTER procedure [DV].[usp_Generator] @generator varchar (100) as
begin
	-- set nocount on added to prevent extra result sets from
	-- interfering with select statements.
	set nocount on;

	exec ('insert into g.' + @generator + ' with(tablock) default values;');
	exec ('delete from g.' + @generator + ' where ID = @@IDENTITY;');
	return @@IDENTITY;
end;

Противопоказания будут?
17 ноя 11, 09:43    [11610447]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Поднимите TIL до SERIALIZABLE.
17 ноя 11, 09:48    [11610485]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
maxim Ts
Member

Откуда:
Сообщений: 57
VNVor,
Спасибо, вариант работает
17 ноя 11, 10:16    [11610648]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
maxim Ts
Member

Откуда:
Сообщений: 57
pkarklin,
Спасибо, так тоже отработало
17 ноя 11, 10:17    [11610652]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить