I have a java app running on MySQL, Hibernate, and Spring-managed transactions.
There's one specific transaction that worries me a little. I tried to set up some concurrent testing environment (using ApacheBench), tests seem ok, but I still have some doubts...
Transaction logic is along these lines (using mixed sql + pseudo-code, for brevity):
updated = 0;
// lock counter row, if exists
selected = select x from tbl1 where [composite PK match] FOR UPDATE;
if(selected == null) {
// Record does not exist... I thought this might be a good idea, as there will be only
// one insert for the given PK daily and many subsequent updates (rather do this than
// forcing 'insert...on duplicate key update' constantly?)
updated = insert into tbl1 values(...) on duplicate key update cnt = cnt + 1;
} else {
// Record exists, just increment the counter.
updated = update tbl1 set cnt = cnt + 1;
}
return updated;
Table tbl1
is like this:
create table tbl1 (
`a_id` int(11) NOT NULL,
`b_id` int(11) NOT NULL,
`c_id` int(11) NOT NULL,
`day` date NOT NULL,
`cnt` int(11) DEFAULT '0',
PRIMARY KEY (`a_id`,`b_id`,`c_id`,`day`)
) ENGINE=InnoDB;
Transaction isolation is MySQL's default (REPEATABLE READ
).
The most important concern for me is:
- Is the above code valid candidate to cause deadlocks in a highly concurrent environment?
...and I'd also like to add two related sub-questions:
Is incrementing a counter on this single table:
update tbl1 set c = c + 1 where [match row by composite PK]
thread-safe / atomic / consistent ?
According to my tests, it is. According to the existing question, it may depend... and if it is, I guess the above logic can be further optimized/simplified so it does not useselect...for update
(I added it just to be on the safe side).If
select...where...for update
is executed within a transaction as a very first statement, but no record matchedWHERE
clause, can this possibly cause some hidden row-level locking before subsequent DML statements may be executed with the same matching condition (as in the example), and how this may affect other concurrent transactions?