473,718 Members | 1,907 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

long running transactions w/ other users needing to read data

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\Admi nistrator
SUPERPABLO . PM AWAITING COMMAND 1813
307 06/09 16:10:34 .Net SqlClient Data Provider 51
52 sleeping SUPERPABLO\Admi nistrator
SUPERPABLO 54 PM SELECT 30 5
06/09 16:10:16 .Net SqlClient Data Provider 52
53 RUNNABLE SUPERPABLO\Admi nistrator
SUPERPABLO . master SELECT 0 3
06/09 16:09:44 SQL Profiler 53
54 RUNNABLE SUPERPABLO\Admi nistrator
SUPERPABLO . PM UPDATE 10095
206 06/09 16:10:02 .Net SqlClient Data Provider 54
56 RUNNABLE SUPERPABLO\Admi nistrator
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

Jul 23 '05 #1
29 5810
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(in sert/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.goo glegroups.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\Admi nistrator
SUPERPABLO . PM AWAITING COMMAND 1813
307 06/09 16:10:34 .Net SqlClient Data Provider 51
52 sleeping SUPERPABLO\Admi nistrator
SUPERPABLO 54 PM SELECT 30 5
06/09 16:10:16 .Net SqlClient Data Provider 52
53 RUNNABLE SUPERPABLO\Admi nistrator
SUPERPABLO . master SELECT 0 3
06/09 16:09:44 SQL Profiler 53
54 RUNNABLE SUPERPABLO\Admi nistrator
SUPERPABLO . PM UPDATE 10095
206 06/09 16:10:02 .Net SqlClient Data Provider 54
56 RUNNABLE SUPERPABLO\Admi nistrator
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

Jul 23 '05 #2
The thing is though, the long running transaction is the only
transaction running. All the other processes are just running non
transactional queries.

Jul 23 '05 #3
Stu
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 DestinationTabl e
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 DestinationTabl e
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 DestinationTabl e
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.

Jul 23 '05 #4
pb648174 wrote:
The thing is though, the long running transaction is the only
transaction running. All the other processes are just running non
transactional queries.


If you read the BOL section "SET IMPLICIT_TRANSA CTIONS", You should be
able to see that *every* statement is part of a transaction - there are
no "non transactional" queries.

What actually happens when you execute a query when there is no active
transaction is that a new transaction is started. When the query
completes, the behaviour is affected by the "IMPLICIT_TRANS ACTIONS"
settings. When it is OFF (the default), the transaction is
automatically committed, provided the query caused no error. When it is
ON, the transaction is kept open, and must be manually committed at a
later stage.

So, on to your problem. You *may* be able to fix it by issueing the
"SET TRANSACTION ISOLATION LEVEL" statement on each of the other
processes connections. by setting it to READ UNCOMMITTED, this will
affect every transaction which is started by this connection (including
these automatic transactions which your statements are
opening/committing).

This will mean that each of these processes may see data in any
imaginable (read: illegal) state. If you are sure that it is safe for
these processes to see (and process) such data, then this may be the
way to go.

HTH,

Damien

Jul 23 '05 #5
This large copy is happening from application code and involved 30 or
40 separate stored procedure being called. It is being done
asynchronously via a c# web app and there is a pause of 2 seconds
before every copy command. Unfortunately this two second pause doesn't
help because of the locking situation, it just makes everything block
for longer.

Isn't this a somewhat common scenario? What is the standard way around
this situation? Can somebody tell me why the entire table is being
locked instead of just the new records? I have no problem with new
records being locked, but I do not want it to lock the records it is
reading or the entire table.

Would disaster ensue if I submitted the following command?
SP_INDEXOPTION 'table_name', 'AllowTableLock s', FALSE
GO

Jul 23 '05 #6
pb648174 wrote:
The thing is though, the long running transaction is the only
transaction running. All the other processes are just running non
transactional queries.


If you read the BOL section "SET IMPLICIT_TRANSA CTIONS", You should be
able to see that *every* statement is part of a transaction - there are
no "non transactional" queries.

What actually happens when you execute a query when there is no active
transaction is that a new transaction is started. When the query
completes, the behaviour is affected by the "IMPLICIT_TRANS ACTIONS"
settings. When it is OFF (the default), the transaction is
automatically committed, provided the query caused no error. When it is
ON, the transaction is kept open, and must be manually committed at a
later stage.

So, on to your problem. You *may* be able to fix it by issueing the
"SET TRANSACTION ISOLATION LEVEL" statement on each of the other
processes connections. by setting it to READ UNCOMMITTED, this will
affect every transaction which is started by this connection (including
these automatic transactions which your statements are
opening/committing).

This will mean that each of these processes may see data in any
imaginable (read: illegal) state. If you are sure that it is safe for
these processes to see (and process) such data, then this may be the
way to go.

HTH,

Damien

Jul 23 '05 #7
This large copy is happening from application code and involved 30 or
40 separate stored procedure being called. It is being done
asynchronously via a c# web app and there is a pause of 2 seconds
before every copy command. Unfortunately this two second pause doesn't
help because of the locking situation, it just makes everything block
for longer.

Isn't this a somewhat common scenario? What is the standard way around
this situation? Can somebody tell me why the entire table is being
locked instead of just the new records? I have no problem with new
records being locked, but I do not want it to lock the records it is
reading or the entire table.

Would disaster ensue if I submitted the following command?
SP_INDEXOPTION 'table_name', 'AllowTableLock s', FALSE
GO

Jul 23 '05 #8
pb648174 (go****@webpaul .net) writes:
This large copy is happening from application code and involved 30 or
40 separate stored procedure being called. It is being done
asynchronously via a c# web app and there is a pause of 2 seconds
before every copy command. Unfortunately this two second pause doesn't
help because of the locking situation, it just makes everything block
for longer.

Isn't this a somewhat common scenario? What is the standard way around
this situation? Can somebody tell me why the entire table is being
locked instead of just the new records? I have no problem with new
records being locked, but I do not want it to lock the records it is
reading or the entire table.

Would disaster ensue if I submitted the following command?
SP_INDEXOPTION 'table_name', 'AllowTableLock s', FALSE


It would not have any effect.

The table you are inserting into is *not* completely locked. You posted
an output from sp_lock. According to this output, spid 54 holds an
exclusive table lock on table 1181963287, but that does not seem to
the main theatre for your problem. (You can use
"SELECT object_name(118 1963287)" to see which table this is.)

Instead, the main part of the show appears to be table 1117963059, and
spid 54 does not any table locks on this table. But it does hold locks
on all newly inserted rows, as well as all new inserted index nodes.
Process 52 is blocked by spid 54, and this is why:

52 5 1117963059 4 KEY (5301214e6d62) S WAIT

Spid 52 is trying to get a shared lock on an index key, but is blocked.

Assume that the query spid 52 has submitted is "show how many items of
widget X we sold last week", and the optimizer decides to use the
non-clustered index over widget_id to access the sales numbers. And
among the new rows you insert, there are rows with widget_id in question.
When spid 52 tries to access those index nodes, it will be blocked.

So while you don't get a table locked, it is not as simple that other
processes can just read the existing data, and don't bother about the
new data.

The best way would be look into how to shorten the transaction length.
It sounds as if row are being inserted one-by-one, in which case there
are lots of possibilities for improvements.

Another possibility is insert the data into a staging table, and the
insert with one big INSERT statement at the end. But if that is many
rows, that could still block for considerable time.

A further development is to use partitioned views. Here, too, you copy
the data into an empty table, that no other process sees. The processes
that reads data, access the view, not the table. Once the table has been
loaded, you change the view definition to include the new table.

Then you can of course, use READ UNCOMMITTED for the readers, but then
may get some funky results that is not consistent. (READ UNCOMMITTED
on the writer has no effect.) A better alternative may be READPAST,
in which cases locked rows are simply skipped. But this is only a locking
hint, and is not settable as a transaction level.

In SQL 2005, you would probably use the new SNAPSHOT isolattion level,
in which case the readers would see the data as it was before the long
transaction started.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9
pb648174 (go****@webpaul .net) writes:
This large copy is happening from application code and involved 30 or
40 separate stored procedure being called. It is being done
asynchronously via a c# web app and there is a pause of 2 seconds
before every copy command. Unfortunately this two second pause doesn't
help because of the locking situation, it just makes everything block
for longer.

Isn't this a somewhat common scenario? What is the standard way around
this situation? Can somebody tell me why the entire table is being
locked instead of just the new records? I have no problem with new
records being locked, but I do not want it to lock the records it is
reading or the entire table.

Would disaster ensue if I submitted the following command?
SP_INDEXOPTION 'table_name', 'AllowTableLock s', FALSE


It would not have any effect.

The table you are inserting into is *not* completely locked. You posted
an output from sp_lock. According to this output, spid 54 holds an
exclusive table lock on table 1181963287, but that does not seem to
the main theatre for your problem. (You can use
"SELECT object_name(118 1963287)" to see which table this is.)

Instead, the main part of the show appears to be table 1117963059, and
spid 54 does not any table locks on this table. But it does hold locks
on all newly inserted rows, as well as all new inserted index nodes.
Process 52 is blocked by spid 54, and this is why:

52 5 1117963059 4 KEY (5301214e6d62) S WAIT

Spid 52 is trying to get a shared lock on an index key, but is blocked.

Assume that the query spid 52 has submitted is "show how many items of
widget X we sold last week", and the optimizer decides to use the
non-clustered index over widget_id to access the sales numbers. And
among the new rows you insert, there are rows with widget_id in question.
When spid 52 tries to access those index nodes, it will be blocked.

So while you don't get a table locked, it is not as simple that other
processes can just read the existing data, and don't bother about the
new data.

The best way would be look into how to shorten the transaction length.
It sounds as if row are being inserted one-by-one, in which case there
are lots of possibilities for improvements.

Another possibility is insert the data into a staging table, and the
insert with one big INSERT statement at the end. But if that is many
rows, that could still block for considerable time.

A further development is to use partitioned views. Here, too, you copy
the data into an empty table, that no other process sees. The processes
that reads data, access the view, not the table. Once the table has been
loaded, you change the view definition to include the new table.

Then you can of course, use READ UNCOMMITTED for the readers, but then
may get some funky results that is not consistent. (READ UNCOMMITTED
on the writer has no effect.) A better alternative may be READPAST,
in which cases locked rows are simply skipped. But this is only a locking
hint, and is not settable as a transaction level.

In SQL 2005, you would probably use the new SNAPSHOT isolattion level,
in which case the readers would see the data as it was before the long
transaction started.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
4732
by: rkusenet | last post by:
Hi, I am still not very proficient in SQLServer. So apology if the question sounds basic. We have a script to clean old unwanted data. It basically deletes all rows which are more than 2 weeks old. It deletes data from 33 tables and the number of rows in each table runs into few millions. What I see in the script (not written by me :-) ) is that all data is deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
6
10577
by: ujjc001 | last post by:
Hello all. I have many connections that are sleeping. I can right click, kill, up to 2 at a time. It takes about 20 seconds to kill it. Is that normal? I tried to make a stored procedure to kill them but it ate all the power of the server, (all users yelled at me at the same time :o) ) Is this length of time normal for killing these processes? Thanks Jeff
1
1270
by: Tim Feeley | last post by:
Hey guys, I currently have a giant client editor form in ASP/SQL. The form is fine. The problem is that there are several sub-forms that store their data in other tables. Which gets cluttered. For example, I can't let users add data in these sub-forms until the main form is completed and an autonumbered ID is generated. We're taking a few months out of new developments to go back and fix a bunch of things (like this), and I was...
5
8707
by: Eric Freeman | last post by:
I'm trying to drop a table and it's taking a very long time. There has been a lot of modification to the table and it has a lot of old data still being used as a result of not using the vacuum function enough. I ran an insert into and it was taking a long time (ran for about 48 hours) so I aborted it. I tried vacuuming it and that ran for about the same amount of time before I aborted. I figured the fastest ting would be to drop the table...
1
1610
by: Stinky Pete | last post by:
Hi, I've got two users needing to look at the same database, but both are running differnent versions of access. One is running running Win 98 / Access 97 and the other is running Win XP / Access 2003. I've converted the file to 97 format so both can use it - but is there a better way to do this and not mess with the original version? Obviously ugrading would a good, but I'm not going to get approval to change until a new PC is...
2
2370
by: Adnan | last post by:
Hey Ppl, I'm developing an Online Auction Site using ASP.net and am experiencing a problem with Transactions in ADO.Net. When beginTrasaction() function is invoked from a specific connection, and insertions are made into a table, no other connection can access the table, until the transaction is committed. I've set the Isoloation level to ReadUncommited.
18
2208
by: Larry Herbinaux | last post by:
I'm having issues with garbage collection with my long-standing service process. If you could review and point me in the right direction it would be of great help. If there are any helpful documents that you could point me to help me control the GC, then that would be great also. The .Net GC does not cleanup memory of our service process unless it is forced to by another process that hogs memory. · GC Algorithm - This is an issue...
35
3973
by: aNt17017 | last post by:
This is my code: long fact(int n) { if (n == 0) return(1); if(n > 100) { printf("\t\tERROR: %d is too large for factorial.\n", n); return 1;
2
1764
by: srinivasan.shanmugapillai | last post by:
Hi, I'm running a VB.NET windows applicatrion that is using the enterprise services transactions. While running this application without enterprise services support, a connection is established and data is retrieved sucessfully. when the class is configured to run inside enterprise services, it fails without proper error description.
0
8827
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8722
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9052
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7985
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6652
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5971
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4481
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4740
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3180
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.