| Author |
Topic |
|
KlausJensen
Starting Member
14 Posts |
 Posted - 2003-11-18 : 09:40:39
|
| Hi!Problem:If I have a running transaction containing an update, and I have not yet committed it, i am unable to select * from the table. Ie. a lock is in function...How do I resolve this - without modifying the select-statement? Is it possible at all?I have looked into Table hints, searched the forums etc, but did not find the answer.Thanks in advance- Klaus---Klaus JensenInternet Developer1eEurope Denmarkhttp://www.1eEurope.dk |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-11-18 : 09:53:46
|
| Can you post the relevant portion of code. |
 |
|
|
KlausJensen
Starting Member
14 Posts |
Posted - 2003-11-18 : 10:36:13
|
| Sure! I have set up a simple example to illustrate the problem.CREATE TABLE [dbo].[tblTest] ( [ID] [int] NOT NULL , [Name] [varchar] (10) )GOINSERT INTO tblTest (ID, name) VALUES (1, 'Joe')INSERT INTO tblTest (ID, name) VALUES (2, 'Bob')INSERT INTO tblTest (ID, name) VALUES (3, 'Billy')Now open two windows (representing two different connection) in query analyser.In the first window, enter...:SELECT * FROM tblTestin the second...:BEGIN TRANSACTION UPDATE tblTest SET NAME = 'Edited' WHERE ID=3--COMMIT TRANSACTIONNow perform the folowing steps..1. Execute the select statement (in the first window) - works great! :)2. Execute the statement in the second window. Note the commit-part is commented out. Works great, the update is issued but is not committed yet.3. Execute the select statement (in the first window) again. This time the query hangs, because (some of) the data in the query is locked. :(4. Mark and run COMMIT TRANSACTION in the second window. The update is now committed and locks released. Note that the select in the first window finished as we committed the transaction.How do I modify the update to avoid this behavior? In real-life production enviroment, the update is MUCH more complex and issued MANY times, but this example illustrates the problem quite nicely I think.Suggestions?Thanks in advanceKlaus---Klaus JensenDeveloper |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2003-11-18 : 10:48:28
|
| Read up on SET TRANSACTION ISOLATION LEVEL in BOL. You can set the behaviour of 'dirty reads' for your connection to either read uncommited data, or exclude it altogether.Raymond |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-11-18 : 10:52:08
|
| Not knowing what concurrency levels your application requires, you may look into Locking Hints on BOL to determine which hint, if any, may be appropriate for your situation. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-18 : 11:45:24
|
| Forget dirty reads....(NOLOCK)What's your update methodology...sounds like you're having lock escalation...AND it sounds like you're using cursors for this...yes?Brett8-) |
|
|
|
KlausJensen
Starting Member
14 Posts |
Posted - 2003-11-19 : 04:01:31
|
| Cursors are not being used, no. And with regards to "please read up on BOL", I have already been there - and I do not know what is required in my current situation. A simple solution that will eliminate the problem in my simple examle will also eliminate the real-world problem, I am sure. :)And it must be a solution to the Update-part, since modifying litearally hundreds of select-statements and select-stored procedures is not a popular option.Tanks in advance- Klaus---Klaus JensenDeveloper |
|
|
|
KlausJensen
Starting Member
14 Posts |
Posted - 2003-11-19 : 04:05:38
|
| Cursors in SQL are not being used. The stored procedure is being called from a vbscript thousands of times - part of an import job. So no locks are issued from SQL Cursors.---Klaus JensenDeveloper |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-11-19 : 04:24:22
|
Klaus, the example you have given will rarely happen in real life - transactions don't (or shouldn't) lie about waiting for somebody to commit them. It is important to have transactions as short as possible to avoid situations like the above. Even on a system with many concurrent users, well written transactions go through in a flash and hardly cause selects to hold up. Besides, SQL Server determines the granularity of the lock required beforehand, and escalates the lock to higher level only if required. Under normal circumstances, your locks wouldn't go beyond page-level, effectively leaving the other rows in the table free to be queried by everyone else.Owais
Where there's a will, I want to be in it. |
|
|
|
|
Update causes locking - how to resolve