此sql在事务1进行操作时锁定test表,事务等待事务1结束后执行
事务1:
CREATE TABLE test
(filed1 NVARCHAR(128),filed2 NVARCHAR(128),filed3 NVARCHAR(128),
filed4 NVARCHAR(128),filed14 NVARCHAR(128),
filed5 NVARCHAR(128),filed15 NVARCHAR(128),
filed6 NVARCHAR(128),filed16 NVARCHAR(128),
filed7 NVARCHAR(128),filed17 NVARCHAR(128),
filed8 NVARCHAR(128),filed18 NVARCHAR(128),
filed9 NVARCHAR(128),filed19 NVARCHAR(128),
filed0 NVARCHAR(128),filed20 NVARCHAR(128),
filed11 NVARCHAR(128),filed21 NVARCHAR(128),
filed12 NVARCHAR(128),filed22 NVARCHAR(128),
filed13 NVARCHAR(128),filed23 NVARCHAR(128),
)
BEGIN TRAN
DELETE FROM test WITH (HOLDLOCK)
DECLARE @i INT
SET @i=1
WHILE @i<160
BEGIN
SET @i=@i+1
INSERT INTO test VALUES('test1','test1','test1','test1','test1','test1','test1','test1','test1','test1','test1','test1','test1'
,'test1','test1','test1','test1','test1','test1','test1','test1','test1','test1')
END
BEGIN
WAITFOR DELAY '00:00:05'
end
COMMIT
事务2:
BEGIN TRAN
SELECT * FROM test WITH (HOLDLOCK)
COMMIT
查看事务进程:
--查询被锁表 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'
--关闭进程 declare @spid int Set @spid = xxx --锁表进程 declare @sql varchar(1000) set @sql='kill '+cast(@spid as varchar) exec(@sql)