Bug #30661 Purging data can cause deadlocks

• Submitted: 28 Aug 2007 2:05
• Modified: 28 Aug 2007 16:06
• Reporter: Bill Weber
• Status: Not a Bug
• Category: MySQL Enterprise Monitor: Server
• Severity: S2 (Serious)
• Version: 1.2.0.7296
• OS: Any
• Assigned to: Sloan Childers
• CPU Architecture: Any

[28 Aug 2007 2:05] Bill Weber

Description:
When Merlin does a data purge, sometimes a deadlock occurs and you get the following:

java.lang.Exception: Encountered: com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

TRANSACTION 0 5094974, ACTIVE 22 sec, OS thread id 2132 inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, undo log entries 18
MySQL thread id 26, query id 16413 localhost 127.0.0.1 root update
INSERT INTO dc_integer VALUES (10,null,1188228233405,1188228233405)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 6186 n bits 392 index `PRIMARY` of table `merlin/dc_integer` trx id 0 5094974 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 168 PHYSICAL RECORD: n_fields 6; compact format; info bits 32

How to repeat:
- install build
- set data.lifespan in the merlin db to 1 day
- wait for more than 24 hours and stop/start tomcat

[28 Aug 2007 16:06] Bill Weber

Nothing to see here, move along...the "retry" mechanism we built into DC writing took care of this, we just logged the first failure attempt. Deadlocking w/ InnoDB in a highly-concurrent system is a fact of life, Merlin-1.2 deals with it now, at least for DC writes (1.1 did not).

The error message was confusing, as it was inserted the 1000-line "SHOW INNODB STATUS" in between two components, so I went ahead and changed that. The message will now look like this while retrying:

"Encountered deadlock, Retrying  n time (or times as appropriate, it's dynamic). Exception was: [innodb gobbledy gook here]"

_If_ the transaction ultimately fails, you'll get the following in the logs:

""Encountered deadlock, retried transaction n time (or times as appropriate). Giving up. Exception was: [innodb gobbledy gook here]"