SP Transaction isolation level locking SQL 2005 [modified]
-
I'm having a locking issue I think. Rather than post a whole heap of code, here is basically what I'm trying: Begin trans Insert into table1 Insert into table2 Insert into table3 select from table1, table2, table3 plus a few other unmodified tables. Commit trans Basically what seems to be happening is that the select on 3rd statement returns no records (because they are locked). After committing, same select returns exactly what it should. I could probably do this with several non-nested transactions, but that kind of defeats the purpose of the transaction. I have tried SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before and during the main trans. Also tried wrapping the first two inserts in begin / commit and SELECT FROM WITH(NOLOCK). Have googled nolock and also TRANSACTION ISOLATION LEVEL but not really come up with anything useful. Does anyone have any ideas or can point me in the direction of a URL with some information please?
modified on Monday, June 16, 2008 4:33 AM
-
I'm having a locking issue I think. Rather than post a whole heap of code, here is basically what I'm trying: Begin trans Insert into table1 Insert into table2 Insert into table3 select from table1, table2, table3 plus a few other unmodified tables. Commit trans Basically what seems to be happening is that the select on 3rd statement returns no records (because they are locked). After committing, same select returns exactly what it should. I could probably do this with several non-nested transactions, but that kind of defeats the purpose of the transaction. I have tried SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before and during the main trans. Also tried wrapping the first two inserts in begin / commit and SELECT FROM WITH(NOLOCK). Have googled nolock and also TRANSACTION ISOLATION LEVEL but not really come up with anything useful. Does anyone have any ideas or can point me in the direction of a URL with some information please?
modified on Monday, June 16, 2008 4:33 AM
Strange - I just tried to replicate said behaviour and I had no issues with inserting in a table, selecting from said table and inserting into another table while within the same transaction. Further more the things you state you have tried should be - AFAIK - enough/the correct method to solve issues similar to this. Are you sure - 100% - that your "insert into table 3" query runs as it should? And that there aren't another problem then with the isolation level? How do you call the transaction - via a code layer? Or directly as a query? - If the first, then try to run a simple example directly as a query and see if that gives you problems. Could be the problem is completely elsewhere?
--------------------------- Blogging about SQL, Technology and many other things
-
Strange - I just tried to replicate said behaviour and I had no issues with inserting in a table, selecting from said table and inserting into another table while within the same transaction. Further more the things you state you have tried should be - AFAIK - enough/the correct method to solve issues similar to this. Are you sure - 100% - that your "insert into table 3" query runs as it should? And that there aren't another problem then with the isolation level? How do you call the transaction - via a code layer? Or directly as a query? - If the first, then try to run a simple example directly as a query and see if that gives you problems. Could be the problem is completely elsewhere?
--------------------------- Blogging about SQL, Technology and many other things
I did exactly the same thing, it all worked as expected - and I agree, AFAK it is the correct way to do it. I think you are correct, the problem is elsewhere. I do know in the past I have employed this technique many times without problems.
Bob Ashfield Consultants Ltd
-
I did exactly the same thing, it all worked as expected - and I agree, AFAK it is the correct way to do it. I think you are correct, the problem is elsewhere. I do know in the past I have employed this technique many times without problems.
Bob Ashfield Consultants Ltd
Thanks for the replies. Took the whole thing apart and ran each query separately, works as expected. Put back together, commented out the 3rd INSERT to just leave select. Pulls no records as expected. Commented out select and hardcoded insert with some data that I knew insert1 & 2 would create and works fine. Headbutted wall for a while, cracked open a bottle of wine, rebooted server and whadaya know...Works perfectly. Removed WITH(NOLOCK)s and... works perfectly. Removed READ UNCOMMITTED, works perfectly. No idea what caused it. Could have been some other lock, although nothing else should have had a lock. Its all marked for some serious testing and I'll see if its reproducable! Anyway, thanks for the help. Hope I can reciprocate one day.
-
Thanks for the replies. Took the whole thing apart and ran each query separately, works as expected. Put back together, commented out the 3rd INSERT to just leave select. Pulls no records as expected. Commented out select and hardcoded insert with some data that I knew insert1 & 2 would create and works fine. Headbutted wall for a while, cracked open a bottle of wine, rebooted server and whadaya know...Works perfectly. Removed WITH(NOLOCK)s and... works perfectly. Removed READ UNCOMMITTED, works perfectly. No idea what caused it. Could have been some other lock, although nothing else should have had a lock. Its all marked for some serious testing and I'll see if its reproducable! Anyway, thanks for the help. Hope I can reciprocate one day.
Hehe. Stuff which should work, but doesn't sporadically .... always good fun to debug :) Well, at least you got it running... more or less :)
--------------------------- Blogging about SQL, Technology and many other things