MySQL Forums
Forum List  »  InnoDB

Primary index lock on duplicate key error
Posted by: RONALDS BREIKSS
Date: September 29, 2021 06:02AM

Hi,

So I'm having an issue understanding the mechanics behind the locking mechanism in following case.

I have a user_tag table that holds all user applicable tags and each user is intended to not have duplicate tags. When I hit duplicate key error check, all other users are not allowed to insert into the user_tag table.

CREATE TABLE user_tag
(
id int UNSIGNED AUTO_INCREMENT
PRIMARY KEY,
user_id int UNSIGNED NOT NULL,
tag varchar(128) NOT NULL,
description varchar(128),
CONSTRAINT project_tag_uk UNIQUE (user_id, tag)
);

# first transaction
START TRANSACTION;
INSERT INTO `user_tag` (`user_id`, `tag`)
VALUES (1, 'user1_tag1');

# Run second time to get duplicate key error
INSERT INTO `user_tag` (`user_id`, `tag`)
VALUES (1, 'user1_tag1');

/*
RECORD LOCKS space id 14 page no 4 n bits 72 index PRIMARY of table `mysql_test`.`user_tag` trx id 3706 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*/

# Seconds transaction

# This insert gets locked
START TRANSACTION;
INSERT INTO `user_tag` (`user_id`, `tag`)
VALUES (2, 'user2_tag2');



Could someone elaborate on this mechanism a bit more?

Options: ReplyQuote


Subject
Views
Written By
Posted
Primary index lock on duplicate key error
96
September 29, 2021 06:02AM


Sorry, only registered users may post in this forum.

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.