Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Query Fails Table Gets Locked By Select Query Expand / Collapse
Author
Message
Posted Thursday, January 27, 2011 6:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, January 27, 2011 6:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:58 AM
Points: 5,077, Visits: 8,918
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
Posted Wednesday, December 12, 2012 5:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:39 AM
Points: 4, Visits: 10
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
Posted Sunday, December 16, 2012 9:51 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:56 PM
Points: 80, Visits: 329
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 »

Add to briefcase

Permissions Expand / Collapse