Update Query Fails Table Gets Locked By Select Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Update Query Fails Table Gets Locked By...

Update Query Fails Table Gets Locked By Select Query

Rate Topic

Display Mode

Topic Options

Author

Message

shatrughna

Posted Thursday, January 27, 2011 6:21 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 03, 2012 12:17 AM
Points: 97, Visits: 1,586

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

Post #1054514

John Mitchell-245523

Posted Thursday, January 27, 2011 6:33 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

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

Post #1054522

emilm

Posted Wednesday, December 12, 2012 5:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 7:04 AM
Points: 4, Visits: 8

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.

Post #1395605

j.miner

Posted Sunday, December 16, 2012 9:51 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 79, Visits: 288

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:
http://craftydba.com/?p=525

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

Post #1397079

« Prev Topic | Next Topic »

Permissions