mysql innodb lock機制原理

2024-07-24 12:34:57

字體：大中小

來源：轉載

供稿：網友

mysql innodb lock機制原理：

　　一.lock 和 latch
　　latch 一般稱為閂鎖，目的是用來保證并發線程操作臨界資源的正確性，無死鎖檢測機制。分為：mutex(互斥量)，rwlock(讀寫鎖)
　　lock 的對象是事務，用來鎖定的數據庫中的對象。

　　二.lock 種類
　　1.行級鎖
　　共享鎖(S lock)：允許事務讀取一行數據
　　排他鎖(X lock)：允許事務修改或刪除一行數據
　　2.表級鎖
　　意向共享鎖(IS lock):在對行加S鎖之前，先對其表追加IS鎖
　　意向排他鎖(IX lock):在對行加X鎖之前，先對其表追加IX鎖

　　三.鎖的應用場景
　　1.一致性非鎖定讀

　　2.一致性鎖定讀
　　通過select * from table for update; 或 select * from table lock in share mode; 來鎖定讀取數據，在數據讀取過程中其他事務不能修改該數據。

　　3.自增長與鎖
　　含有自增長值的表，都有一個自增長計數器，當對該表進行插入操作時，執行如下語句來得到計數器的值。
　　select max(auto_inc_col) from table for update;
　　該鎖不是事務完成之后才釋放，而是insert命令執行完成后就釋放該鎖。

　　mysql5.1.22之前，該模式對于有自增值列的表的并發插入性能較差。
　　mysql5.1.22開始，innodb提供了一種輕量級的互斥量的自增長實現機制，這種機制大大提高了自增長值的插入性能。
　　相關參數，innodb_autoinc_lock_mode,默認值為1

　　4.外鍵和鎖
　　在innodb存儲引擎下，外鍵列如果沒有顯式的建立index，mysql會為該列自動添加index，避免發生表鎖。
　　對于外鍵值的插入和更新，會先select父表，但該select操作并不是一致性非鎖定讀，而是一致性鎖定讀（對父表追加S鎖）。因此當父表被其他事務加上X鎖時，子表的操作會被阻塞。

　　--主表
　　CREATE TABLE `wwj`.`t1` (
　　 `deptno` INT NOT NULL,
　　 `deptname` VARCHAR(45) NOT NULL,
　　 `address` VARCHAR(45) NOT NULL,
　　 PRIMARY KEY (`deptno`));

　　--子表
　　 CREATE TABLE `wwj`.`t2` (
　　 `empno` INT NOT NULL,
　　 `empname` VARCHAR(45) NOT NULL,
　　 `age` INT NOT NULL,
　　 `deptno` INT NOT NULL,
　　 PRIMARY KEY (`empno`),
　　 INDEX `deptno_idx` (`deptno` ASC),
　　 CONSTRAINT `deptno`
　　    FOREIGN KEY (`deptno`)
　　    REFERENCES `wwj`.`t1` (`deptno`)
　　    ON DELETE NO ACTION
　　    ON UPDATE NO ACTION);

　　insert into wwj.t1 values(1,'it','北京');
　　insert into wwj.t1 values(2,'product','天津');
　　insert into wwj.t1 values(3,'haha','上海');
　　mysql innodb lock機制原理

　　mysql> select * from information_schema.innodb_locks/G;
　　*************************** 1. row ***************************
　　lock_id: 1303:26:3:2
　　lock_trx_id: 1303
　　lock_mode: S
　　lock_type: RECORD
　　lock_table: `wwj`.`t1`
　　lock_index: PRIMARY
　　lock_space: 26
　　lock_page: 3
　　lock_rec: 2
　　lock_data: 1
　　*************************** 2. row ***************************
　　lock_id: 1298:26:3:2
　　lock_trx_id: 1298
　　lock_mode: X
　　lock_type: RECORD
　　lock_table: `wwj`.`t1`
　　lock_index: PRIMARY
　　lock_space: 26
　　lock_page: 3
　　lock_rec: 2
　　lock_data: 1
　　2 rows in set, 1 warning (0.00 sec)

　　四.鎖的算法
　　行鎖的三種算法：
　　1.record lock
　　單個記錄上的鎖

　　2.gap lock
　　間隙鎖，鎖定一個范圍，但不包含記錄本身

　　3.next-key lock
　　record lock+gap lock 鎖定一個范圍，但不包含記錄本身

　　--場景模擬
　　CREATE TABLE `wwj`.`t3` (
　　 `idt3` INT NOT NULL,
　　 `idt4` INT NOT NULL,
　　 PRIMARY KEY (`idt3`),
　　 INDEX `idx-1` (`idt4` ASC));

　　 insert into wwj.t3 values(1,10);
　　 insert into wwj.t3 values(3,30);
　　 insert into wwj.t3 values(5,50);

　　mysql> select * from wwj.t3;
　　+------+------+
　　| idt3 | idt4 |
　　+------+------+
　　| 1 | 10 |
　　| 3 | 30 |
　　| 5 | 50 |
　　+------+------+

　　因為idt3上有唯一索引，因此鎖定的只是idt3=3這個值，而不是(1,3)這個范圍，即鎖定由next-key lock降級為record lock

