需要读取数据的其他用户需要长时间运行的事务 [英] long running transactions w/ other users needing to read data

查看：65
本文介绍了需要读取数据的其他用户需要长时间运行的事务的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个非常长的事务，它运行在同一个数据库上，而其他用户需要用于现有数据。我不在乎他们是否在交易完成之前看到了来自交易的
数据并且我只使用了

交易，因为我需要一种方法将其回滚（如果有的话）在交易过程中发生错误

。不幸的是，在长期

运行事务中受影响的所有表都被完全锁定，并且在运行时，没有其他人可以访问任何受影响的表。我使用的是

事务隔离级别的读取未提交，这是从我有限的

了解隔离级别是最不严格的。我该怎么办？
可以防止这种情况发生？


下面是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 \管理员

SUPERPABLO。 PM AWAITING COMMAND 1813

307 06/09 16:10:34 .Net SqlClient数据提供者51

52睡觉SUPERPABLO \管理员

SUPERPABLO 54 PM SELECT 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类型资源模式状态

------ ------ ----------- ---- - ---- ---------------- -------- ------

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等待

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授予

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授予

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授予

54 5 1181963287 5 PAG 1：8859 IX授予

54 5 1181963287 6 PAG 1：10888 IX授予

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授予

54 5 0 0 PAG 1：10882 IX授予

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 GRANT

54 5 1117963059 3 KEY（5301406ad2bc）X GRANT

54 5 1117963059 4 PAG 1：7401 IX授予

54 5 0 0 PAG 1：7387 IX授予

54 5 1117963059 2 PAG 1：7389 IX授予

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

5 5 1117963059 3 KEY（5301059ea5c1）X GRANT

54 5 0 0 PAG 1：10820 IX GRANT

54 5 1181963287 4 PAG 1：10821 IX授予

54 5 1181963287 5 PAG 1：10874 IX授予

54 5 1181963287 5 PAG 1：10876 IX授予

54 5 0 0 PAG 1：10877 IX授予

54 5 1181963287 5 PAG 1：10878 IX授予

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授权

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

推荐答案

根据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.


这篇关于需要读取数据的其他用户需要长时间运行的事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