168

Please help me understand the use-case behind SELECT ... FOR UPDATE.

Question 1: Is the following a good example of when SELECT ... FOR UPDATE should be used?

Given:

  • rooms[id]
  • tags[id, name]
  • room_tags[room_id, tag_id]
    • room_id and tag_id are foreign keys

The application wants to list all rooms and their tags, but needs to differentiate between rooms with no tags versus rooms that have been removed. If SELECT ... FOR UPDATE is not used, what could happen is:

  • Initially:
    • rooms contains [id = 1]
    • tags contains [id = 1, name = 'cats']
    • room_tags contains [room_id = 1, tag_id = 1]
  • Thread 1: SELECT id FROM rooms;
    • returns [id = 1]
  • Thread 2: DELETE FROM room_tags WHERE room_id = 1;
  • Thread 2: DELETE FROM rooms WHERE id = 1;
  • Thread 2: [commits the transaction]
  • Thread 1: SELECT tags.name FROM room_tags, tags WHERE room_tags.room_id = 1 AND tags.id = room_tags.tag_id;
    • returns an empty list

Now Thread 1 thinks that room 1 has no tags, but in reality the room has been removed. To solve this problem, Thread 1 should SELECT id FROM rooms FOR UPDATE, thereby preventing Thread 2 from deleting from rooms until Thread 1 is done. Is that correct?

Question 2: When should one use SERIALIZABLE transaction isolation versus READ_COMMITTED with SELECT ... FOR UPDATE?

Answers are expected to be portable (not database-specific). If that's not possible, please explain why.

