January 27, 2011 at 6:21 am
Hi,
My update query fails as table gets locked by select query.
When i am selecting data it comes around 500 something.
But after that i am trying to Update it is giving timeout erro.
But if i am doing SELECT TOP 200 it is working fine as normal.
Can any one give me whats going on inside DB.
Thanks!!
Shatrughna
Shatrughna
January 27, 2011 at 6:33 am
Shatrughna
Please can we see your SELECT and UPDATE queries? What does "500 something" mean?
It sounds as if you're doing SELECT * from a large table, and this is taking a long time and blocking the update. If you do SELECT TOP 200 * then the results will be returned quicker. As soon as all the data is returned then the lock is released and your UPDATE statement can get the exclusive lock it needs. One way to avoid the blocking is to use the NOLOCK hint in your SELECT statement. Only do this if it is not important for the data to be totally accurate. Do a search for "dirty reads" if you want to know why this is.
John
December 12, 2012 at 5:32 am
Hello,
I have a problem with selecting rows which I want to update.
Select sets a read lock on the rows and then if I try to update selected rows I get a deadlock.
In ORACLE there is a SELECT FOR UPDATE clause but it does not exist in the MSSQL. How it should be solved?
In easy words how to select rows which then I want to update in the same transaction properly?
Thank you in advance for help.
December 16, 2012 at 9:51 pm
Hi shatrughna,
Interesting idea from Oralce to use a select to do the lock first. Look at the note section since this applies to SQL Server.
---
Link:
http://docs.oracle.com/cd/E17952_01/refman-5.1-en/innodb-locking-reads.html
Note:
Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.
---
In SQL server, autocommit is set on as a default. Just run the UPDATE statement with a where clause.
If you are trying to join to other tables to get the input to the UPDATE statement, you can use the FROM clause.
My blog, update samples:
If you want to get creative, use a common table expression (CTE) to pick out the rows by the Primary Key (PK). Pass this to a UPDATE statement.
In short, even if you were doing this in a transaction, I do not see the need of a select before the update. I do not think this technique is valid with SQL Server. Only thing that I know that even comes close is an update cursor.
Good luck,
John Miner
Crafty DBA
www.craftydba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply