查看連接信息 show processlist
我們提供的服務有:網(wǎng)站設計制作、做網(wǎng)站、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認證、榆林ssl等。為超過千家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務,是有科學管理、有技術(shù)的榆林網(wǎng)站制作公司
+----+------+------------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------------+------+---------+------+----------+------------------+
| 3 | root | 172.17.0.1:60542 | test | Query | 0 | starting | show processlist |
| 5 | root | 172.17.0.1:60546 | test | Sleep | 4168 | | <null> |
| 8 | root | 172.17.0.1:60552 | test | Sleep | 4170 | | <null> |
+----+------+------------------+------+---------+------+----------+------------------+
MySQL 非企業(yè)版本只支持一個線程一個鏈接
查看線程模型 show variables like 'thread_handling'
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-----------------------------------------+---------------------------+
【 事務提交策略】
有兩個隱藏事務提交時間點需要注意,第一個是 autocommit=1
Mysql session 級別的自動提交變量,所有 ORM 框架中的事務提交控制都會受到這個字段影響,默認情況下當前語句會自動提交,但是如果是顯示 begin transaction 開啟事務需要自行手動提交。有些時候 ORM 框架會根據(jù)一些設置或者策略,將 autocommit 設置為0。
第二個就是,DDL操作前都會隱式提交當前事務,有些腳本將DML和DDL混合在一起使用,這樣會有一致性問題。DDL會自動提交當前事務。因為DDL在5.7之前都是不支持事務原則操作的。(Mysql8.0已經(jīng)支持DDL事務性)
Next-Key Lock 只發(fā)生在 RR(REPEATABLE-READ) 隔離級別下。
Mysql 有很多類型對種鎖,表鎖
、record lock
、gap lock
、意向共享/排他鎖
、插入意向鎖
、元數(shù)據(jù)鎖
、Auto_Incr自增鎖
,排除掉 元數(shù)據(jù)鎖、Auto_Incr自增鎖 之后,剩下的鎖組合使用最多的就是在RR隔離級別下。
RR隔離級別是默認事務隔離級別,也是Mysql的強項之一,在RR隔離級別下事務有最大的吞吐量,而且不會出現(xiàn)幻讀問題。Next-Key Lock 就是為了解決這個問題,簡單講 record lock+gap lock 就是 Next-Key Lock。
_幻讀_的根本問題就是出現(xiàn)在記錄的邊界值上,比如我們統(tǒng)計年齡大于30歲的人數(shù):select count(1) peoples where age>30
這個語句有可能每次查詢得到的結(jié)果集都是不一樣的,因為只要符合 age>30 的記錄進到我們的 peoples 表中就會被查詢條件命中。
所以要想解決幻讀不僅不允許記錄的空隙被插入記錄外,還要防止兩遍記錄被修改,因為如果前后兩條記錄被修改了那區(qū)間就會變大,就會有幻讀出現(xiàn)。
我們看個例子。
CREATE TABLE `peoples` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_peoples_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
+----+-----+
| id | age |
+----+-----+
| 1 | 20 |
| 2 | 30 |
| 3 | 35 |
| 4 | 40 |
+----+-----+
為了方便調(diào)試,將 innodb 獲取鎖的超時時間調(diào)大點
show variables like '%innodb_lock_wait%'
set innodb_lock_wait_timeout=600
開啟兩個會話。
session A id=8:
begin
select count(1) from peoples where age>30 for update;
session B id=5:
begin
insert into peoples(age) values(31)
show processlist
找到連接的id。
***************************[ 1. row ]***************************
Id | 3
User | root
Host | 172.17.0.1:60542
db | test
Command | Query
Time | 0
State | starting
Info | show processlist
***************************[ 2. row ]***************************
Id | 5
User | root
Host | 172.17.0.1:60546
db | test
Command | Query
Time | 394
State | update
Info | insert into peoples(age) values(31)
***************************[ 3. row ]***************************
Id | 8
User | root
Host | 172.17.0.1:60552
db | test
Command | Sleep
Time | 396
State |
Info | <null>
select * from information_schema.innodb_trx \G
查看事務執(zhí)行情況。
***************************[ 1. row ]***************************
trx_id | 457240
trx_state | LOCK WAIT
trx_started | 2020-01-27 06:08:12
trx_requested_lock_id | 457240:131:4:4
trx_wait_started | 2020-01-27 06:09:25
trx_weight | 6
trx_mysql_thread_id | 5
trx_query | insert into peoples(age) values(31)
trx_operation_state | inserting
trx_tables_in_use | 1
trx_tables_locked | 1
trx_lock_structs | 5
trx_lock_memory_bytes | 1136
trx_rows_locked | 4
trx_rows_modified | 1
trx_concurrency_tickets | 0
trx_isolation_level | REPEATABLE READ
trx_unique_checks | 1
trx_foreign_key_checks | 1
trx_last_foreign_key_error | <null>
trx_adaptive_hash_latched | 0
trx_adaptive_hash_timeout | 0
trx_is_read_only | 0
trx_autocommit_non_locking | 0
***************************[ 2. row ]***************************
trx_id | 457239
trx_state | RUNNING
trx_started | 2020-01-27 06:07:59
trx_requested_lock_id | <null>
trx_wait_started | <null>
trx_weight | 3
trx_mysql_thread_id | 8
trx_query | <null>
trx_operation_state | <null>
trx_tables_in_use | 0
trx_tables_locked | 1
trx_lock_structs | 3
trx_lock_memory_bytes | 1136
trx_rows_locked | 5
trx_rows_modified | 0
trx_concurrency_tickets | 0
trx_isolation_level | REPEATABLE READ
trx_unique_checks | 1
trx_foreign_key_checks | 1
trx_last_foreign_key_error | <null>
trx_adaptive_hash_latched | 0
trx_adaptive_hash_timeout | 0
trx_is_read_only | 0
trx_autocommit_non_locking | 0
457240 事務狀態(tài)是 LOCK WAIT
在等待鎖,457239事務狀態(tài)是 RUNNING
執(zhí)行中,正在等待事務提交。
select * from information_schema.innodb_locks \G
查看鎖的占用情況。
***************************[ 1. row ]***************************
lock_id | 457240:131:4:4
lock_trx_id | 457240
lock_mode | X,GAP
lock_type | RECORD
lock_table | `test`.`peoples`
lock_index | idx_peoples_age
lock_space | 131
lock_page | 4
lock_rec | 4
lock_data | 35, 7
***************************[ 2. row ]***************************
lock_id | 457239:131:4:4
lock_trx_id | 457239
lock_mode | X
lock_type | RECORD
lock_table | `test`.`peoples`
lock_index | idx_peoples_age
lock_space | 131
lock_page | 4
lock_rec | 4
lock_data | 35, 7
innodb_locks 表包含了已經(jīng)獲取到的鎖信息和請求鎖的信息。lock_index字段表示鎖走的索引,record鎖都是基于索引完成。
根據(jù)上面事務457240狀態(tài)是獲取鎖,lock_data | 35, 7
,表示請求的數(shù)據(jù)。而事務457239占用了當前X鎖。
select * from information_schema.innodb_lock_waits
查看鎖等待信息。
***************************[ 1. row ]***************************
requesting_trx_id | 457240
requested_lock_id | 457240:131:4:4
blocking_trx_id | 457239
blocking_lock_id | 457239:131:4:4
457240 事務需要獲取131:4:4鎖,457239 事務占用了131:4:4鎖。
show engine innodb status
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422032240994144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 457240, ACTIVE 394 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140556966967040, query id 105 172.17.0.1 root update
insert into peoples(age) values(31)
------- TRX HAS BEEN WAITING 165 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 4 n bits 72 index idx_peoples_age of table `test`.`peoples` trx id 457240 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000023; asc #;;
1: len 4; hex 00000007; asc ;;
------------------
---TRANSACTION 457239, ACTIVE 407 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 8, OS thread handle 140556966696704, query id 104 172.17.0.1 root
MySQL thread id 5 正在準備上插入意向鎖,插入意向鎖
本質(zhì)上是加間隙鎖,是為了保證最大并發(fā)插入,不相關(guān)的行插入不受到互斥。thread id 5 需要保證在插入前加上間隙鎖,主要是防止并發(fā)插入帶來的一致性問題。
session 5 和 session 8 都沒有操作到 id=3,age=35的記錄,但是卻被X+Gap Lock 鎖住,只有這樣才能解決幻讀問題。
作者:王清培(趣頭條 Tech Leader)
分享題目:Mysql一分鐘定位Next-KeyLock,你需要幾分
新聞來源:http://muchs.cn/article2/ipgioc.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián),為您提供動態(tài)網(wǎng)站、網(wǎng)站排名、企業(yè)網(wǎng)站制作、網(wǎng)頁設計公司、關(guān)鍵詞優(yōu)化、品牌網(wǎng)站建設
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)