Reply to Re: long running transactions w/ other users needing to read data

Your name:

Reply:


Posted by Gang He [MSFT] on 06/10/05 00:49

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" <google@webpaul.net> wrote in message
news:1118351890.044392.321950@g43g2000cwa.googlegroups.com...
> 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
>

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация