long running transactions w/ other users needing to read data

**Gang He [MSFT]** · Jul 23 '05, 09:24 AM

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

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" <google@webpaul .net> wrote in message
news:1118351890 .044392.321950@ g43g2000cwa.goo glegroups.com.. .[color=blue]
> 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
>[/color]

**pb648174** · Jul 23 '05, 09:24 AM

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

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

**Stu** · Jul 23 '05, 09:24 AM

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

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.

**Damien** · Jul 23 '05, 09:25 AM

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

pb648174 wrote:[color=blue]
> The thing is though, the long running transaction is the only
> transaction running. All the other processes are just running non
> transactional queries.[/color]

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

**pb648174** · Jul 23 '05, 09:25 AM

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

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

**Damien** · Jul 23 '05, 09:25 AM

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

pb648174 wrote:[color=blue]
> The thing is though, the long running transaction is the only
> transaction running. All the other processes are just running non
> transactional queries.[/color]

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

**pb648174** · Jul 23 '05, 09:25 AM

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

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

**Erland Sommarskog** · Jul 23 '05, 09:25 AM

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

pb648174 (google@webpaul .net) writes:[color=blue]
> 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[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Erland Sommarskog** · Jul 23 '05, 09:25 AM

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

pb648174 (google@webpaul .net) writes:[color=blue]
> 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[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**pb648174** · Jul 23 '05, 09:25 AM

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

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.

**pb648174** · Jul 23 '05, 09:25 AM

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

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** · Jul 23 '05, 09:25 AM

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

On 9 Jun 2005 14:18:10 -0700, "pb648174" <google@webpaul .net> wrote:
[color=blue]
>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
>understandin g of isolation levels is the least strict. What can I do to
>prevent this from happening?[/color]

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.

**Steve Jorgensen** · Jul 23 '05, 09:25 AM

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

On 9 Jun 2005 14:18:10 -0700, "pb648174" <google@webpaul .net> wrote:
[color=blue]
>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
>understandin g of isolation levels is the least strict. What can I do to
>prevent this from happening?[/color]

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** · Jul 23 '05, 09:25 AM

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

pb648174 (google@webpaul .net) writes:[color=blue]
> 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?[/color]

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.
[color=blue]
> 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.[/color]

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.

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

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

long running transactions w/ other users needing to read data

long running transactions w/ other users needing to read data

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment