问题描述:
Step 1:
建立一个自增列的table:s_VAid ,并给他建立一个索引
Create TRIGGER [dbo].[tg_s_VAid_Add] ON [dbo].[s_VAid]
FOR INSERT
AS
DELETE FROM dbo.s_VAid with(rowlock)
------------------------------------------------------------
Step 2:
打开两个查询窗口分别执行下面这段脚本:
DECLARE @count INT
declare @l_OldId int
SET @count=1
WHILE(@count<10000)
BEGIN
SET @count=@count+1
insert into dbo.s_VAid DEFAULT VALUES
SELECT @l_OldId = @@IDENTITY
END如果disable触发器,仍然打开两个查询窗口分别执行下面这段脚本,这样不会造成死锁
DECLARE @count INT
declare @l_OldId int
SET @count=1
WHILE(@count<10000)
BEGIN
SET @count=@count+1
insert into dbo.DEFAULT VALUES
SELECT @l_OldId = @@IDENTITY
delete from s_VAid
END以上现象,请帮忙分析的原因,越详细越好!
Step 1:
建立一个自增列的table:s_VAid ,并给他建立一个索引
Create TRIGGER [dbo].[tg_s_VAid_Add] ON [dbo].[s_VAid]
FOR INSERT
AS
DELETE FROM dbo.s_VAid with(rowlock)
------------------------------------------------------------
Step 2:
打开两个查询窗口分别执行下面这段脚本:
DECLARE @count INT
declare @l_OldId int
SET @count=1
WHILE(@count<10000)
BEGIN
SET @count=@count+1
insert into dbo.s_VAid DEFAULT VALUES
SELECT @l_OldId = @@IDENTITY
END如果disable触发器,仍然打开两个查询窗口分别执行下面这段脚本,这样不会造成死锁
DECLARE @count INT
declare @l_OldId int
SET @count=1
WHILE(@count<10000)
BEGIN
SET @count=@count+1
insert into dbo.DEFAULT VALUES
SELECT @l_OldId = @@IDENTITY
delete from s_VAid
END以上现象,请帮忙分析的原因,越详细越好!
问题描述:
Step 1:
建立一个自增列的table:s_VAid ,并给他建立一个索引
Create TRIGGER [dbo].[tg_s_VAid_Add] ON [dbo].[s_VAid]
FOR INSERT
AS
DELETE FROM dbo.s_VAid with(rowlock)
------------------------------------------------------------
Step 2:
打开两个查询窗口分别执行下面这段脚本:
DECLARE @count INT
declare @l_OldId int
SET @count=1
WHILE(@count<10000)
BEGIN
SET @count=@count+1
insert into dbo.s_VAid DEFAULT VALUES
SELECT @l_OldId = @@IDENTITY
END如果disable触发器,仍然打开两个查询窗口分别执行下面这段脚本,这样不会造成死锁
DECLARE @count INT
declare @l_OldId int
SET @count=1
WHILE(@count<10000)
BEGIN
SET @count=@count+1
insert into dbo.DEFAULT VALUES
SELECT @l_OldId = @@IDENTITY
delete from s_VAid
END
DECLARE @count INT
declare @l_OldId int
SET @count=1
WHILE(@count<10000)
BEGIN
SET @count=@count+1 begin tran
insert into dbo.s_vaid DEFAULT VALUES
delete from s_VAid
commit tran SELECT @l_OldId = @@IDENTITY
END
在你的事务insert之后,别的事务也insert,造成了本事务无法删除,形成死锁。请记得结贴
DECLARE @count INT
declare @l_OldId int
SET @count=1
WHILE(@count<10000)
BEGIN
SET @count=@count+1 --begin tran
insert into dbo.s_VAid DEFAULT VALUES
delete from s_VAid
--commit tran SELECT @l_OldId = @@IDENTITY
END
加上begin tran.....commit tran之后,insert之后,SQL还要在该行上保留独占锁,直到本事务提交。欲知详情,请自行研究联机丛书。赶紧的,速度结贴!
1.insert into dbo.s_VAid DEFAULT VALUES
2.insert into dbo.s_VAid DEFAULT VALUES
3.delete from s_VAid
3.delete from s_VAid
commit tran commit tran
1.事务一中语句1执行后,SQL在该行上放了个独占锁。
2.事务二中语句2执行后,SQL在该行(与1不同的行)上放了个独占锁。1与2无冲突。
3.事务一中语句3执行时,需要取得全表的独占锁,与2冲突,需要等事务二释放。
4.事务二中语句4执行时,需要取得全表的独占锁,与1冲突,需要等事务一释放。
3与4互相等待,形成死锁。