July 16, 2008 at 2:53 am
I'm using ROWLOCK in my select statement, to lock a particular row...
begin tran
Select * from
but when I try to update the same record in a different session, it is not getting updated since it is locked...
But if i try to update a different row in the table, it is not allowing that too... and I'm able to update any record in the table only if I commit the transaction...
I'm not sure why the ROWLOCK is locking the complete table instead of the row...
* My table DOES NOT contain any Indexes...
Your thoughts in this regard would immensely help me... and correct me if am wrong anywhere...
thans in advance...
July 16, 2008 at 3:33 am
Check what locks are held on the tables in question, there might be shared locks from your select statement because you are not using an index the update might not be able to get/find the row in question
July 16, 2008 at 4:46 am
Almost all the time, it's better to allow SQL Server to control the locking scheme in your queries. There are exceptions to this, but the exceptions should be exceptional.
It sounds like you need some indexes on your table. Generally, without an index to help direct the optimizer and query engine, they'll do table scans. So even though you've placed a specific lock within your query, it doesn't mean that SQL Server can perform the query with only that lock. At the very least, there's probably a shared lock being taken out on the entire table and that would prevent other, exclusive, locks from taking hold.
Start with identifying a good place to put the clustered index. Then worry about other indexes you might need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2008 at 8:31 am
ashok (7/16/2008)
I'm not sure why the ROWLOCK is locking the complete table instead of the row...* My table DOES NOT contain any Indexes...
If you have no indexes, then a read of any row of the table will require a read of the entire table, hence the entire tablewill be getting locked.
What proportion of the table are you returning with that select statement?
Also, what isolation level are you running in? The default (read committed) releases shared locks as soon as the statement finishes, not when the transaction completes. To hold a shared lock until the end of the transaction requires repeatable read or serializable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 12:12 pm
A word of caution, "hints" are just that "hints". The optimizer will "try" but there could be cases in which it can't. This, is one of them. You should avoid scans if you are planing to use this.
* Noel
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply