Xrhrft

I have a concurrency problem where 2 (or more) processes are making inserts at the same time and are all locking the same index page (I guess) among with a on a SELECT FOR UPDATE clause

Here is a reproducible sample:

CREATE TABLE U ( -- user

    id int not null primary key,

    name varchar(222)

);



CREATE TABLE O ( -- object

    id int not null primary key,

    name varchar(222)

);



CREATE TABLE OU ( -- user <-> object

    id int not null auto_increment primary key,

    object_id int,

    user_id int,



    -- (object_id, user_id) should be UNIQUE (not enforced by a constraint)

    CONSTRAINT fk_object_id FOREIGN KEY (object_id) REFERENCES O(id) ON DELETE CASCADE,

    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES U(id) ON DELETE CASCADE

);



INSERT INTO U VALUES (1, 'foo'), (2, 'bar');



-- Start 2 concurrent transactions (A, B)

BEGIN; -- A

BEGIN; -- B

INSERT INTO O VALUES (1, 'a'); -- A

INSERT INTO O VALUES (2, 'b'); -- B

SAVEPOINT s_a; -- A

SAVEPOINT s_b; -- B

SELECT * FROM OU WHERE object_id = 1 FOR UPDATE; -- A

SELECT * FROM OU WHERE object_id = 2 FOR UPDATE; -- B

INSERT INTO OU(object_id, user_id) VALUES (1, 1); -- A

INSERT INTO OU(object_id, user_id) VALUES (2, 1); -- B

one of the transactions will fail with a Deadlock found when trying to get lock; try restarting transaction error.

The problem is that it rolls back the whole transaction without any way for me to gracefully handle the error.

In that example, the object 2 is lost. I would really like for it to still persist and handle the second insert a few seconds / minutes after another transaction.

The way the code is currently designed does not allow fine tuning of the transaction block (I cannot commit after the first insert then start another transaction)

Is there any way to tell MySQL to not rollback on that deadlock error, or to rollback specifically to the savepoint?

edit here is the innodb engine status

*** (1) TRANSACTION:

TRANSACTION 3727668996, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1

MySQL thread id 20145497, OS thread handle 0x7f4fdba2b700, query id 15334836096 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.33 user1 update

INSERT INTO `Tablename` (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655988, 1, 26399, NULL, '2017-04-20 19:04:21')

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 201 page no 1012 n bits 840 index `product_id` of table `dbname`.`Tablename` trx id 3727668996 lock_mode X locks gap before rec insert intention waiting

*** (2) TRANSACTION:

TRANSACTION 3727668998, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1

MySQL thread id 20145647, OS thread handle 0x7f5d6d253700, query id 15334836100 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.35 user1 update

INSERT INTO `Tablename` (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655991, 1, 26399, NULL, '2017-04-20 19:04:21')

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 201 page no 1012 n bits 840 index `product_id` of table `dbname`.`Tablename` trx id 3727668998 lock_mode X locks gap before rec

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 201 page no 1012 n bits 840 index `product_id` of table `dbname`.`Tablename` trx id 3727668998 lock_mode X locks gap before rec insert intention waiting

*** WE ROLL BACK TRANSACTION (2)

First, some observations/comments/requests:

• Please provide SHOW ENGINE INNODB STATUS;, in case it sheds any further light on the issue.


• I usually find that SAVEPOINT can be avoided by rethinking the entire transaction.


• Since SAVEPOINT seems to be critical to the problem, and it seems to be "broken", please file a bug at http://bugs.mysql.com .


• Deadlocks will happen, no matter what you do to avoid them. It is best to "live with them". That is, catch them, then rerun the entire transaction. (SAVEPOINT complicates the code for such.)

On the constructive side, I would recommend improving the many:many OU table by the tips in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table -- In particular, its id is both wasteful and possibly contributing to the problem.