Primary index lock on duplicate key error

[RONALDS BREIKSS]

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?