16

How does one correctly implement optimistic locking in MySQL?

Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but we'd like to validate that this is the best way to do it.

  1. Create a version field on the table you want to use optimistic locking for e.g. column name = "version"
  2. On selects, make sure to include the version column and make note of the version
  3. On a subsequent update to the record, the update statement should issue "where version = X" where X is the version we received in #2 and set the version field during that update statement to X + 1
  4. Perform a SELECT FOR UPDATE on the record we are going to update so that we serialize who can make changes to the record we are trying to update.

To clarify, we are trying to prevent two threads which select the same record in the same time window where they grab the same version of the record from overwriting each others if they were to try and update the record at the same time. We believe that unless we do #4, there is a chance, that if both threads enter their respective transactions at the same time (but have not issued their updates yet), when they go to update, the second thread that will use the UPDATE ... where version = X will be operating on old data.

Are we correct in thinking we must do this pessimistic locking when updating even though we are using version fields/optimistic locking?

5
  • What's the problem? You increase the version number with your UPDATE, then the second UPDATE will fail because the version number isn't the same as when it was read - which is what you want.
    – AndreKR
    Nov 17, 2012 at 0:27
  • Are you certain? Its unclear that unless you set the transaction isolation level to a specific setting that you would actually see the other threads update. If you both enter the transaction at the same time, the second thread can very well see the OLD data when it goes to do the update. MySQL is not as robust in the ACID arena as say Oracle, hence looking for the best practices way to implement optimistic locking in MySQL that will prevent dirty reads/updates. Nov 17, 2012 at 0:40
  • But then the transaction will fail anyway during commit, right?
    – AndreKR
    Nov 17, 2012 at 0:42
  • Indications are that one would want to do a select for update in order to deal with this situation: dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html Nov 17, 2012 at 4:01
  • @BestPractices You need either SELECT ... FOR UPDATE or optimistic locking by row versioning, not both. See detail in answer. Nov 19, 2012 at 1:16

2 Answers 2

26

Your developer is mistaken. You need either SELECT ... FOR UPDATE or row versioning, not both.

Try it and see. Open three MySQL sessions (A), (B) and (C) to the same database.

In (C) issue:

CREATE TABLE test(
    id integer PRIMARY KEY,
    data varchar(255) not null,
    version integer not null
);
INSERT INTO test(id,data,version) VALUES (1,'fred',0);
BEGIN;
LOCK TABLES test WRITE;

In both (A) and (B) issue an UPDATE that tests and sets the row version, changing the winner text in each so you can see which session is which:

-- In (A):

BEGIN;
UPDATE test SET data = 'winnerA',
            version = version + 1
WHERE id = 1 AND version = 0;

-- in (B):

BEGIN;
UPDATE test SET data = 'winnerB',
            version = version + 1
WHERE id = 1 AND version = 0;

Now in (C), UNLOCK TABLES; to release the lock.

(A) and (B) will race for the row lock. One of them will win and get the lock. The other will block on the lock. The winner who got the lock will proceed to change the row. Assuming (A) is the winner, you can now see the changed row (still uncommitted so not visible to other transactions) with a SELECT * FROM test WHERE id = 1.

Now COMMIT in the winner session, say (A).

(B) will get the lock and proceed with the update. However, the version no longer matches, so it will change no rows, as reported by the row count result. Only one UPDATE had any effect, and the client application can clearly see which UPDATE succeeded and which failed. No further locking is necessary.

See session logs at pastebin here. I used mysql --prompt="A> " etc to make it easy to tell the difference between sessions. I copied and pasted the output interleaved in time sequence, so it's not totally raw output and it's possible I could've made errors copying and pasting it. Test it yourself to see.


If you had not added a row version field, then you would need to SELECT ... FOR UPDATE to be able to reliably ensure ordering.

If you think about it, a SELECT ... FOR UPDATE is completely redundant if you're immediately doing an UPDATE without re-using data from the SELECT, or if you're using row versioning. The UPDATE will take a lock anyway. If someone else updates the row between your read and subsequent write, your version won't match anymore so your update will fail. That's how optimistic locking works.

The purpose of SELECT ... FOR UPDATE is:

  • To manage lock ordering to avoid deadlocks; and
  • To extend the span of a row lock for when you want to read data from a row, change it in the application, and write a new row that's based on the original one without having to use SERIALIZABLE isolation or row versioning.

You do not need to use both optimistic locking (row versioning) and SELECT ... FOR UPDATE. Use one or the other.

6
  • Thanks Craig. You were correct-- the developer was mistaken. Thanks for running this test. Nov 19, 2012 at 15:58
  • What about SQL server? Is there always a lock acquired on the updated row independently of the transaction isolation level?
    – plalx
    Nov 19, 2014 at 20:49
  • @plalx Well, what does the documentation say? What happens if you run an interactive test just like this one? Nov 19, 2014 at 23:27
  • @CraigRinger, what will happen if B get the lock before A commit but after A update?
    – MengT
    Jan 17, 2017 at 22:02
  • 1
    @MengT It can't, that's why it's a lock. Jan 18, 2017 at 20:48
0
UPDATE tbl SET owner = $me,
               id = LAST_INSERT_ID(id)
    WHERE owner = ''
    LIMIT 1;
$id = SELECT LAST_INSERT_ID();
Do some stuff (arbitrarily long time)...;
UPDATE  tbl SET owner = '' WHERE id = $id;

No Locks (not table, not transaction) needed, or even desired:

  • UPDATE is atomic
  • LAST_INSERT_ID() is session-specific, hence thread-safe.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.