　　五.一條sql的加鎖范圍
　　對于各種情況下加鎖的分析
　　mysql> show full processlist;
　　+----+------+-----------+------+---------+------+----------+-----------------------+
　　| Id | User | Host | db | Command | Time | State | Info |
　　+----+------+-----------+------+---------+------+----------+-----------------------+
　　| 11 | root | localhost | NULL | Sleep | 99 | | NULL |
　　| 12 | root | localhost | NULL | Sleep | 81 | | NULL |
　　| 13 | root | localhost | NULL | Query | 0 | starting | show full processlist |
　　+----+------+-----------+------+---------+------+----------+-----------------------+
　　殺掉線程:
　　KILL [CONNECTION | QUERY] thread_id
　　kill 13 --殺掉線程連接
　　kill query 13 --殺掉正在執行的語句，保留連接

　　mysql> show engine innodb status/G;
　　------------
　　TRANSACTIONS
　　------------
　　Trx id counter 1296
　　Purge done for trx's n:o < 1294 undo n:o < 0 state: running but idle
　　History list length 2
　　LIST OF TRANSACTIONS FOR EACH SESSION:
　　---TRANSACTION 421324408397424, not started
　　0 lock struct(s), heap size 1136, 0 row lock(s)
　　---TRANSACTION 1295, ACTIVE 396 sec inserting
　　mysql tables in use 1, locked 1
　　LOCK WAIT 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
　　MySQL thread id 12, OS thread handle 139848225883904, query id 61 localhost root executing
　　insert into wwj.t3 select 4,20
　　------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
　　RECORD LOCKS space id 26 page no 4 n bits 72 index idx-1 of table `wwj`.`t3` trx id 1295 lock_mode X locks gap before rec insert intention waiting
　　Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
　　 0: len 4; hex 8000001e; asc ;;
　　 1: len 4; hex 80000003; asc ;;

　　---------------------
　　TRANSACTION 1294, ACTIVE 449 sec
　　4 lock struct(s), heap size 1136, 3 row lock(s)
　　MySQL thread id 11, OS thread handle 139848226150144, query id 50 localhost root
　　Trx read view will not see trx with id >= 1294, sees < 1294

　　mysql> select * from information_schema.INNODB_LOCK_WAITS;
　　+-------------------+-------------------+-----------------+------------------+
　　| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
　　+-------------------+-------------------+-----------------+------------------+
　　| 1302 | 1302:26:4:3 | 1301 | 1301:26:4:3 |
　　+-------------------+-------------------+-----------------+------------------+
　　1 row in set, 1 warning (0.00 sec)

　　mysql> select lock_id,lock_trx_id,lock_mode,lock_type,lock_table,lock_index from information_schema.INNODB_LOCKs;
　　+-------------+-------------+-----------+-----------+------------+------------+
　　| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index |
　　+-------------+-------------+-----------+-----------+------------+------------+
　　| 1302:26:4:3 | 1302 | X,GAP | RECORD | `wwj`.`t3` | idx-1 |
　　| 1301:26:4:3 | 1301 | X | RECORD | `wwj`.`t3` | idx-1 |
　　+-------------+-------------+-----------+-----------+------------+------------+
　　2 rows in set, 1 warning (0.00 sec)

　　SELECT
　　        p2.`HOST` Blockedhost, #被阻塞方host
　　        p2.`USER` BlockedUser, #被阻塞方用戶
　　        r.trx_id BlockedTrxId, #被阻塞方事務id
　　        r.trx_mysql_thread_id BlockedThreadId,      #被阻塞方線程號
　　        TIMESTAMPDIFF(
　　            SECOND,
　　            r.trx_wait_started,
　　            CURRENT_TIMESTAMP
　　        ) WaitTime,   #等待時間
　　        r.trx_query BlockedQuery,         #被阻塞的查詢
　　        l.lock_table BlockedTable,        #阻塞方鎖住的表
　　        m.`lock_mode` BlockedLockMode,    #被阻塞方的鎖模式
　　        m.`lock_type` BlockedLockType,    #被阻塞方的鎖類型(表鎖還是行鎖)
　　        m.`lock_index` BlockedLockIndex, #被阻塞方鎖住的索引
　　        m.`lock_space` BlockedLockSpace, #被阻塞方鎖對象的space_id
　　        m.lock_page BlockedLockPage,      #被阻塞方事務鎖定頁的數量
　　        m.lock_rec BlockedLockRec,        #被阻塞方事務鎖定行的數量
　　        m.lock_data BlockedLockData,      #被阻塞方事務鎖定記錄的主鍵值
　　        p.`HOST` blocking_host,           #阻塞方主機
　　        p.`USER` blocking_user,           #阻塞方用戶
　　        b.trx_id BlockingTrxid,           #阻塞方事務id
　　        b.trx_mysql_thread_id BlockingThreadId, #阻塞方線程號
　　        b.trx_query BlockingQuery,        #阻塞方查詢
　　        l.`lock_mode` BlockingLockMode,   #阻塞方的鎖模式
　　        l.`lock_type` BlockingLockType,   #阻塞方的鎖類型(表鎖還是行鎖)
　　        l.`lock_index` BlockingLockIndex, #阻塞方鎖住的索引
　　        l.`lock_space` BlockingLockSpace, #阻塞方鎖對象的space_id
　　        l.lock_page BlockingLockPage,     #阻塞方事務鎖定頁的數量
　　        l.lock_rec BlockingLockRec,       #阻塞方事務鎖定行的數量
　　        l.lock_data BlockingLockData,     #阻塞方事務鎖定記錄的主鍵值
　　       IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx #阻塞方事務空閑的時間
　　    FROM
　　        information_schema.INNODB_LOCK_WAITS w
　　    INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
　　    INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
　　    INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id`
　　      INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
　　    INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
　　 INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
　　    ORDER BY
　　        WaitTime DESC;

（編輯：武林網）

上一篇：GDB調試MySQL

下一篇：mysqldump備份時如何保持數據的一致性