insert exclusive locking

I have thought about the following SQL statements:

INSERT INTO A(a1, a2)
SELECT b1, udf_SomeFunc(b1)
FROM B

Where udf_SomeFunc makes a select on table A. As I understand, first, a shared lock is set on A (I am talking just about table A now), then, after this lock is released, an exclusive lock is obtained to insert the data. The question is: is it possible, that another transaction will get the exclusive lock on table A, just before the current transaction takes its exclusive lok on A?

---

Food for thought

create table test(id int)

insert test values(1)
GO

Now in one window run this

begin tran


insert into test
select * from test with (holdlock, updlock)
waitfor delay '00:00:30'
commit

while that is running open another connection and do this

begin tran

insert into test
select * from test with (holdlock, updlock)
commit

as you can see the second insert doesn't happen until the first transaction is complete

now take out the locking hints and observer the difference

链接地址: http://www.djcxy.com/p/32876.html

上一篇: 何时/什么锁在READ COMMITTED隔离级别保持/释放

下一篇: 插入独占锁定