9

I have a complex select query and a huge table.

I'm running this select statement, meanwhile an Update statement arrives and tries to update the table.

IMHO - update requires an exclusive lock - so the update statement will have to wait till the select command is finished.

  1. Am I right ?

  2. what can I do in order to: execute the complex select, and also let the update command run (currently I don't care about dirty data)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Royi Namir
  • 144,742
  • 138
  • 468
  • 792

2 Answers2

16

Yes - to a degree.

How long a SELECT holds on to a shared lock is depending on the isolation level of the transaction:

  • READ UNCOMMITTED - no shared lock is acquired at all - UPDATE is not blocked
  • READ COMMITTED - shared lock is acquired just for the duration of reading the data - UPDATE might be blocked for a very short period of time
  • REPEATABLE READ and SERIALIZABLE - shared lock is acquired and held on to until the end of the transaction - UPDATE is blocked until the SELECT transaction ends

Technically, the UPDATE statement first gets an UPDATE lock - which is compatible with a shared lock (as used by the SELECT) - for the duration of the time while it's reading the current values of the rows to be updated.

Once that's done, the Update lock is escalated to an exclusive lock for the new data to be written to the table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • `the UPDATE statement first gets an UPDATE lock - which is compatible with a shared lock ` I don't understand marc. the update is waiting to select to finish ? – Royi Namir Aug 06 '12 at 14:35
  • 1
    @RoyiNamir: the update can acquire the `Update` lock (to read the "old" data) while another process is still reading that same row and has a `shared` lock on that row. The update won't be able to go to an exclusive lock (to write the "new" data back), however, as long as that `shared` lock is still in place - so it'll have to wait there.... – marc_s Aug 06 '12 at 14:35
6

When you run the two statements concurrently (a SELECT and an UPDATE) the actual behavior will be basically random. This is because neither of the operations is instantaneous. To simplify, consider your table a list and SELECT is traversing this list, looking at one row at a time. UPDATE is also trying to update one or more rows. When the UPDATE is trying to update a row behind the SELECT then nothing happens (no blocking) because the SELECT has already progressed past the UPDATE point. If the UPDATE is trying to update the row at which SELECT is looking right now then the UPDATE will have to wait for SELECT to move on, which will happen very very very fast and the UPDATE will unblock and succeed, while the SELECT is moving ahead. But if the UPDATE is updating a row ahead of the SELECT then the update will succeed and, later, SELECT will eventually reach exactly this row and will stop, blocked. Now SELECT has to wait until the transaction that did the UPDATE commits.

This is the simplified story. The real life is much more complicated. The SELECT can have multiple read points (parallel plans). Both the SELECT and the UPDATE are subject to choosing an access path, meaning use one or more secondary indexes to locate the rows. Complex queries may contain operators that cause multiple lookups into a table (eg. joins). Both the SELECT and the UPDATE can do bookmark lookups to fetch BLOB data, which changes significantly the locking behavior. Cardinality estimation may cause the SELECT to run at a high granularity lock mode (eg. table level Shared lock). The UPDATE can trigger lock escalation, and the escalation can fail or succeed. Choosing different access paths can lead to deadlock. False lock contention can occur due to hash collisions. There are just about one myriad variables that have a say in this. And I didn't even mention higher isolation levels (repeatable read, serializable).

Perhaps you should use SNAPSHOT isolation and stop worrying about this issue?

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • If you suggest going with `SNAPSHOT` - just make sure the `TempDB` is properly set up ! (location and number of data files, sizing options etc.) – marc_s Aug 06 '12 at 14:08
  • 1
    Yes, repeatable and serializable cause shared lock to be retained for the transaction scope. Serializable also add range locks. All these add complexity. – Remus Rusanu Aug 06 '12 at 14:08
  • 1
    @marc_s: the link to the MSDN topic covers the [Row Versioning Resource Usage](http://msdn.microsoft.com/en-us/library/ms175492(v=sql.105)). I prefer not to scare users away, since I consider the benefits of SNAPSHOT far outweigh its cost. – Remus Rusanu Aug 06 '12 at 14:10