jpa hibernate deadlock with simple find update-CodePudding

I'm frequently having deadlock when 2 transactions are doing:

entitymanager.find by id, no lock mode in particular
entitymanager.merge, no lock mode in particular

They are all under @Transactional and the default isolation is repeatable read, under mysql 5.7. The entity as id PK autoincrement as commonly used. No @Version if that matters...

What happens is this:

txn A finds the row
txn B finds the row
txn A tries to update and thus escalate to an exclusive X lock but waits because there seems to be a shared (S) (read) lock on the row from txn B
txn B tries to update and thus escalate to an exclusive X lock but it is after txn A, which is held back by B itself. Now this is detected as a deadlock so one of these txn will rollback.

The SHOW ENGINE INNODB STATUS reveals the last detected deadlock. It clearly says there were shared (S) locks.

What is surprising: I enabled the hibernate DEBUG level on org.hibernate.SQL to see the statements, and NONE of them show any "select ... lock in share mode" (nor select ... for update).

(I've gone the extra mile and packet-sniffed the mysql protocol over port 3306 with wireshark, and not a single hint of special locking mode, nor any session variable other than the usual "set session transaction read write" vs "... read only" from time to time, which has no effect on locking).

There is enough time between steps 1 and 3 obviously for txn B to sneak in. So I presume this shared lock is not a momentary effect of the update statement. We wouldn't get deadlocks that easily if that was the case. So I presume the shared lock comes from the "find".

The question is where is this configured? For all the docs I read, the default lock mode is LockMode.NONE.

If I write raw sql in 2 sessions, like below, (and using transaction read write mode, the default) I don't get the deadlock:

txnA: select * from foo where id = 1;
txnB: select * from foo where id = 1;
txnA: update foo set x=x 1 where id = 1;
txnB: update foo set x=x 1000 where id = 1;

but if I write this, then I get the same deadlock:

txnA: select * from foo where id = 1 lock in share mode ;
txnB: select * from foo where id = 1 lock in share mode ;
txnA: update foo set x=x 1 where id = 1;
txnB: update foo set x=x 1000 where id = 1;

Now, I don't want to use a X (or U) lock in the find, as mentioned in How do UPDATE locks prevent a common form of deadlock?.

I want to just lock less, as the raw SQL seems to allow. So again, the question is where is this configured? why is this shared lock requested? How does hibernate even get to that point if none of the sql statement I see in the sniffed packets even hint at those shared locks?

Thanks.

CodePudding user response：

What is surprising: I enabled the hibernate DEBUG level on org.hibernate.SQL to see the statements, and NONE of them show any "select ... lock in share mode" (nor select ... for update). (...) The question is where is this configured? For all the docs I read, the default lock mode is LockMode.NONE.

The default locking behaviour is configured in your RDBMS, of course. It is also known as isolation level. You said yours was set to REPEATABLE READ, so both read locks and write locks are maintained till the end of transaction.

You seem to be confused about expecting no locks to be in use unless explicitly requested. This is not at all how it works. A SELECT statement always acquires a read lock, and an UPDATE statement always acquires a write lock. The million dollar question is when those locks get released, and that's what isolation levels control.

I want to just lock less

Well then, switch to a more lenient isolation level - READ COMMITED, if your use case doesn't care about non-repeatable reads.

Or, if there's little contention over DB rows and your update operations are relatively inexpensive, use optimistic locking, like @roccobaroccoSC suggested.

Or, if contention changes dynamically, try a hybrid approach: first, try optimistic locking n times, and if that fails, use pessimistic locking, with upfront calls to em.find(..., LockMode.PESSIMISTIC).

CodePudding user response：

Your expectations about nonlocking reads are correct and documentation clearly states the same: https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html, however I do believe that your analysis of the situation is incomplete - when you are performing tracing you might overlook some statements which look irrelevant for you, but from InnoDB perspective such statements might make sense, e.g. consider the following DB structure:

mysql> create table t(id int(11), v int(11));
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> insert into t(id,v) primary key, values(1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t;
 ------ ------ 
| id   | v    |
 ------ ------ 
|    1 |    1 |
|    2 |    2 |
 ------ ------ 
2 rows in set (0.00 sec)

and the next statement actually locks the whole table due to "missing" index on v column:

mysql> update t set v=4 where id<2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show engine innodb status;
...
---TRANSACTION 3672, ACTIVE 2 sec
3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1

which is counterintuitive for us, but described in documentation: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html:

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.