Thiết kế website giá rẻ

Question

Database Info

It looks like Mysql or MariaDb 5.6.16, I am not sure

Welcome to the MariaDB monitor. Commands end with ; or g.
Your MySQL connection id is 12297574
Server version: 5.6.16-log Source distribution

Table Info

There are two unique index

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` int(11) NOT NULL DEFAULT '0',
  `cid` bigint(20) DEFAULT NULL,
  `uid` varchar(32) DEFAULT NULL,
  `flag` int(11) NOT NULL DEFAULT '0',
  `cid_create_time` datetime DEFAULT NULL,
  `uid_create_time` datetime DEFAULT NULL,
  `cid_delete_time` datetime DEFAULT NULL,
  `uid_delete_time` datetime DEFAULT NULL,
  `last_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_cid` (`cid`) USING BTREE,
  UNIQUE KEY `udx_uid` (`uid`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4

Due to space limitations, I omitted some of the preceding data, and cid column and uid column is sorted

+-----+---------------------+---------------------+
| id  | cid                 | uid                 |
+-----+---------------------+---------------------+
....
....
| 201 | 1774864160186843136 | 1774864180210307072 |
| 202 | 1774864160547553280 | 1774864180856229888 |
| 205 | 1774866745815875584 | 1774866761200439296 |
| 206 | 1774866746168197120 | 1774866787490336768 |
| 393 | 1774866813629382656 | NULL                |
| 394 | 1774866813973315584 | NULL                |
+-----+---------------------+---------------------+

Operation steps

I start two transactions: session 1 and session 2

Step1

session1

INSERT INTO test(cid) SELECT 1774866813629382656 FROM (SELECT 1) vt LEFT JOIN test t on t.cid=1774866813629382656 WHERE t.cid is NULL;

-- generate id 395
INSERT INTO test(uid) SELECT '1774866837847150592' FROM (SELECT 1) vt LEFT JOIN test t on t.uid='1774866837847150592' WHERE t.uid is NULL;

Step2

session2

INSERT INTO test(cid) SELECT 1774866813973315584 FROM (SELECT 1) vt LEFT JOIN test t on t.cid=1774866813973315584 WHERE t.cid is NULL;

-- generate id 396
INSERT INTO test(uid) SELECT '1774866842418941952' FROM (SELECT 1) vt LEFT JOIN test t on t.uid='1774866842418941952' WHERE t.uid is NULL;

Step3

delete from test where id = 395
update test set cid = 1774866813629382656, uid = '1774866837847150592' where id = 393

After complete step3, I found there are two locks by query table INNODB_LOCKS

*************************** 1. row ***************************
    lock_id: 413652530:2348:5:165
lock_trx_id: 413652530
  lock_mode: S
  lock_type: RECORD
 lock_table: `extplugin`.`test`
 lock_index: udx_uid
 lock_space: 2348
  lock_page: 5
   lock_rec: 165
  lock_data: '1774866842418941952'
*************************** 2. row ***************************
    lock_id: 413652560:2348:5:165
lock_trx_id: 413652560
  lock_mode: X
  lock_type: RECORD
 lock_table: `extplugin`.`test`
 lock_index: udx_uid
 lock_space: 2348
  lock_page: 5
   lock_rec: 165
  lock_data: '1774866842418941952'

I feel so confused why two session can hold X lock ans S lock on a same record?

Step4

delete from test where id = 396
update test set cid = 1774866813973315584, uid = '1774866842418941952' where id = 394

After step4 complete, dead lock happened

And I get dead lock information by command show engine innodb status

2024-05-16 14:39:51 7fba89b79700
*** (1) TRANSACTION:
TRANSACTION 413655288, ACTIVE 106.044 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 3
LOCK BLOCKING MySQL thread id: 12294698 block 12294693
MySQL thread id 12294693, OS thread handle 0x7fba8b1ab700, query id 1146997122 192.168.31.209 dbwrite updating
update test set cid = 1774866813629382656, uid = '1774866837847150592' where id = 393
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2348 page no 5 n bits 368 index `udx_uid` of table `extplugin`.`test` trx id 413655288 lock mode S waiting
Record lock, heap no 165 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 19; hex 31373734383636383432343138393431393532; asc 1774866842418941952;;
 1: len 8; hex 8000000000000191; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 413655343, ACTIVE 98.640 sec updating or deleting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 3
MySQL thread id 12294698, OS thread handle 0x7fba89b79700, query id 1146998311 192.168.31.209 dbwrite updating
update test set cid = 1774866813973315584, uid = '1774866842418941952' where id = 394
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2348 page no 5 n bits 368 index `udx_uid` of table `extplugin`.`test` trx id 413655343 lock_mode X locks rec but not gap
Record lock, heap no 165 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 19; hex 31373734383636383432343138393431393532; asc 1774866842418941952;;
 1: len 8; hex 8000000000000191; asc         ;;

 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2348 page no 5 n bits 368 index `udx_uid` of table `extplugin`.`test` trx id 413655343 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 165 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 19; hex 31373734383636383432343138393431393532; asc 1774866842418941952;;
 1: len 8; hex 8000000000000191; asc         ;;

I don’t understand how deadlocks happen? I don’t think there is a data conflict between the two transactions