SQL concurrent 'select then update' pattern with READ_COMMITED isolation level

Question

We have a clustered Java application running over a mysql 5.5 database, InnoDb engine. The app uses Spring with transaction isolation level set to SERIALIZABLE.

Inside a method, which might be executed concurrently, either in different threads of one node or in separate nodes, there's a typical SELECT-then-UPDATE pattern. Here's the pseudo-code:

old_status = null;
do {
    old_status = SELECT status 
                 FROM bookings 
                 WHERE code=123;

    affected_rows = UPDATE bookings 
                    SET status=<new_status> 
                    WHERE code=123 AND status=<old_status>;

} while (affected_rows == 0);

// Now we can do stuff with <old_status> value

Field code is the PK; <old_status> and <new_status> values are always different.

The SELECTand the UPDATE run on separate transactions.

This works pretty well. I even think that the AND status=<old_status> checking in the WHERE clause of the UPDATE statement is unnecesary, since transaction isolation level is SERIALIZABLE, meaning that it would be safe to issue the SELECT and then the UPDATE without checking the value of the status field.

So, first question: (1) Am I OK in that all this is unnecesary when transaction isolation level is SERIALIZABLE?

Now, what happens is that a few days ago, the DBA suddenly materialized in my workplace and came with arguments and metrics that 'proved' we should start to use READ_COMMITED transaction isolation level. As he almost never leaves his catacomb, I immediately knew that he was being serious. He said that if we changed transaction isolation level from SERIALIZABLE to READ_COMMITED, cpu's usage would descend by 10% and that queries would run lot way faster, etc. In short, he had his arguments to make us switch to READ_COMMITED transaction isolation level.

So, second question: (2) Can we happily switch to READ_COMMITED transaction isolation level, given we are already checking the value of the status field in the WHERE clause of the UPDATE sentence by means of AND status=<old_status>?

I think we can, but would like to check with the community first. Thanks in advance!

Note: I can't use native constructs, such as mysql's SELECT ... FOR UPDATE, etc.

Is the 10% speedup noticeable by mere mortals, or just by some tool? In other words, is the system so close to melting down that 10% is worth chasing? I ask it this way because you may have bigger scaling issues, and possibly bigger gains to be had. — Rick James, Feb 22 '15 at 19:44
How many rows are changed by the update? Sounds like 1. Or, rephrasing, is `code` unique in that table? Also, how many times per second do you run this UPDATE? — Rick James, Feb 22 '15 at 19:45
Please explain why you cannot use `SELECT ... FOR UPDATE`. Might you be allowed to use a Stored Procedure (that could contain that statement)? — Rick James, Feb 22 '15 at 19:48
Do you know `old_status` before starting this code? Do you need the value of `old_status` after executing the UPDATE? (I am questioning the existence of the SELECT all together.) — Rick James, Feb 22 '15 at 19:49
@Rick regarding 10% speedup, I must trust the DBA. If he says it's worth giving this a try, then we'll do it. But we'd like to know the implications of this change in advance. — fps, Feb 22 '15 at 20:17
@Rick rows changed by the update are either 0 or 1. code is unique, actually. — fps, Feb 22 '15 at 20:20
Frequency of the update is low. It could happen to be a peak of 10 updates per second. That's our bound. — fps, Feb 22 '15 at 20:21
No select for update or stored procedures because the DBA would hang us. For SP we don't even have permissions. The DBA loves to keep everything standard. He hates locks, etc. I don't fully understand his reasons. For us (development team ) this is just a constraint. — fps, Feb 22 '15 at 20:26
And @Rick yes. We do need old_status after the update. Status is a string that maps to an enum. So we know all possibilities, however we do not know the exact value prior to the select. — fps, Feb 22 '15 at 20:30
The DBA needs to understand that locks are absolutely necessary and happen on _every_ SQL statement. The UPDATE will grab an exclusive lock on the row. The failure to get a lock by using "FOR UPDATE" on the SELECT could to data corruption or application errors. Given that you have a UNIQUE(code) constraint, only one row is locked. I assume the table is InnoDB?? — Rick James, Feb 22 '15 at 21:16

score 1 · Answer 1 · answered Feb 23 '15 at 21:12

First of all. In serialized isolation to compare the old status is unnecessary. A serialized transaction will fail if a read lock is overwritten by a write lock of another transaction on the same data element or the write lock is delayed depending on the implementation used within the database.

Also I highly doupt the 10% claim for read committed.

I am also no friend of read committed. I worked in an insurance company where the new system uses Read Committed all over. They used Select for update. I as a guy trained in transaction management and databases (main subject at the university) I highly dont like using read committed isolation for write transactions. For read transactions using only a select its ok thou but where is the point.

I think that read committed only saves you undo information (maybe) and will reduce the chance that a transaction needs to be rolled back (douptful).

There is also a third suggestion. Use two datasources. One using the read committed isolation level and the second uses the serialized. Use the second to do all write transactions and the first one doing the rest.

But yet again you have all sorts of trouble. Since using read committed there is the chance that the same data fail. And as I know by myself 10% is nothing compared to all the bugs engineers craft into transactional code once they are unaware of a new restriction.

There is only one misassumption needed inside your collections of hundreds and thousands of transactions and you are likely to face a bug that will costs you tens to the thousands hours of searching. I know defect which were never found until we record the isolation level and saw the missing piece in the puzzle.

Writing transactions is easy but getting it right is very hard.

So my advice: as long as you do not have any performance issues regarding 10% of your database do not change isolation level. If you have performance issues upgrade your hardware. Hardware is cheap as long as it doesnt come from IBM, Oracle or HP or whatever.

And remember 100 hours of your time and you can add one TB of RAM into your maschine (or 100GB if it comes from IBM, Oracle or HP or whatever) and those 100GB will boost your performance more than the isolation level.

Dont mess with it unless you need it or you are a guru. For the insurance software, they used SQL rows for pessimistic locking and used select for update. Each were performance killers since they are bottle necks.

Your solution is good but you do not need the compare.

The best solution would be:

` [begin read transaction] select * .... [/end read transaction]

calculate change

[begin write transaction] compare and set by using update ... where select ... [end write transaction] `

I think you do this transaction handling within your while loop otherwise your code would be not so good :).

You're right. I double-checked and we aren't performing the select and the update in the same transaction. My bad, I'll edit the question to clarify this. — fps, Feb 23 '15 at 22:55

score 1 · Accepted Answer · answered Feb 24 '15 at 22:35

If both are in separate transactions you need the comparing the old value. What you do is a simple technique in optimistic application transactions it is called compare and set. So yeah you are free to go with that.

About the Read committed isolation level its only interesting within a transaction. If you have only a single select or update it shouldnt make any difference at all if I understood Oracles implementation correctly. Oracle access a single row in a table only once and will reuse those information already got. But their might be situations it will not be able to reuse those information (terms of out of session cache etc).

So it will require a high level of understanding and should not be done without considering each and every transaction regarding the effects of certain change and I mean all potential change. So if you want this done right you can see your costs skyrocket.

I would never change the isolation level easy especially if you havent designed for it.

SQL concurrent 'select then update' pattern with READ_COMMITED isolation level

2 Answers2