Это транзакция?

Дмитрий Исаев

MSSQL 2000 SP4.
INSERT INTO Table (pk, Name)
 SELECT @cod, 'abc'
 WHERE not exists (SELECT * FROM Table WHERE pk=@cod)
Это транзакция? Иначе говоря, может ли выполниться сначала SELECT, потом какой-нибудь левый процесс туда быстро что-то вставит, и только затем - INSERT?
13 ответов

Дмитрий Исаев

Иначе говоря, может ли выполниться сначала SELECT, потом какой-нибудь левый процесс туда быстро что-то вставит, и только затем - INSERT?
Может. Если ваш SELECT не заблокирует таблицу.


Дмитрий Исаев

Т.е. нужен TABLOCKX для внутреннего селекта? Явно прописывать begin tran - commit tran не обязательно?


Дмитрий Исаев

Т.е. нужен TABLOCKX для внутреннего селекта? Явно прописывать begin tran - commit tran не обязательно?
Обязательно. Иначе с чего select будет продолжать удерживать блокировку после своего окончания ?


Дмитрий Исаев

Ок, спасибо!


Дмитрий Исаев

Glory,по-моему tablockx - жестоко тут достаточно updlock и serializable.


Дмитрий Исаев

Дмитрий Исаев,только tablockx наверное необязательно, достаточно serializable.


Дмитрий Исаев

iljy, зачем updlock?


Дмитрий Исаев

iljy, Le Peaceа если кто-нибудь в другом коннекте будет делать то же самое, select тоже покажет, что такой записи нет,оба попытаются вставить, а вставит только один из-за UNIQUE CONSTRAINT?Второму вернётся сообщение о нарушении констрейнта?Это если монопольную блокировку не наложить.


Дмитрий Исаев

iljy, Le Peaceа если кто-нибудь в другом коннекте будет делать то же самое, select тоже покажет, что такой записи нет,оба попытаются вставить, а вставит только один из-за UNIQUE CONSTRAINT?Второму вернётся сообщение о нарушении констрейнта?Это если монопольную блокировку не наложить.
Вот как раз для этого и нужен updlock мы диапазон ключа захватываем на обновление, поэтому в другом коннекте даже select (такой же) будет ждать. Если сделать xlock - тогда select будет ждать даже если он запущен просто на уровне serislizable, без updlock или xlock.


Дмитрий Исаев

iljy,да, ляпнул не подумав :)


Дмитрий Исаев

iap,iljyВсё таки вопрос - что я делаю не так проделал эксперимент и не понял почему не устраивает SERIALIZABLE?Наверное не очень понял Ваших рассуждений, но
use tempdb
create table test (pk smallint not null primary key,name varchar(<b>50</b>))
go
/*******коннекшен 1************/
declare 
@cod smallint 
set @cod=<b>1000</b> 
begin tran
 insert into test WITH (SERIALIZABLE)
 SELECT @cod, 'abc' 
 WHERE not exists (SELECT * FROM test WHERE pk=@cod)
--->выполняем пока до этой строки, commit выполним потом 
commit tran

/*******коннекшен 2************/
---выполняем скрипт , но так как в первой коннекции не закрыта транзакция успешно висим,
---lеё окончания 
declare 
@cod smallint 
set @cod=<b>1000</b> 

insert into test WITH 
 SELECT @cod, 'abc' 
 WHERE not exists (SELECT * FROM test WHERE pk=@cod)

/*******коннекшен 3************/
---просто смотрим что висим 
SELECT resource_type, resource_description,
 resource_associated_entity_id, request_mode, request_status
FROM sys.dm_tran_locks where resource_database_id=<b>2</b>
PAGE	1:110 	72057594038648832	IS	GRANTPAGE	1:110 	72057594038648832	IX	GRANTOBJECT	 	277576027	IX	GRANTOBJECT	 	277576027	IX	GRANTKEY	(************) 	72057594038648832	X	GRANTKEY	(************) 	72057594038648832	S	WAIT
----далее возвращаемся к коннекшен 1 и выполняем 
--->
commit tran


/*соответственно в коннекшен 2 "блокировка" заканчивает работу
0 rows affected
*/
итого первая коннекция вставила запись, а вторая нет (т к она "ждала" как того и требует SERIALIZABLE - окончания первой транзакции и только потом начала выполнятьcя) , клинча по PK constraint нет и вроде бы всё устраивает. Вопросик всё таки - чем не устроил SERIALIZABLE, зачем updlock? Объясните плзСпасибо!


Дмитрий Исаев

rollCommit,обясняю.У вас идет несолько обращений к таблице - select и insert. Вы проверяете ситуацию, когда insert отработал и, соответственно, повесил на вставленную запись и диапазон ключа блокировку X. Да, тут уже все хорошо, но! У вас сначала работает select с проверкой условия, и только потом - insert. Так представьте, что у вас выполняется:1. SELECT - Connection 12. SELECT - Connection 2Оба селекта не вернут ничего - в таблице нет записи, а на диапазон ключа мы вешаем S-блокировки, которые совместимы.Соотвенственно not exists выполнится в обоих соединениях. А дальше будет интересно3. INSERT - Connetion 1Запись вставлена, повешена блокировка X.4. INSERT- Connection 2.Пытаемся повесить блокировку X - не можем, она уже есть. Ждем пока она снимется. Когда она снимается - пытаемся вставить запись и ловим исключение.UPDLOCK (или XLOCK) заставляет SELECT вешать не S, а U(или X)-блокировку на диапазон ключа, поэтому второй SELECT ставится в ожидание - U и U блокировки не совместимы. Блокировка снимается после выполнения INSERT в первом соединении, после чего второе получает вставленную запись, not exists не выполняется и до второго INSERT дело не доходит.


Дмитрий Исаев

iljy , спасибо !