第十课mysql8.0锁机制和事务

••举例来说selectforsharemode语句就是施加了共享意图锁，而select…forupdate语句就是施加了排他意图锁 X

Column表示该锁的模式。可以是S[,GAPX[,GAPIS[,GAP],IX[,GAPAUTO_INC,UNKNOWN除了当为行锁时，则代表该锁涉及的索引名，否则为事务锁定记录主键值，若是表锁则该值为Column请求锁被的process上述事务获得锁的事务事务对应的process mysql>createtabletemp(idint,nameQueryOK,0rowsaffected(0.01mysql>insertintotemp QueryOK,0rowsaffected(0.00

mysql>updatetempsetname='aa'whereid=1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:02 锁mysql>QueryOK,0rowsaffected(0.004

mysql>updatetempsetname='bb'whereid=2;QueryOK,1rowaffectedsec)Rowsmatched:1Changed: 锁ocked,trx_rows_lockedfromame,object_name,lock_data,lock_statusfrom1|trx_id|trx_state| trx_tables_locked|trx_rows_locked||engine_lock_id|engine_transaction_id|lock_mode|lock_type |object_name|| 20090| | | | |Emptyset,1warning(0.00|20090||20090:54:4:1 20090| | GEN_CLUST_INDEX| |supremumpseudo-record|20090:54:4:2 20090| | GEN_CLUST_INDEX| | 04:52:521 4|20090:54:4:3 20090|GEN_CLUST_INDEX| ||RECORD|||20090:54:4:4 20090|GEN_CLUST_INDEX| ||RECORD||2|trx_id|trx_state|trx_started trx_tables_locked|trx_rows_locked| |20091|LOCKWAIT|2019-03-13 1 1|engine_lock_id|engine_transaction_id|lock_mode|lock_type|index_name |object_name|lock_data | 20091| || | |20091:54:4:2 20091| |GEN_CLUST_INDEX| | | 20090| || | ||||||locked_table_name|locked_index waiting_lock_id|blocking_lock_id|blocking_pid| |temp |GEN_CLUST_INDEX| 14| |20090:54:4:2 11||20090| 1 4|20090:54:4:1 20090| | GEN_CLUST_INDEX| |supremumpseudo-record|20090:54:4:2 20090|GEN_CLUST_INDEX| ||RECORD|||20090:54:4:3 20090|GEN_CLUST_INDEX| ||RECORD|||20090:54:4:4 20090|GEN_CLUST_INDEX| ||RECORD||ocked,trx_rows_lockedfromme,object_name,lock_data,lock_statusfrom3|trx_id|trx_state|engine_lock_id|engine_transaction_id|lock_mode|lock_type| |object_name|lock_data | 20091| | | | |20091:54:4:1 20091| | GEN_CLUST_INDEX| |supremumpseudo-record|20091:54:4:2 20091| | GEN_CLUST_INDEX| | |20091:54:4:3 20091| | GEN_CLUST_INDEX| | |20091:54:4:4 20091| | GEN_CLUST_INDEX| | Emptyset,1warning(0.00 |20091RUNNING1 44|trx_id|trx_state|engine_lock_id|engine_transaction_id|lock_mode|lock_type| |object_name|lock_data | 20091| | | | |20091:54:4:1 20091| | GEN_CLUST_INDEX| |supremumpseudo-record|20091:54:4:2 20091| | GEN_CLUST_INDEX| | |20091:54:4:3 20091| | GEN_CLUST_INDEX| | |20091:54:4:4 20091| | GEN_CLUST_INDEX| | Emptyset,1warning(0.00 |20091RUNNING1 45Emptyset(0.00Emptyset,1warning(0.00Emptyset,1warning(0.00mysql>altertabletempaddprimaryQueryOK,0rowsaffected(0.01

QueryOK,0rowsaffected(0.00

mysql>updatetempsetname='aa'whereid=1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:02 锁mysql>QueryOK,0rowsaffected(0.004

mysql>updatetempsetname='bb'whereid=1;QueryOK,1rowaffectedsec)Rowsmatched:1Changed: 锁ked,trx_rows_lockedfrom1|trx_id|trx_state|trx_started |engine_lock_id|engine_transaction_id||lock_type|index_name|object_name|lock_dataEmptyset,1warning(0.00 |20119|RUNNING|2019-03- 1 1| || | | |20119:55:4:2| 20119|X,REC_NOT_GAP|RECORD |PRIMARY | 2|trx_id|trx_state|trx_started |engine_lock_id|engine_transaction_id||lock_type|index_name|object_name|lock_data|locked_table_name|locked_index|waiting_pid|waiting_lock_id|blocking_lock_id|blocking_pid| |20120|LOCKWAIT|2019-03-13 1 1| 20120| || | | |temp |PRIMARY 14| |20119:55:4:2 |20119|RUNNING|2019-03- 1 1|20120:55:4:2| 20120|X,REC_NOT_GAP|RECORD |PRIMARY | | || | | |20119:55:4:2 X,REC_NOT_GAP| | || ked,trx_rows_lockedfrom3|trx_id|trx_state| trx_rows_locked||engine_lock_id|engine_transaction_id||lock_type|index_name|object_name|lock_data Emptyset,1warning(0.00 |20120|RUNNING|1 1| 20120| || | | |20120:55:4:2| 20120|X,REC_NOT_GAP|RECORD |PRIMARY | 4Emptyset(0.00Emptyset,1warning(0.00Emptyset,1warning(0.00mysql>altertabletempaddprimary QueryOK,0rowsaffected(0.01121 QueryOK,0rowsaffected(0.00 mysql>updatetempsetname='aa'whereid=1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:02mysql>updatetempsetname='bb'whereid=2;QueryOK,0rowsaffectedsec)Rowsmatched:1Changed:3锁mysql>QueryOK,0rowsaffected(0.004ocked,trx_rows_lockedfromk_type,index_name,object_name,lock_datafrom1|trx_id|trx_state| | || | | Emptyset,1warning(0.00 |20117:55:4:2 | | | | |20117|RUNNING|2019-03- 1 12|trx_id|trx_state| |20118|RUNNING|2019-03- 1 1|20117|RUNNING|2019-03- 1 1 |lock_type|index_name|object_name|lock_data| | | | | |20118:55:4:3| 20118|X,REC_NOT_GAP|RECORD | |||requesting_trx_id|+| | | | | | | ||20117:55:4:2 X,REC_NOT_GAP| | | |ocked,trx_rows_lockedfromk_type,index_name,object_name,lock_datafrom3|trx_id|trx_state| |engine_lock_id|engine_transaction_id||lock_type|index_name|object_name|lock_dataEmptyset,1warning(0.00 |20118|RUNNING|2019-03- 1 1| || | | |20118:55:4:3 | | | | 4|trx_id|trx_state| |engine_lock_id|engine_transaction_id||lock_type|index_name|object_name|lock_dataEmptyset,1warning(0.00 |20118|RUNNING|2019-03- 1 1| || | | |20118:55:4:3 | | | | 5Emptyset(0.00Emptyset,1warning(0.00Emptyset,1warning(0.00当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的间隔锁是施加在索引记录之间的间隔上的锁，锁定一个范围的记录、但不包括记录本身,比如SELECTc1FROMtWHEREc1BETWEEN10and20FORUPDATE语句，尽管有可能对c1字段来说当前表里没有=15 1事务 级别为REPEATABLE-READ，且sql走的索引为非唯一索引（无论是等值检索还是范围检索）2事务 CREATETABLE`temp`(`id`int(11)NOTNULL,ENGINE=InnoDBDEFAULTCHARSET=latin1mysql>insertintotemp

锁mysql>setQueryOK,0rowsaffected(0.00

mysql>updatetempsetname='abc'whereidbetween46;QueryOK,0rowsaffected(0.00 mysql>insertintotemp 锁mysql>QueryOK,0rowsaffected(0.004

QueryOK,1rowaffectedsec)Rowsmatched:1Changed: 锁ocked,trx_rows_lockedfromocked,trx_rows_lockedfromtusfromperformance_schema.data_locks;1|trx_id|trx_state| | |||20136:55:4:120136| || |20136| ||supremumpseudo-record|Emptyset,1warning(0.00|20136|RUNNING|2019-03- 1 1| | 2|trx_id|trx_state| |20137|LOCKWAIT|2019-03-13 1 1 |lock_type|index_name|object_name|lock_data |lock_status| |locked_table_name|locked_indexwaiting_pid|waiting_lock_id| |20136|RUNNING|2019-03- 1 1| | || 20137|| || | 14| |||20137:55:4:1 X,INSERT_INTENTION| |temp |supremumpseudo-record|WAITING | | || 20136|| ||20136:55:4:1 20136|| | | tusfrom3|trx_id|trx_state| | 20137| Emptyset,1warning(0.00 | | || |20137:55:4:1 |20137|RUNNING|2019-03- 1 1X,INSERT_INTENTION| | |supremumpseudo-record|||4|trx_id|trx_state| | 20137| Emptyset,1warning(0.00 | | || |20137:55:4:1 |20137|RUNNING|2019-03- 1 1X,INSERT_INTENTION| | |supremumpseudo-record|||5Emptyset(0.00Emptyset,1warning(0.00Emptyset,1warning(0.00 1的update语句是updatetempsetname=‘abc’whereid>4;而 mysql>updatetempsetname='abc'whereQueryOK,0rowsaffected(0.00Rowsmatched:0Changed:0Warnings:mysqlinsertintotempvalues(4,‘abc’);##••Locks，就是记录锁和间隔锁的结合，• • 事务mysql>CREATETABLEchild(idint(11)NOTNULL,PRIMARYKEY(id))mysql>INSERTINTOchild(id)valuesmysql>STARTmysql>SELECT*FROMchildWHEREid>100FOR +|id +|102 +mysql>STARTmysql>INSERTINTOchild(id)VALUES我们可以通过showengineinnodbstatus命令查看插入意向锁被RECORDLOCKSspaceid31pageno3nbits72index`PRIMARY`oftable`test`.`child`trxid8731lock_modeXlocksgapbeforerecinsertintentionwaitingRecordlock,heapno3PHYSICALRECORD:n_fields3;compactformat;infobits00:len4; ;asc1:len6;hex000000002215;asc"2:len7;hex9000000172011c;ascr mysql>showvariableslike |Variable_name| |tx_isolation|REPEATABLE-READ mysql>showvariableslike | mysql>showvariableslike | |Value |innodb_table_locks| mysql>showvariableslike | |Value Mysql> mysql>updatetempsetname='abc'whereMysql> mysql>insertintotemp ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestarting READ 级别，也可以通过set[session/global]transaction SET[GLOBAL|SESSION]TRANSACTIONISOLATION{ |READ|} REPEATABLEREAD：可重复读，默认值。表明对同一个事务来说获得和第一次读相同的结果。当读操作是加锁的读语句(select…forupdate或者lockinsharemode),或者update和delete语句时，加锁的方式依赖于语句是否使用唯一索引唯一值或者范围值当的是唯一索引的唯一值时，则InnoDB当唯一索引的范围值时，则会在扫描的索引行上增加间隔锁或者next-key锁以防止其他对此范围的插入 READ 的快照。对于加锁读语句(select…forupdate和lockinshare 语句对应的行在上次mit之后的数据是否在锁的范围，如果不是，则不影响update操作，如果是，则需要等121Mysql>setsessiontransactionlevelread mysql>updatetempsetwhereQueryOK,1rowaffected(0.002mysql>setsessiontransactionisolationlevelcommitted;mysql> mysql>select*from |id|name |1| |2| |3|abc|4|abc3QueryOK,0rowsaffected(0.004mysql>select*from |id|name |1|aaa|2| |3|abc|4|abc5 CREATETABLEt(aINTNOTNULL,bINT)ENGINE=INSERTINTOtVALUES mit=UPDATEtSETb=5WHEREb= mit=UPDATEtSETb=4WHEREb=•x-lock(2,3);update(2,3)to(2,5);retainx-x-lock(4,3);update(4,3)to(4,5);retainx-x-lock(1,2);blockandwaitforfirstUPDATEtocommitorroll•对x-lock(2,3);update(2,3)to(2,5);retainx-x-lock(4,3);update(4,3)to(4,5);retainx-x-lock(1,2);update(1,2)to(1,4);retainx-x-lock(3,2);update(3,2)to(3,4);retainx-x-lock(5,2);update(5,2)to(5,4);retainx-•• 句隐含的转化为select…lockinsharemode121mysql>setsessiontransactionlevel QueryOK,0rowsaffected(0.00 QueryOK,0rowsaffected(0.00mysql>updatetempsetQueryOK,3rowsaffected2 QueryOK,0rowsaffected(0.00 QueryOK,0rowsaffected(0.00mysqlselect*from |id|name |1| |2| |3| |4| 4rowsinset(0.003mysql>QueryOK,0rowsaffected(0.004121mysql>setsessiontransactionlevelQueryOK,0rowsaffected(0.00 QueryOK,0rowsaffected(0.00mysql>updatetempsetQueryOK,0rowsaffectedRowsmatched:4Changed:0Warnings:2 QueryOK,0rowsaffectedmysqlselect*fromtemp;##3QueryOK,0rowsaffected(0.004mysql>select*from |id|name |1| |2| |3| |4| 5当设 而 mit属性开启时，可以通过执行starttransaction或者begin语句而当在数据 ，事务的终结由commit或者rollback决定，同时也意味着下一个事务的开始果一个事务 的情况下数据 退出而没有执 mit语 mit=0将当前 mysql>--Doatransaction mitturnedmysql>STARTmysql>INSERTINTOcustomerVALUES(10,QueryOK,1rowaffected(0.00mysql>QueryOK,0rowsaffected(0.00mysql>--Doanothertransaction mitturnedmysql> mysql>INSERTINTOcustomerVALUES(15,QueryOK,1rowaffected(0.00mysql>INSERTINTOcustomerVALUES(20,QueryOK,1rowaffected(0.00mysql>DELETEFROMcustomerWHEREb=QueryOK,1rowaffected(0.00mysql>--Nowweundothoselast2insertsandthemysql>QueryOK,0rowsaffected(0.00mysql>SELECT*FROM | | 10|Heikki在默认的级别下一致读是指InnoDB在多版本控制中在事务的首改都不到以到在提交数据级别下，一致读的每个操作都会有自己的镜 Session||||v

SELECT*FROMemptyINSERTINTOtVALUES(1,SELECT*FROMemptySELECT*FROMemptySELECT*FROM QueryOK,0rowsaffected(0.00mysql>updatetempsetname='aaa';QueryOK,4rowsaffected(0.00sec)3

QueryOK,0rowsaffected(0.00mysql>select*from |id|name mysql>updatetempsetname='bbb';QueryOK,4rowsaffected(0.00sec)5

|1|aaa|2|aaa|3|aaa|4|aaamysql>select*from |id|name |1|aaa|2|aaa|3|aaa|4|aaa 当碰到insertinto…select,update…select和createtable…select语句时，在默认的事务级别下，语句的执行更类似于在提交数据的级别下的读操作无法其他事务对相同数据执行修改操作，所以InnoDBselect…lockinsharemode：在 selectforupdate：和update操作一样 般的读操作先查父表有值然后再插入的方法是不的，因为在读操SELECT*FROMparentWHERENAME='Jones'LOCKINSHAREMODE;相同的值，在这种情况下就要使用selectforupdate语句保证一个 121mit=0;mysql>select*fromtemp; |id|name |1|abc|2|abc|3|abc|4|abc2mysql>set mysql>deletefromtempwhereid=3;QueryOK,1rowaffected(0.00sec)mysql>commit;QueryOK,0rowsaffected(0.013mysql>select*from |id|name |1|abc|2|abc|3|abc|4|abc4mysql>insertintotemp_childvalues(1,3);ERROR1452(23000):Cann