Gili
  • 86,244
  • 97
  • 390
  • 689
  • 2
    Which RDBMS are you using? – Quassnoi May 07 '13 at 17:34
  • 4
    @Quassnoi, as mentioned at the bottom of the question, I am looking for a portable (not database-specific) solution. – Gili May 07 '13 at 17:36
  • 2
    Are the options `REPEATABLE_READ` and `READ_COMMITTED` even portable options? The only results I get for those are for MSSQL server – Billy ONeal May 07 '13 at 17:41
  • 1
    @BillyONeal, these [isolation levels](http://en.wikipedia.org/wiki/Isolation_(database_systems)) are defined by the SQL standard, so yes they are portable. – Gili May 07 '13 at 17:42
  • 4
    @BillyONeal: note that isolations modes guarantee that you don't see quirks they don't allow, but say nothing about the quirks they do allow. This means that setting, say, `READ COMMITTED` mode does not define whether or not you will actually see records committed by another transaction: it only makes sure you will never see uncommitted records. – Quassnoi May 07 '13 at 18:09
  • 5
    A `select ... for update` on `rooms` will still allow `room_tags` to be deleted because they are separate tables. Did you mean to ask whether the `for update` clause will prevent deletions from `rooms`? – Chris Saxon May 12 '13 at 15:30
  • "SELECT tags.name FROM room_tags, tags WHERE room_tags.tag_id = 1 AND tags.id = room_tags.tag_id;" , do you mean "WHERE room_tags.room_id = 1" instead of "tag_id = 1" ? – luochen1990 Jun 01 '21 at 06:51
  • @luochen1990 good catch. Fixed. – Gili Jun 01 '21 at 21:47

3 Answers3

125

The only portable way to achieve consistency between rooms and tags and making sure rooms are never returned after they had been deleted is locking them with SELECT FOR UPDATE.

However in some systems locking is a side effect of concurrency control, and you achieve the same results without specifying FOR UPDATE explicitly.


To solve this problem, Thread 1 should SELECT id FROM rooms FOR UPDATE, thereby preventing Thread 2 from deleting from rooms until Thread 1 is done. Is that correct?

This depends on the concurrency control your database system is using.

  • MyISAM in MySQL (and several other old systems) does lock the whole table for the duration of a query.

  • In SQL Server, SELECT queries place shared locks on the records / pages / tables they have examined, while DML queries place update locks (which later get promoted to exclusive or demoted to shared locks). Exclusive locks are incompatible with shared locks, so either SELECT or DELETE query will lock until another session commits.

  • In databases which use MVCC (like Oracle, PostgreSQL, MySQL with InnoDB), a DML query creates a copy of the record (in one or another way) and generally readers do not block writers and vice versa. For these databases, a SELECT FOR UPDATE would come handy: it would lock either SELECT or the DELETE query until another session commits, just as SQL Server does.

When should one use REPEATABLE_READ transaction isolation versus READ_COMMITTED with SELECT ... FOR UPDATE?

Generally, REPEATABLE READ does not forbid phantom rows (rows that appeared or disappeared in another transaction, rather than being modified)

  • In Oracle and earlier PostgreSQL versions, REPEATABLE READ is actually a synonym for SERIALIZABLE. Basically, this means that the transaction does not see changes made after it has started. So in this setup, the last Thread 1 query will return the room as if it has never been deleted (which may or may not be what you wanted). If you don't want to show the rooms after they have been deleted, you should lock the rows with SELECT FOR UPDATE

  • In InnoDB, REPEATABLE READ and SERIALIZABLE are different things: readers in SERIALIZABLE mode set next-key locks on the records they evaluate, effectively preventing the concurrent DML on them. So you don't need a SELECT FOR UPDATE in serializable mode, but do need them in REPEATABLE READ or READ COMMITED.

Note that the standard on isolation modes does prescribe that you don't see certain quirks in your queries but does not define how (with locking or with MVCC or otherwise).

When I say "you don't need SELECT FOR UPDATE" I really should have added "because of side effects of certain database engine implementation".

Gili
  • 86,244
  • 97
  • 390
  • 689
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    The last point is the crux of the matter, I think: "you don't need a SELECT FOR UPDATE in serializable mode, but do need them in REPEATABLE READ or READ COMMITED". – Colin 't Hart May 07 '13 at 18:01
  • You're right. The second question should have asked when `SERIALIZABLE` should be used versus `READ_COMMITTED` with `SELECT ... FOR UPDATE`. Can you please update your answer to reflect this updated question? – Gili May 07 '13 at 18:18
  • 1
    @Gili: "you don't need a `SELECT FOR UPDATE` in serializable mode", with `InnoDB`. With the other `MVCC` systems, the two are synonyms and you do need `SELECT FOR UPDATE`. – Quassnoi May 07 '13 at 18:18
  • 1
    I find Colin's [post](http://stackoverflow.com/a/16425610/14731) answers my specific questions better than your answer but I appreciate all the references you provided. I will accept an answer that best combines the two (specific answers on top, supporting references below). – Gili May 07 '13 at 18:30
  • `This depends on the concurrency control your database system is using`: I think you're splitting hairs. All cases that you list below say that the room isn't deleted between `SELECT` to the end of the transaction. So, shouldn't the answer simply be `Yes` with the supporting references below? – Gili May 07 '13 at 18:42
  • @Gili: in `Oracle`, `PostgreSQL` and `MySQL` with `InnoDB` in `READ COMMITTED` mode, the room may be deleted in another transaction after `SELECT` without `FOR UPDATE`. Actually it can even in `SQL Server`, if the `DELETE` query starts after the first `SELECT` completes. – Quassnoi May 07 '13 at 18:45
  • Yes, but we're not talking about `SELECT` without `FOR UPDATE`. The question specifically asks about `SELECT` **with** `FOR UPDATE`. I think you are also splitting hairs in the answer to the second question: `If you don't want to show the rooms after they have been deleted, you should lock the rows with SELECT FOR UPDATE`. When choosing between `SERIALIZABLE` or `READ_COMMITTED` with `SELECT ... FOR UPDATE`, either approach will fix the application but the database might vary how this gets implemented under the hood. Correct me if I'm wrong. – Gili May 07 '13 at 18:48
  • @Gili: in your first question, you ask whether `FOR UPDATE` "should" be used. Per [RFC definition of "should"](http://www.ietf.org/rfc/rfc2119.txt), you are asking about "valid reasons in particular circumstances to ignore a particular item", with "full implications understood and carefully weighed before choosing a different course". I'm trying to explain such implications so that you can understand and carefully weigh them. In some systems, you can get away without specifying `FOR UPDATE`. Say, in `SQL Server` one does not simply use `SELECT FOR UPDATE`, not without declaring a cursor. – Quassnoi May 07 '13 at 18:55
  • So in part 1 you're saying MVCC systems need `SELECT ... FOR UPDATE` while some other systems are atomic without it. And in part 2 you're saying `SELECT ... FOR UPDATE` is necessary for `READ_COMMITTED` or `REPEATABLE_READ` but not `SERIALIZABLE`. Is that correct? If so, please try rephasing the answering in a more concise way. I'm having problems seeing the forest for the trees, if you know what I mean. – Gili May 07 '13 at 20:09
  • Instead of using `SELECT FOR UPDATE` can't I just open a transaction every time I need multiple instructions to be atomically executed? It sounds more straightforward to me... – zambotn Mar 31 '22 at 08:36
  • @zambotn: the definition of "atomically" comes with a whole lot of fine print, which this question and answer are all about. – Quassnoi Mar 31 '22 at 13:13
  • @Quassnoi fair enough...To me "atomically" means basically `SERIALIZABLE` (instructions are executed _as if_ there is no other instruction executed while my critical zone is active). The purpose my question was understanding how this example can be close to a real-world scenario. Does `SELECT FOR UPDATE` works so much better than a `SERIALIZABLE` transaction in this example? – zambotn Mar 31 '22 at 15:18
  • 1
    @zambotn: you'll have to define "better" (in a separate post, not in comments). Is it ok if the delete locks? Is it ok if the room is returned by the select after it's been deleted in a concurrent transaction and the delete has been committed? And so on. – Quassnoi Mar 31 '22 at 16:15
49

Short answers:

Q1: Yes.

Q2: Doesn't matter which you use.

Long answer:

A select ... for update will (as it implies) select certain rows but also lock them as if they have already been updated by the current transaction (or as if the identity update had been performed). This allows you to update them again in the current transaction and then commit, without another transaction being able to modify these rows in any way.

Another way of looking at it, it is as if the following two statements are executed atomically:

select * from my_table where my_condition;

update my_table set my_column = my_column where my_condition;

Since the rows affected by my_condition are locked, no other transaction can modify them in any way, and hence, transaction isolation level makes no difference here.

Note also that transaction isolation level is independent of locking: setting a different isolation level doesn't allow you to get around locking and update rows in a different transaction that are locked by your transaction.

What transaction isolation levels do guarantee (at different levels) is the consistency of data while transactions are in progress.

Colin 't Hart
  • 7,372
  • 3
  • 28
  • 51
  • 1
    I think `What transaction isolation levels do guarantee [...] is the consistency of data once transactions are completed.` incorrectly implies that isolation levels don't affect what happens *during* a transaction. I recommend revising this section and providing more detail about how they impact what you see (or don't see) during a transaction. – Gili May 07 '13 at 18:22
  • 1
    I find your post answers my specific questions better than [Quassnoi's](http://stackoverflow.com/a/16425603/14731) but I appreciate all the references he provided. I will accept an answer that best combines the two (specific answers on top, supporting references below). – Gili May 07 '13 at 18:31
  • Locking and isolation are interchangeably complicated. So are there any books to get the knowledge about that? – Chao Jan 27 '15 at 06:59
3

What is SELECT FOR UPDATE?

SELECT FOR UPDATE is a SQL command that’s useful in the context of transactional workloads. It allows you to “lock” the rows returned by a SELECT query until the entire transaction that query is part of has been committed. Other transactions attempting to access those rows are placed into a time-based queue to wait, and are executed chronologically after the first transaction is completed.

BEGIN;
SELECT * FROM kv WHERE k = 1 FOR UPDATE;
UPDATE kv SET v = v + 5 WHERE k = 1;
COMMIT
Mahdi Abedi
  • 124
  • 2
  • 3
  • So, what is the benefit of the `SELECT` statement when `UPDATE` will lock the rows while performing the update? – Morey Jun 08 '23 at 20:07
  • @Morey [This answer](https://stackoverflow.com/a/33796030/1106272) puts it quite succinctly. Basically you're preventing other sessions from reading the row until you're done with it. This means that no other queries can prevent you from updating the row after selecting it, which could sometimes lead to a deadlock. – Steen Schütt Jul 20 '23 at 13:26