Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2010-09-10 : 23:56:25
|
Hi there,If the following query is running concurrently then can a dead lock occur?SELECT * FROM tbl WITH (UPDLOCK) How about this...SELECT * FROM tbl WITH (UPDLOCK) WHERE Col1 = 'hello' In MS a later query will always block on an earlier one. But I was wondering if this would happen if the queries ran extremely close together in time so that one query got half of the table where as the other got the other half?Cheers, XF. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-09-11 : 01:19:30
|
| OK, so what I've got is a table of holiday chalets to sell and when a user adds one to their shopping cart, the query needs to fetch one with the correct berth that's not already being referenced by another table.If I put an ORDER BY in the query, will concurrent queries attack the table in the same direction? Or do I need to put a table lock on the the table of chalets? There needs to be a lock on the chalet row because otherwise another user might try and grab hold of it for another order.Thanks for your input, XF. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-09-12 : 03:45:37
|
| Select query also creates a lock against the table in order to retrieve the right results.you can override this by setting "set transaction level isolation level to read uncommited or you can use query hint like select * from MyTable with (nolock)this will be true in case of select statement, so both queries can run simultaniesly. but try to avoid so called dirty reads. :) |
|
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-09-12 : 13:29:00
|
quote:
If the following query is running concurrently then can a dead lock occur?SELECT * FROM tbl WITH (UPDLOCK)
With that simple query, I don't think so.If 2 processes run the query concurrently, the first will block the second, but never block each other. UPDLOCK plays a litter purpose in that simple query. I don't know how it can results a dead lock.The same for query:SELECT * FROM tbl WITH (UPDLOCK) WHERE Col1 = 'hello'quote:
In MS a later query will always block on an earlier one. But I was wondering if this would happen if the queries ran extremely close together in time so that one query got half of the table where as the other got tha other half?
Doesn't matter how "extremely close" they are, either one will run first, andathe other one (which accesses the same sourse, in your case) has to wait untill the first releases the lock. So no deadlock. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Can this result in a dead lock?