MySQL Forums
Forum List  »  InnoDB

deadlock of gap lock upgrade to insert attention lock
Posted by: BB DD
Date: November 18, 2020 05:45AM

how can i insert a absolute new data in concurrent trx.
my idea like this example:

##trx1 and trx2
begin;
##trx1
select max(id) from `test_lock` where (`aid` = 10) limit 1 for update;
##trx2(have blocked)
select max(id) from `test_lock` where (`aid` = 10) limit 1 for update;
##trx1
insert into `test_lock` (`aid`) values(10);
##then trx2 will gave a deadlock error and look that error even not need to commit


//prepared:
CREATE TABLE `test_lock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`aid` int(11) NOT NULL,
`otherinfo` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `aid` (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
insert into `test_lock` (`aid`) values(10);

Options: ReplyQuote


Subject
Views
Written By
Posted
deadlock of gap lock upgrade to insert attention lock
288
November 18, 2020 05:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.