SELECT FOR UPDATE for locked queries

Question

I'm using MySql 5.x and in my environment, I have a table with the name CALLS.

Table CALLS has a column status which takes an enum {inprogress, completed}.

I want reads/updates of the table to be row-locked, so:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET AUTOCOMMIT = 0;
SELECT amount from CALLS where callId=1213 FOR UPDATE;
COMMIT

Basically I'm doing a FOR UPDATE even in situations whereby I only need to read the amount and return. I find that this allow me to ensure that reads/updates are prevented from interfering from each other. However I've been told this will reduce the concurrency of the app.

Is there anyway to achieve the same transaction consistency without incurring locking overheads ? Thanks.

Answer 1

What you've implemented there--in case you weren't familiar with it--is called pessimistic locking. You're sacrificing performance for consistency, which is sometimes a valid choice. In my professional experience, I've found pessimistic locking to be far more of a hindrance than a help.

For one thing, it can lead to deadlock.

The (better imho) alternative is optimistic locking, where you make the assumption that collisions occur infrequently and you simply deal with them when they happen. You're doing your owrk in a transaction so a collision shouldn't leave your data in an inconsistent state.

Here's more information on optimistic locking in a Java sense but the ideas are applicable to anything.

Answer 2

Disclaimer: MySQL is generally full of surprises, so the following could be untrue.

What you are doing doesn't make any sense to me: You are committing after the SELECT, which should break the lock. So in my opinion, your code shouldn't really incur any significant overhead; but it doesn't give you any consistency improvements, either.

In general, SELECT FOR UPDATE can be a very sound and reasonable way to ensure consistency without taking more locks than are really needed. But of course, it should only be used when needed. Maybe you should have different code paths: One (using FOR UPDATE) used when the retrieved value is used in a subsequent change-operation. And another one (not using FOR UPDATE) used when the value doesn't have to be protected from changes.