一.lock 和 latchlatch 一般稱為閂鎖，目的是用來(lái)保證并發(fā)線程操作臨界資源的正確性，無(wú)死鎖檢測(cè)機(jī)制。分為：mutex(互斥量)，rwlock(讀寫(xiě)鎖)
lock 的對(duì)象是事務(wù)，用來(lái)鎖定的數(shù)據(jù)庫(kù)中的對(duì)象。
二.lock 種類1.行級(jí)鎖
共享鎖(S lock)：允許事務(wù)讀取一行數(shù)據(jù)
排他鎖(X lock)：允許事務(wù)修改或刪除一行數(shù)據(jù)
2.表級(jí)鎖
意向共享鎖(IS lock):在對(duì)行加S鎖之前，先對(duì)其表追加IS鎖
意向排他鎖(IX lock):在對(duì)行加X(jué)鎖之前，先對(duì)其表追加IX鎖
表級(jí)意向鎖和行級(jí)鎖的兼容性：
三.鎖的應(yīng)用場(chǎng)景1.一致性非鎖定讀
2.一致性鎖定讀通過(guò)select * from table for update; 或 select * from table lock in share mode; 來(lái)鎖定讀取數(shù)據(jù)，在數(shù)據(jù)讀取過(guò)程中其他事務(wù)不能修改該數(shù)據(jù)。
3.自增長(zhǎng)與鎖含有自增長(zhǎng)值的表，都有一個(gè)自增長(zhǎng)計(jì)數(shù)器，當(dāng)對(duì)該表進(jìn)行插入操作時(shí)，執(zhí)行如下語(yǔ)句來(lái)得到計(jì)數(shù)器的值。
select max(auto_inc_col) from table for update;
該鎖不是事務(wù)完成之后才釋放，而是insert命令執(zhí)行完成后就釋放該鎖。
MySQL5.1.22之前，該模式對(duì)于有自增值列的表的并發(fā)插入性能較差。
mysql5.1.22開(kāi)始，innodb提供了一種輕量級(jí)的互斥量的自增長(zhǎng)實(shí)現(xiàn)機(jī)制，這種機(jī)制大大提高了自增長(zhǎng)值的插入性能。
相關(guān)參數(shù)，innodb_autoinc_lock_mode,默認(rèn)值為1
4.外鍵和鎖在innodb存儲(chǔ)引擎下，外鍵列如果沒(méi)有顯式的建立index，mysql會(huì)為該列自動(dòng)添加index，避免發(fā)生表鎖。
對(duì)于外鍵值的插入和更新，會(huì)先select父表，但該select操作并不是一致性非鎖定讀，而是一致性鎖定讀（對(duì)父表追加S鎖）。因此當(dāng)父表被其他事務(wù)加上X鎖時(shí)，子表的操作會(huì)被阻塞。
--主表
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> 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
單個(gè)記錄上的鎖
2.gap lock
間隙鎖，鎖定一個(gè)范圍，但不包含記錄本身
3.next-key lock
record lock+gap lock 鎖定一個(gè)范圍，但不包含記錄本身
--場(chǎng)景模擬
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 |
+------+------+
唯一索引的鎖定范圍
因?yàn)閕dt3上有唯一索引，因此鎖定的只是idt3=3這個(gè)值，而不是(1,3)這個(gè)范圍，即鎖定由next-key lock降級(jí)為record lock
輔助索引的鎖定范圍
五.一條sql的加鎖范圍對(duì)于各種情況下加鎖的分析
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 --殺掉正在執(zhí)行的語(yǔ)句，保留連接
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, #被阻塞方事務(wù)id
r.trx_mysql_thread_id BlockedThreadId, #被阻塞方線程號(hào)
TIMESTAMPDIFF(
SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) WaitTime, #等待時(shí)間
r.trx_query BlockedQuery, #被阻塞的查詢
l.lock_table BlockedTable, #阻塞方鎖住的表
m.`lock_mode` BlockedLockMode, #被阻塞方的鎖模式
m.`lock_type` BlockedLockType, #被阻塞方的鎖類型(表鎖還是行鎖)
m.`lock_index` BlockedLockIndex, #被阻塞方鎖住的索引
m.`lock_space` BlockedLockSpace, #被阻塞方鎖對(duì)象的space_id
m.lock_page BlockedLockPage, #被阻塞方事務(wù)鎖定頁(yè)的數(shù)量
m.lock_rec BlockedLockRec, #被阻塞方事務(wù)鎖定行的數(shù)量
m.lock_data BlockedLockData, #被阻塞方事務(wù)鎖定記錄的主鍵值
p.`HOST` blocking_host, #阻塞方主機(jī)
p.`USER` blocking_user, #阻塞方用戶
b.trx_id BlockingTrxid, #阻塞方事務(wù)id
b.trx_mysql_thread_id BlockingThreadId, #阻塞方線程號(hào)
b.trx_query BlockingQuery, #阻塞方查詢
l.`lock_mode` BlockingLockMode, #阻塞方的鎖模式
l.`lock_type` BlockingLockType, #阻塞方的鎖類型(表鎖還是行鎖)
l.`lock_index` BlockingLockIndex, #阻塞方鎖住的索引
l.`lock_space` BlockingLockSpace, #阻塞方鎖對(duì)象的space_id
l.lock_page BlockingLockPage, #阻塞方事務(wù)鎖定頁(yè)的數(shù)量
l.lock_rec BlockingLockRec, #阻塞方事務(wù)鎖定行的數(shù)量
l.lock_data BlockingLockData, #阻塞方事務(wù)鎖定記錄的主鍵值
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx #阻塞方事務(wù)空閑的時(shí)間
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;
參考書(shū)籍:
MySQL技術(shù)內(nèi)幕：InnoDB存儲(chǔ)引擎
成都創(chuàng)新互聯(lián)公司從2013年成立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元豐潤(rùn)做網(wǎng)站,已為上家服務(wù),為豐潤(rùn)各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:028-86922220
名稱欄目：mysqlinnodblock機(jī)制原理
轉(zhuǎn)載源于：
http://weahome.cn/article/jjiopc.html
重慶分公司
重慶分公司
