| Author |
Topic |
|
tp5harp
Starting Member
5 Posts |
 Posted - 2010-01-20 : 18:12:43
|
| Hi All,I have written a number of insert statements that are surrounded by a Begin and Commit Transaction.However during the process of this running I notice that it places a full table lock on the table.The only way I can read the data whilst the inserts are running is to add the Read Uncommited to my select statement.Does anybody know a way of allowing the insert statements to only lock the current row. Not the whole table.Many thanks,tp5harp |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-20 : 18:28:42
|
| yes. remove the transactions. |
 |
|
|
tp5harp
Starting Member
5 Posts |
Posted - 2010-01-20 : 19:13:23
|
quote:
Originally posted by russell
yes. remove the transactions.
Trouble is I need the transactions. I present a SSRS report at the end of the program. If the users is happy with the figures they click Yes and the transactions are committed, if they click No they are rolled back.Is this the only way to remove the table lock. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-20 : 19:49:40
|
| wow. that's begging for performance problems. so you're holding an open tran while the user peruses the data?i would need to really understand your business needs to recommend a solid approach, but common methods are data islands, use of timestamps columns, checksums etc. you can inplement manual locking of the records in question with a column or two added to the tables in question.again, not much specific advice i can give you at this point, but i assure you that maintaining open transactions while the end user is viewing the data is never going to be a good idea.what happens if the user executes the app, starts looking at the data, then takes a couple of phone calls, goes to lunch and then calls it a day...? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 02:54:33
|
| The points Russell makes are very important (and very nicely put, normally folk here just say WTF when this question comes up!)Its a common problem, lots of routes to solutions, all of them painful! |
|
|
|
tp5harp
Starting Member
5 Posts |
Posted - 2010-01-21 : 04:16:43
|
| Fair comments, you guys have obviously been doing this a lot longer than me. I will change the code not to hold on to the transaction, but instead delete the batch at the end if the user does not want the keep the transactions. I will keep the transaction in the code when the inserts are being created in case of a crash, then the transaction will be rolled back. I still don't understand however why the transaction put a full lock on the table and not just the records its inserting. Could someone explain this to me.Many thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 05:01:03
|
| most likely reason for a lock on the table is that the number of rows involved, and the number of pages in the table that they affected, got to the point where SQL escalated the locks to a Table Lock. (There comes a point where there are so many individual locks that the maintenance of them exceeds the brute-force impact of a Table lock).Even without that locking is normally at the Page level - so your action may be locking other records which happen to be on the pages that your rows occupy.Either way, best to have some form of "soft lock" or "logical lock" if you need to wait for user interaction |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-21 : 05:40:43
|
| you may find that using the isolation level READ_COMMITTED_SNAPSHOT may help but I urge you to follow Russell's advice. He is talking sense.However. MS recommends this isolation level to reduce blocking for reads while write actions are being performed. Read here for more information. Not sure if this will help during table lock state.http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspxCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
tp5harp
Starting Member
5 Posts |
Posted - 2010-01-21 : 13:48:03
|
| That's great info, thanks for all your help. |
|
|
|
|
Record Locks