分享程序员开发的那些事...
更新时间：2023-12-02 13:42:40
根据sp_who2和sp_lock输出，spid54长时间运行
交易。它在Key上有一个X锁定（5301214e6d62）。 Spid 52正试图将
锁定在密钥上并被阻止。读取uncommited隔离级别
并不会阻止spid 54对密钥进行X锁定，因为X锁定可能是由于修改而获得的
（插入/删除/更新），对于
，事务不能跳过锁定。我看到阻止来自阻塞的唯一方法就是将读取uncommited隔离级别应用于spid
52（而不是spid 54），所以spid 52可以读取脏的未提交数据修改
by spid 54.不确定你的应用程序的语义是否允许它。
-
Gang他是b $ b软件设计工程师
Microsoft SQL Server存储引擎
该帖子按原样提供。没有保证，也没有赋予任何权利。
" pb648174" <去**** @ webpaul.net>在消息中写道
news：11 ********************** @ g43g2000cwa.googlegr oups.com ...
According to the sp_who2 and sp_lock output, spid54 has the long running
transaction. It has an X lock on Key (5301214e6d62). Spid 52 is trying to
get S lock on the key and got blocked. The read uncommited isolation level
doesn''t prevent spid 54 from getting X lock on the key as the X lock is
likely obtained as a result of a modification(insert/delete/update), for
which the transaction can''t skip locking. The only way I see to prevent
spid52 from blocking is to apply read uncommited isolation level to spid
52(rather than spid 54), so spid 52 can read dirty uncommitted data modified
by spid 54. Not sure whether your app''s semantics allow it though.
--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pb648174" <go****@webpaul.net> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
我有一个很长的事务，它运行在其他用户需要用于现有数据的同一个数据库上。我不在乎他们是否在交易完成之前看到了交易中的数据并且只使用了
交易，因为如果发生任何错误，我需要一种方法将其回滚
交易。不幸的是，在长时间运行的事务中受影响的所有表都被完全锁定，并且在运行时没有其他人可以访问任何受影响的表。我使用的是
事务隔离级别的read uncommitted，这对我有限的隔离级别的理解是最不严格的。我该怎么做才能防止这种情况发生？
下面是sp_who2和sp_lock的输出，当进程正在运行而另一个进程被它阻止时。
SPID状态登录
HostName BlkBy DBName命令CPUTime
DiskIO LastBatch ProgramName SPID
----- --------------- ---------------
------------------------------- ----------------- ---------- -----
------------ - -------------- ------- ------ --------------
----- ----------------------- -----
1背景sa
。 。 NULL LAZY WRITER 0 0
06/09 15:42:52 1
2睡觉sa
。 NULL LOG WRITER 10 0
06/09 15:42:52 2
3背景sa
。 。大师SIGNAL HANDLER 0 0
06/09 15:42:52 3
4背景sa
。 。 NULL LOCK MONITOR 0 0
06/09 15:42:52 4
5背景sa
。 。掌握任务经理0 5
06/09 15:42:52 5
6背景sa
。 。大师任务经理0 0
06/09 15:42:52 6
7睡觉sa
。 NULL CHECKPOINT SLEEP 0 12
06/09 15:42:52 7
8背景sa
。 。大师任务经理0 2
06/09 15:42:52 8
9背景sa
。 。大师任务经理0 0
06/09 15:42:52 9
10背景sa
。 。大师任务经理0 0
06/09 15:42:52 10
11背景sa
。 。大师任务经理0 1
06/09 15:42:52 11
12背景sa
。 。掌握任务经理0 0
06/09 15:42:52 12
51睡觉SUPERPABLO \Administrator
SUPERPABLO。 PM AWAITING COMMAND 1813
307 06/09 16:10:34 .Net SqlClient数据提供者51
52睡觉SUPERPABLO \Administrator
SUPERPABLO 54 PM选择30 5
06/09 16:10:16 .Net SqlClient数据提供程序52
53 RUNNABLE SUPERPABLO \Administrator
SUPERPABLO。掌握SELECT 0 3
06/09 16:09:44 SQL Profiler 53
54 RUNNABLE SUPERPABLO \Administrator
SUPERPABLO。 PM UPDATE 10095
206 06/09 16:10:02 .Net SqlClient数据提供者54
56 RUNNABLE SUPERPABLO \Administrator
SUPERPABLO。 PM SELECT INTO 151 27
06/09 16:10:33 SQL查询分析器56
（17行受影响）
spid dbid ObjId IndId Type资源模式状态
------ ------ ----------- ------ ---- ---------- ------ -------- ------
51 5 0 0 DB S GRANT
52 5 0 0 DB S GRANT
52 5 1117963059 4 PAG 1：7401 IS GRANT
52 5 1117963059 4 KEY（5301214e6d62）S WAIT
52 5 1117963059 0 TAB授权
54 5 1117963059 0 TAB IX GRANT
54 5 1852025829 0 TAB IX GRANT
54 5 1181963287 3 PAG 1：9017 IX GRANT
54 5 1117963059 4 KEY（5301934930a4）X GRANT
54 5 111796 3059 3 KEY（530187fc93f3）X GRANT
54 5 1117963059 4 KEY（530154df71eb）X GRANT
54 5 0 0 DB [BULK-OP-LOG] NULL GRANT
54 5 0 0 FIL 2 ：0：d U授予
54 5 1117963059 2 KEY（1d0096c50a7d）X GRANT
54 5 1117963059 2 KEY（1b004a9a6158）X GRANT
54 5 1117963059 2 KEY（1800a435d44a）X GRANT
54 5 1181963287 6 PAG 1：8745 IX授予
54 5 1181963287 4 PAG 1：8923 IX授予
54 5 1181963287 2 PAG 1：8937 IX授予
54 5 1117963059 4 KEY（ 5301112b0696）X GRANT
54 5 0 0 PAG 1：10889 IX GRANT
54 5 1181963287 5 PAG 1：8859 IX GRANT
54 5 1181963287 6 PAG 1：10888 IX GRANT
54 5 0 0 PAG 1：10891 IX GRANT
54 5 0 0 PAG 1：10893 IX GRANT
54 5 0 0 PAG 1：10892 IX GRANT
54 5 0 0 PAG 1：10894 IX GRANT
54 5 0 0 PAG 1：10882 IX GRANT
54 5 1117963059 3 KEY（530135fbce35）X GRANT
54 5 1117963059 0 RID 1 ：7387：57 X授权
54 5 1117963059 0 RID 1：7387：59 X授权
54 5 1117963059 0 RID 1：7387：61 X授权
54 5 1117963059 3 KEY（5301406ad2bc） X GRANT
54 5 1117963059 4 PAG 1：7401 IX GRANT
54 5 0 0 PAG 1：7387 IX授予
54 5 1117963059 2 PAG 1：7389 IX GRANT
54 5 1117963059 3 PAG 1：7391 IX GRANT
54 5 1117963059 0 RID 1：7387：10 X GRANT
54 5 1117963059 0 RID 1：7387：56 X GRANT
54 5 1117963059 0 RID 1：7387：58 X GRANT
54 5 1117963059 0 RID 1：7387：60 X GRANT
54 5 1117963059 3 KEY（530144afbed8）X GRANT
54 5 1117963059 4 KEY（530115ee6af2）X GRANT
54 5 1117963059 3 KEY（5301c6cd88ea）X GRANT
54 5 1149963173 0 TAB IX GRANT
54 5 1181963287 0 TAB X GRANT
54 5 1117963059 4 KEY（5301d2782bbd）X GRANT
54 5 1117963059 3 KEY（5301015bc9a5）X GRANT
54 5 0 0 DB S授权
54 5 0 0 DB [BULK-OP-DB] NULL授予
54 5 1117963059 4 KEY（5301501a1d8f）X GRANT
54 5 1117963059 2 KEY（1c00f3a2b6c5）X GRANT
54 5 1117963059 2 KEY（1a002ffddde0）X GRANT
54 5 0 0 PAG 1：7411 IX授权
54 5 1117963059 2 KEY（1900c15268f2）X GRANT
54 5 0 0 PAG 1： 10840 IX GRANT
54 5 1181963287 4 PAG 1：10841 IX GRANT
54 5 0 0 PAG 1：10842 IX GRANT
54 5 1117963059 3 KEY（5301059ea5c1）X GRANT
54 5 0 0 PAG 1：10820 IX GRANT
54 5 1181963287 4 PAG 1：10821 IX GRANT
54 5 1181963287 5 PAG 1：10874 IX GRAN T
54 5 1181963287 5 PAG 1：10876 IX GRANT
54 5 0 0 PAG 1：10877 IX GRANT
54 5 1181963287 5 PAG 1：10878 IX GRANT
54 5 0 0 PAG 1：10849 IX授予
54 5 0 0 PAG 1：10850 IX授予
54 5 1117963059 2 KEY（1700f225b712）X GRANT
54 5 1117963059 4 KEY（5301214e6d62）X GRANT
56 5 0 0 DB S GRANT
56 1 85575343 0 TAB IS GRANT
I have a very long transaction that runs on the same database that
other users need to use for existing data. I don''t care if they see
data from the transaction before it is done and am only using the
transaction because I need a way to roll it back if any errors happen
during the transaction. Unfortunately all tables affected in the long
running transaction are completely locked and nobody else can access
any of the affected tables while it is running. I am using the
transaction isolation level of read uncommitted, which from my limited
understanding of isolation levels is the least strict. What can I do to
prevent this from happening?
Below is the output from sp_who2 and sp_lock while the process is
running and another process is being blocked by it.
SPID Status Login
HostName BlkBy DBName Command CPUTime
DiskIO LastBatch ProgramName SPID
----- ------------------------------
------------------------------------------------ ---------- -----
------------ ---------------- ------- ------ --------------
---------------------------- -----
1 BACKGROUND sa
. . NULL LAZY WRITER 0 0
06/09 15:42:52 1
2 sleeping sa
. . NULL LOG WRITER 10 0
06/09 15:42:52 2
3 BACKGROUND sa
. . master SIGNAL HANDLER 0 0
06/09 15:42:52 3
4 BACKGROUND sa
. . NULL LOCK MONITOR 0 0
06/09 15:42:52 4
5 BACKGROUND sa
. . master TASK MANAGER 0 5
06/09 15:42:52 5
6 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 6
7 sleeping sa
. . NULL CHECKPOINT SLEEP 0 12
06/09 15:42:52 7
8 BACKGROUND sa
. . master TASK MANAGER 0 2
06/09 15:42:52 8
9 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 9
10 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 10
11 BACKGROUND sa
. . master TASK MANAGER 0 1
06/09 15:42:52 11
12 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 12
51 sleeping SUPERPABLO\Administrator
SUPERPABLO . PM AWAITING COMMAND 1813
307 06/09 16:10:34 .Net SqlClient Data Provider 51
52 sleeping SUPERPABLO\Administrator
SUPERPABLO 54 PM SELECT 30 5
06/09 16:10:16 .Net SqlClient Data Provider 52
53 RUNNABLE SUPERPABLO\Administrator
SUPERPABLO . master SELECT 0 3
06/09 16:09:44 SQL Profiler 53
54 RUNNABLE SUPERPABLO\Administrator
SUPERPABLO . PM UPDATE 10095
206 06/09 16:10:02 .Net SqlClient Data Provider 54
56 RUNNABLE SUPERPABLO\Administrator
SUPERPABLO . PM SELECT INTO 151 27
06/09 16:10:33 SQL Query Analyzer 56
(17 row(s) affected)
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 5 0 0 DB S GRANT
52 5 0 0 DB S GRANT
52 5 1117963059 4 PAG 1:7401 IS GRANT
52 5 1117963059 4 KEY (5301214e6d62) S WAIT
52 5 1117963059 0 TAB IS GRANT
54 5 1117963059 0 TAB IX GRANT
54 5 1852025829 0 TAB IX GRANT
54 5 1181963287 3 PAG 1:9017 IX GRANT
54 5 1117963059 4 KEY (5301934930a4) X GRANT
54 5 1117963059 3 KEY (530187fc93f3) X GRANT
54 5 1117963059 4 KEY (530154df71eb) X GRANT
54 5 0 0 DB [BULK-OP-LOG] NULL GRANT
54 5 0 0 FIL 2:0:d U GRANT
54 5 1117963059 2 KEY (1d0096c50a7d) X GRANT
54 5 1117963059 2 KEY (1b004a9a6158) X GRANT
54 5 1117963059 2 KEY (1800a435d44a) X GRANT
54 5 1181963287 6 PAG 1:8745 IX GRANT
54 5 1181963287 4 PAG 1:8923 IX GRANT
54 5 1181963287 2 PAG 1:8937 IX GRANT
54 5 1117963059 4 KEY (5301112b0696) X GRANT
54 5 0 0 PAG 1:10889 IX GRANT
54 5 1181963287 5 PAG 1:8859 IX GRANT
54 5 1181963287 6 PAG 1:10888 IX GRANT
54 5 0 0 PAG 1:10891 IX GRANT
54 5 0 0 PAG 1:10893 IX GRANT
54 5 0 0 PAG 1:10892 IX GRANT
54 5 0 0 PAG 1:10894 IX GRANT
54 5 0 0 PAG 1:10882 IX GRANT
54 5 1117963059 3 KEY (530135fbce35) X GRANT
54 5 1117963059 0 RID 1:7387:57 X GRANT
54 5 1117963059 0 RID 1:7387:59 X GRANT
54 5 1117963059 0 RID 1:7387:61 X GRANT
54 5 1117963059 3 KEY (5301406ad2bc) X GRANT
54 5 1117963059 4 PAG 1:7401 IX GRANT
54 5 0 0 PAG 1:7387 IX GRANT
54 5 1117963059 2 PAG 1:7389 IX GRANT
54 5 1117963059 3 PAG 1:7391 IX GRANT
54 5 1117963059 0 RID 1:7387:10 X GRANT
54 5 1117963059 0 RID 1:7387:56 X GRANT
54 5 1117963059 0 RID 1:7387:58 X GRANT
54 5 1117963059 0 RID 1:7387:60 X GRANT
54 5 1117963059 3 KEY (530144afbed8) X GRANT
54 5 1117963059 4 KEY (530115ee6af2) X GRANT
54 5 1117963059 3 KEY (5301c6cd88ea) X GRANT
54 5 1149963173 0 TAB IX GRANT
54 5 1181963287 0 TAB X GRANT
54 5 1117963059 4 KEY (5301d2782bbd) X GRANT
54 5 1117963059 3 KEY (5301015bc9a5) X GRANT
54 5 0 0 DB S GRANT
54 5 0 0 DB [BULK-OP-DB] NULL GRANT
54 5 1117963059 4 KEY (5301501a1d8f) X GRANT
54 5 1117963059 2 KEY (1c00f3a2b6c5) X GRANT
54 5 1117963059 2 KEY (1a002ffddde0) X GRANT
54 5 0 0 PAG 1:7411 IX GRANT
54 5 1117963059 2 KEY (1900c15268f2) X GRANT
54 5 0 0 PAG 1:10840 IX GRANT
54 5 1181963287 4 PAG 1:10841 IX GRANT
54 5 0 0 PAG 1:10842 IX GRANT
54 5 1117963059 3 KEY (5301059ea5c1) X GRANT
54 5 0 0 PAG 1:10820 IX GRANT
54 5 1181963287 4 PAG 1:10821 IX GRANT
54 5 1181963287 5 PAG 1:10874 IX GRANT
54 5 1181963287 5 PAG 1:10876 IX GRANT
54 5 0 0 PAG 1:10877 IX GRANT
54 5 1181963287 5 PAG 1:10878 IX GRANT
54 5 0 0 PAG 1:10849 IX GRANT
54 5 0 0 PAG 1:10850 IX GRANT
54 5 1117963059 2 KEY (1700f225b712) X GRANT
54 5 1117963059 4 KEY (5301214e6d62) X GRANT
56 5 0 0 DB S GRANT
56 1 85575343 0 TAB IS GRANT
事情是，长时间运行的交易是唯一运行的
交易。所有其他进程只是运行非
事务查询。
The thing is though, the long running transaction is the only
transaction running. All the other processes are just running non
transactional queries.
我已经通过插入块来欺骗大型INSERT语句他们在交易中批量处理
，WAITFOR DELAY为几秒钟。
这使得个别交易更短，并允许其他交易
语句潜入并完成一些工作。我已经发现，插入5批不断增加的百分比尺寸（20，
25,30,50,100）通常最终会插入接近相等的批量大小。
此方法的伪代码如下所示：
BEGIN TRANSACTION
INSERT INTO holdingTable
SELECT PrimaryKey
FROM Table
- 10000条记录的前2000条记录
INSERT INTO DestinationTable
SELECT TOP 20 PERCENT Cols
来自SourceTable JOIN HoldingTable ON a = b
ORDER BY HoldingTable PrimaryKey
>
删除
来自HoldingTable
WHERE PrimaryKey IN（选择前20％主键
来自按键，按主键排序）
---暂停10秒
等待延迟'00：00：10''
---前2000记录其余8000条记录
INSERT INTO DestinationTable
SELECT TOP 25 PERCENT Cols
来自SourceTable JOIN HoldingTable ON a = b
ORDER BY HoldingTable PrimaryKey
DELETE
FROM HoldingTable
WHERE PrimaryKey IN（SELECT TOP 25 PERCENT PrimaryKey
FROM HoldingTable ORDER BY Primary KEY）
WAITFOR DELAY' '00：00：10''
.....
---剩余记录
INSERT INTO DestinationTable
SELECT TOP 100 PERCENT Cols
来自SourceTable JOIN HoldingTable ON a = b
ORDER BY HoldingTable PrimaryKey
drop HoldingTable - 假设它是临时表或表变量
COMMIT TRANSACTION
类似的概念应该适用于UPDATES。
可能还有其他解决方案;这对我有用。
I''ve cheated with large INSERT statements by inserting blocks of them
in a batch inside a transaction, with a WAITFOR DELAY of a few seconds.
This makes the individual transactions shorter, and allows the other
statements to sneak in and get some work done while doing it. I''ve
found that inserting 5 batches of of increasing percentage sizes (20,
25, 30, 50, 100) usually ends up inserting near equivelent batch sizes.
The psuedo-code for this method would be something like the following:
BEGIN TRANSACTION
INSERT INTO holdingTable
SELECT PrimaryKey
FROM Table
-- first 2000 records of 10000 records
INSERT INTO DestinationTable
SELECT TOP 20 PERCENT Cols
FROM SourceTable JOIN HoldingTable ON a=b
ORDER BY HoldingTable PrimaryKey
DELETE
FROM HoldingTable
WHERE PrimaryKey IN (SELECT TOP 20 PERCENT PrimaryKey
FROM HoldingTable ORDER BY Primary KEY)
---pause for 10 seconds
WAITFOR DELAY ''00:00:10''
---first 2000 records of remaining 8000 records
INSERT INTO DestinationTable
SELECT TOP 25 PERCENT Cols
FROM SourceTable JOIN HoldingTable ON a=b
ORDER BY HoldingTable PrimaryKey
DELETE
FROM HoldingTable
WHERE PrimaryKey IN (SELECT TOP 25 PERCENT PrimaryKey
FROM HoldingTable ORDER BY Primary KEY)
WAITFOR DELAY ''00:00:10''
.....
---remaining records
INSERT INTO DestinationTable
SELECT TOP 100 PERCENT Cols
FROM SourceTable JOIN HoldingTable ON a=b
ORDER BY HoldingTable PrimaryKey
drop HoldingTable --assumes it''s a temp table or table variable
COMMIT TRANSACTION
A similar concept should work for UPDATES.
There may be other solutions out there; this works for me.