1

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:

  1. 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:

  1. 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 use select...for update (I added it just to be on the safe side).

  2. If select...where...for update is executed within a transaction as a very first statement, but no record matched WHERE 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?

Community
  • 1
  • 1
Less
  • 3,047
  • 3
  • 35
  • 46

0 Answers0