mysql資料庫的鎖session_Mysql 資料庫鎖的一個問題

13

131 天前

我覺得會死鎖啊，會競争 X 鎖啊。你開兩個事務，start transaction 手動測試就知道了啊。

看之前的回答我還懷疑自己，測試了下：

Trx id counter 101931

Purge done for trx's n:o < 101930 undo n:o < 0 state: running but idle

History list length 11

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 101930, ACTIVE 16 sec

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

MySQL thread id 13, OS thread handle 19224, query id 835 localhost ::1 root starting

show engine innodb status

---TRANSACTION 101928, ACTIVE 107 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)

MySQL thread id 14, OS thread handle 1736, query id 834 localhost ::1 root Searching rows for update

update user set id = 1 where name = 'aa'

------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 489 page no 3 n bits 72 index PRIMARY of table `orange`.`user` trx id 101928 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0