long running transactions w/ other users needing to read data

[pb648174]

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

[Gang He [MSFT]]

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

[pb648174]

The thing is though, the long running transaction is the only
transaction running. All the other processes are just running non
transactional queries.

[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 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.

[Damien]

If you read the BOL section "SET IMPLICIT_TRANSACTIONS", 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_TRANSACTIONS"
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

[pb648174]

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', 'AllowTableLocks', FALSE
GO

[Erland Sommarskog]

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(1181963287)" 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, esq...@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[pb648174]

Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
some work on the indexes in order to fix it? Would changing to a
clustered index make it less likely to block?

The rows aren't being inserted one row at a time, but they are being
done one "project" at a time(maybe a few thousand records), with pretty
standard Insert Into..Select statements. I have my application pausing
for a few seconds between projects to try and reduce stress on the
server and give other processes a chance for freedom, but from what you
are saying, perhaps I need to remove the wait times in order to get the
transaction over as quickly as possible.

[Steve Jorgensen]

On 9 Jun 2005 14:18:10 -0700, "pb648174" <goo...@webpaul.net> wrote:

>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?

Personally, I don't abide long-running database transactions in my
applications. If the issue comes up, I reingineer until the need for the
long-running transaction goes away.

Here's one approach I've used...
1. For each table that will be affected, add 2 links to batch records, one for
initial, and one for final.
2. Add a table of batches with a batch ID, and a status that may be pending or
completed.
3. For each batch process, create a new batch record with a status of pending.
4. When writing to the database, point the initial batch of each new record to
the batch record, and point the final batch of each deleted record to the
batch record. Do not modify existing records - instead, add a new modifed
copy, and finalize the old copy.
5. Once all batch updates are completed, change the batch record status from
pending to completed.

When querying the data, to see only data that is current, simply join to the
batch table, and exclude any records that have final batch links to a
completed batch record. Every once in a while, purge these outdated records,
so they don't pile up, and slow down the system.

If a transaction fails, you can roll back by deleting the records with initial
batch references to your batch record, and set any final batch references to
your batch record back to Null, then delete your batch record.

This approach only works if there can only be one batch processor at a time
affecting a particular group of tables, but it has the benefit of not
requiring server transactions to be maintained for long periods of time. A
batch could take several days and have no negative impact on anything. You
could even halt the batch, and continue it on a different meachine if
necessary.

[Erland Sommarskog]

pb648174 (goo...@webpaul.net) writes:
> Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
> some work on the indexes in order to fix it? Would changing to a
> clustered index make it less likely to block?

I sort of assumed that all new rows were inserted at the end of the
clustered index. But if the rows you insert are not aligned with the
clustered index, then the problem becomes a lot worse. Any process
that finds itself in need ot a table scan would be blocked.

> The rows aren't being inserted one row at a time, but they are being
> done one "project" at a time(maybe a few thousand records), with pretty
> standard Insert Into..Select statements. I have my application pausing
> for a few seconds between projects to try and reduce stress on the
> server and give other processes a chance for freedom, but from what you
> are saying, perhaps I need to remove the wait times in order to get the
> transaction over as quickly as possible.

Had you committed after each batch, the pause could make some sense. But
if you don't commit until the end, then you should get away with those
pauses.

[pb648174]

The only thing about taking out the pauses is that it hammers on the
database so hard, it pegs the CPU.. With the pauses other processes
have a chance to go forward, but with this locking it doesn't seem to
matter.

[pb648174]

Isn't this "batch" approach sort of ridiculous, given that databases
are supposed to be able to run transactions?? We have hundreds of
tables and the above approach would be extremely onerous. I don't think
it would work anyway since there are identity columns in most of the
tables which need to match up between parent and child relationships.
If somebody inserted a single record while the transaction was running,
that would hose the whole process.

[pb648174]

This is interesting - adding a clustered index on the identity column
fixed the previous problem, so I picked one module (which has about 5
or 6 tables) to see if I could free up that module while the copy
process was running. I went and put clustered indexes on all the
identity columns of those tables, but now another table has a locking
problem, although this time, not on the index. The output from that
line from sp_lock is below:

54 8 2101582525 1 PAG 1:26568 S WAIT

So now it is a page lock, which from what I've read is a group of
records. That seems resonable given that I am copying large amounts of
data, but why is it stopping my other process?

[Erland Sommarskog]

pb648174 (goo...@webpaul.net) writes:
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU..

CPU:s are humans. Just keep them working!

(But, OK, if you see a puff of smoke, it's probably time for a pause.)

[Erland Sommarskog]

On a little more serious note... if the machine has more than one
CPU, you could consider to reduce the degree of parallelism, to leave
some CPUs to the rest of the pack. You do this by adding

OPTION (MAXDOP n)

at the end of the query. In fact you can even try 1, to abort parallelism
entirely. SQL Server appears to be over-optimistic by the benefits of
parallelism, and non-parallel plans may be better.

If your CPU(s) are hyper-threaded, you should server-configuration
parameter "Max degree of parallelism" to the number of physical CPUs.

[Erland Sommarskog]

Lacking telepathic abilities, I can't say why.

What you could try is to use aba_lockinfo, which you find on my web
site, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. This procedure
give you an overview active process, which object they lock, and also
current statements. This gives a little better idea of what is going
on.

[rcamarda]

I have no idea if this will help or not....
A vendor gave me a giant sql select that has 200 fields and 25 joins.
The sample of the join:
LEFT OUTER JOIN view_TPSEnum_10145 WITH(NOLOCK) ON
view_TPSEnum_10145.nIndex = [tblObjectType5001_1].[FldNumeric21251]
I'm told the WITH(NOLOCK) wont lock the source table *shrug*. Read the
docs that it allows dirty reads and is used only for selects.