基于更新SQL语句理解MySQL锁定详解
前言
MySQL数据库锁是实现数据一致性，解决并发问题的重要手段。数据库是一个多用户共享的资源，当出现并发的时候，就会导致出现各种各样奇怪的问题，就像程序代码一样，出现多线程并发的时候，如果不做特殊控制的话，就会出现意外的事情，比如“脏“数据、修改丢失等问题。所以数据库并发需要使用事务来控制，事务并发问题需要数据库锁来控制，所以数据库锁是跟并发控制和事务联系在一起的。
本文主要描述基于更新SQL语句来理解MySQL锁定。下面话不多说了，来一起看看详细的介绍吧
一、构造环境
(root@localhost)[user]>showvariableslike'version'; +---------------+------------+ |Variable_name|Value| +---------------+------------+ |version|5.7.23-log| +---------------+------------+ (root@localhost)[user]>desct1; +-------------+--------------+------+-----+---------+----------------+ |Field|Type|Null|Key|Default|Extra| +-------------+--------------+------+-----+---------+----------------+ |id|int(11)|NO|PRI|NULL|auto_increment| |n|int(11)|YES||NULL|| |table_name|varchar(64)|YES||NULL|| |column_name|varchar(64)|YES||NULL|| |pad|varchar(100)|YES||NULL|| +-------------+--------------+------+-----+---------+----------------+ (root@localhost)[user]>selectcount(*)fromt1; +----------+ |count(*)| +----------+ |3406| +----------+ (root@localhost)[user]>createuniqueindexidx_t1_padont1(pad); QueryOK,0rowsaffected(0.35sec) Records:0Duplicates:0Warnings:0 (root@localhost)[user]>createindexidx_t1_nont1(n); QueryOK,0rowsaffected(0.03sec) Records:0Duplicates:0Warnings:0 (root@localhost)[user]>showindexfromt1; +-------+------------+------------+--------------+-------------+-----------+-------------+------+------------+ |Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Null|Index_type| +-------+------------+------------+--------------+-------------+-----------+-------------+------+------------+ |t1|0|PRIMARY|1|id|A|3462||BTREE| |t1|0|idx_t1_pad|1|pad|A|3406|YES|BTREE| |t1|1|idx_t1_n|1|n|A|12|YES|BTREE| +-------+------------+------------+--------------+-------------+-----------+-------------+------+------------+ select'Leshami'author,'http://blog.csdn.net/leshami'Blog; +---------+------------------------------+ |author|Blog| +---------+------------------------------+ |Leshami|http://blog.csdn.net/leshami| +---------+------------------------------+
二、基于主键更新
(root@localhost)[user]>starttransaction; QueryOK,0rowsaffected(0.00sec) (root@localhost)[user]>updatet1settable_name='t1'whereid=1299; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0 SELECTtrx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_level FROMINFORMATION_SCHEMA.INNODB_TRX\G --从下面的结果可知，trx_rows_locked，一行被锁定 ***************************1.row*************************** trx_id:6349647 trx_state:RUNNING trx_started:2018-11-0616:54:12 trx_mysql_thread_id:2 trx_tables_locked:1 trx_rows_locked:1 trx_rows_modified:1 trx_isolation_level:REPEATABLEREAD (root@localhost)[user]>rollback; QueryOK,0rowsaffected(0.01sec)
三、基于二级唯一索引
(root@localhost)[user]>starttransaction; QueryOK,0rowsaffected(0.00sec) (root@localhost)[user]>updatet1settable_name='t2'wherepad='4f39e2a03df3ab94b9f6a48c4aecdc0b'; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0 SELECTtrx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_level FROMINFORMATION_SCHEMA.INNODB_TRX\G --从下面的查询结果可知，trx_rows_locked，2行被锁定 ***************************1.row*************************** trx_id:6349649 trx_state:RUNNING trx_started:2018-11-0616:55:22 trx_mysql_thread_id:2 trx_tables_locked:1 trx_rows_locked:2 trx_rows_modified:1 trx_isolation_level:REPEATABLEREAD (root@localhost)[user]>rollback; QueryOK,0rowsaffected(0.00sec)
三、基于二级非唯一索引
(root@localhost)[user]>starttransaction; QueryOK,0rowsaffected(0.00sec) (root@localhost)[user]>updatet1settable_name='t3'wheren=8; QueryOK,350rowsaffected(0.01sec) Rowsmatched:351Changed:351Warnings:0 SELECTtrx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_level FROMINFORMATION_SCHEMA.INNODB_TRX\G --从下面的查询结果可知，703行被锁定 ***************************1.row*************************** trx_id:6349672 trx_state:RUNNING trx_started:2018-11-0617:06:53 trx_mysql_thread_id:2 trx_tables_locked:1 trx_rows_locked:703 trx_rows_modified:351 trx_isolation_level:REPEATABLEREAD (root@localhost)[user]>rollback; QueryOK,0rowsaffected(0.00sec)
四、无索引更新
(root@localhost)[user]>starttransaction; QueryOK,0rowsaffected(0.00sec) (root@localhost)[user]>updatet1settable_name='t4'wherecolumn_name='id'; QueryOK,26rowsaffected(0.00sec) Rowsmatched:26Changed:26Warnings:0 SELECTtrx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_level FROMINFORMATION_SCHEMA.INNODB_TRX\G --从下面的查询结果可知，trx_rows_locked，3429行被锁定，而被更新的仅仅为26行 --而且这个结果超出了表上的总行数3406 ***************************1.row*************************** trx_id:6349674 trx_state:RUNNING trx_started:2018-11-0617:09:41 trx_mysql_thread_id:2 trx_tables_locked:1 trx_rows_locked:3429 trx_rows_modified:26 trx_isolation_level:REPEATABLEREAD (root@localhost)[user]>rollback; QueryOK,0rowsaffected(0.00sec) --也可以通过showengineinnodbstatus进行观察 showengineinnodbstatus\G ------------ TRANSACTIONS ------------ Trxidcounter6349584 Purgedonefortrx'sn:o<0undon:o<0state:runningbutidle Historylistlength0 LISTOFTRANSACTIONSFOREACHSESSION: ---TRANSACTION421943222819552,notstarted 0lockstruct(s),heapsize1136,0rowlock(s) ---TRANSACTION6349583,ACTIVE2sec 2lockstruct(s),heapsize1136,1rowlock(s),undologentries1 ------------ TRANSACTIONS ------------ Trxidcounter6349586 Purgedonefortrx'sn:o<6349585undon:o<0state:runningbutidle Historylistlength1 LISTOFTRANSACTIONSFOREACHSESSION: ---TRANSACTION421943222819552,notstarted 0lockstruct(s),heapsize1136,0rowlock(s) ---TRANSACTION6349585,ACTIVE8sec 3lockstruct(s),heapsize1136,2rowlock(s),undologentries1 MySQLthreadid2,OSthreadhandle140467640694528,queryid29localhostroot
五、锁相关查询SQL
1：查看当前的事务
SELECT*FROMINFORMATION_SCHEMA.INNODB_TRX;
2：查看当前锁定的事务
SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS;
3：查看当前等锁的事务
SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS; SELECTtrx_id, trx_state, trx_started, trx_mysql_thread_idthr_id, trx_tables_lockedtb_lck, trx_rows_lockedrows_lck, trx_rows_modifiedrow_mfy, trx_isolation_levelis_lvl FROMINFORMATION_SCHEMA.INNODB_TRX; SELECTr.`trx_id`waiting_trx_id, r.`trx_mysql_thread_id`waiting_thread, r.`trx_query`waiting_query, b.`trx_id`bolcking_trx_id, b.`trx_mysql_thread_id`blocking_thread, b.`trx_query`block_query FROMinformation_schema.`INNODB_LOCK_WAITS`w INNERJOINinformation_schema.`INNODB_TRX`b ONb.`trx_id`=w.`blocking_trx_id` INNERJOINinformation_schema.`INNODB_TRX`r ONr.`trx_id`=w.`requesting_trx_id`;
六、小结
1、MySQL表更新时，对记录的锁定根据更新时where谓词条件来确定锁定范围
2、对于聚簇索引过滤，由于索引即数据，因为仅仅锁定更新行，这是由聚簇索引的性质决定的
3、对于非聚簇唯一索引过滤，由于需要回表，因此锁定为唯一索引过滤行数加上回表行数
4、对于非聚簇非唯一索引过滤，涉及到了间隙锁，因此锁定的记录数更多
5、如果过滤条件无任何索引或无法使用到索引，则锁定整张表上所有数据行
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。