SQL Server 2005 Transactions

Question

I have a long running stored proc (approx 30 mins) which is currently running within a transaction (isolation level snapshot).
I've set the transaction to snapshot to avoid locking records preventing other processes from accessing the data.

What I'm trying to do is write to and read from a status table, but although we're in a transaction I'd like to write to and read from the status table as if I'm not in a transaction. I need this so that other processes can read any updates to this table by my stored proc, and this stored proc can also read any inserts made by other processes.

I realise that having my entire stored proc running within a transaction isn't recommended, but this has been done for other reasons and we need to stick with that approach.

So my question is within a transaction, is it possible to execute a query or call a stored proc which effectively isn't enlisted in the transaction?

Answer 1

No. Your only chance is to open a separate session, eg. use a CLR procedure that connect back on a different connection (not on the internal context connection).

Snapshot isolation helps only for your reads. For writes, is the other transactions that have to use snapshot isolation (or have READ_COMMITTED_SNAPSHOT ON on the database). Write vs. write conflicts have no solution.

Answer 2

If you do "SET TRANSACTION ISOLATION READ UNCOMMITED" on both your long running query and the query that needs to read data from the transaction, they should be able to read dirty data.

I don't know how this will work with your isolation level snapshot, as I'm not familiar with it.

Answer 3

I would have to test the theory but I think Remus could probably confirm or not, if I wanted a sneaky way outside of the transaction to make adjustments I would check whether making a call to msdb.dbo.sp_start_job 'yourjob name' and configuring a job that performs the update statement you want would work.

It's sneaky enough that it might work - the agent job would be run on a different account and should be outside of your connection / transaction.

It's a easier than writing a CLR stored proc, although I appreciate it is a hack.

A.

(Tested it - hack worked)

Answer 4

What does the sp do? One transaction lasting for 30 minutes is eccessive. Most likely you have poorly performing code (you didn't use a cursor did you or a correlated subquery or anythingelse that involves row by row insted of set-based processing?) and the sp itself can be reduced to a transaction that won't cause as much locking.