第十章

數據庫中的事務與鎖Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄10.1

實戰目標與準備10.2事務概述與常用命令10.3

MySQL中ACID特性驗證10.4并發導致的數據不一致問題重現實戰10.5MySQL并發控制——鎖10.6封鎖協議與MySQL的隔離級別10.7基于隔離級別與鎖解決數據不一致問題10.8MySQL的死鎖10.9MySQL并發控制——MVCC10.1實戰目標與準備實戰目標本章的目標是在掌握了數據庫和基本表的創建和管理的基礎上，學習如何通過一定的工具或SQL命令來對基本表中的數據進行增、刪、改、查等基本操作。熟練掌握對基本表進行數據插入、修改和刪除的SQL語句。熟練掌握數據查詢的SQL語句（簡單查詢和復合查詢）。掌握對視圖的數據操作，并了解其與基本表數據操作的關系。掌握SQL語句查詢性能分析的基本知識。了解TPC-C基準數據庫10.2事務概述與常用命令1.

事務的概念一個數據庫操作序列一個不可分割的工作單位（要么全做，要么全不做）恢復和并發控制的基本單位在關系數據庫中的事務和程序：一個事務可以是一條SQL語句，一組SQL語句或整個程序。一個程序通常包含多個事務。10.2事務概述與常用命令2.

事務的特性(ACID)原子性（Atomicity）一致性（Consistency）隔離性（Isolation）持續性（Durability）要么全做，要么全不做事務執行的結果必須是使數據庫從一個一致性狀態變到另一個一致性狀態一個事務的執行不能被其他事務干擾事務一旦提交，它對數據庫中數據的改變就應該是永久性的。10.2事務概述與常用命令commit10.2事務概述與常用命令rollback10.2事務概述與常用命令starttransaction;insertintotvalues(1);insertintotvalues(2);savepoints1;insertintotvalues(3);insertintotvalues(4);savepoints2;insertintotvalues(5);insertintotvalues(6);select*fromt;rollbacktosavepoints2;select*fromt;savepoint10.2事務概述與常用命令功能例子查看當前活動事務select*frominformation_schema.innodb_trx;設置/查看是否自動提交set@@autocommit=0;setglobal@@autocommit=0;setautocommit=0;setglobalautocommit=0;showglobal/sessionvariableslike'autocommit’;(省略global/session修飾符時，默認為session范圍)設置/查看全局隔離級別setglobaltransactionisolationlevelrepeatableread;showglobalvariableslike'transaction_isolation';設置/查看當前連接(session)隔離級別settransactionisolationlevelrepeatableread;setsessiontransactionisolationlevelrepeatableread;showvariableslike'transaction_isolation’;showsessionvariableslike'transaction_isolation’;select@@transaction_isolation;10.3MySQL中ACID特性驗證事務的原子性與一致性示例10.3MySQL中ACID特性驗證事務的隔離性與持久性示例10.4并發導致的數據不一致問題重現實戰第一類丟失更新問題10.4并發導致的數據不一致問題重現實戰第二類丟失更新問題10.4并發導致的數據不一致問題重現實戰讀臟數據問題10.4并發導致的數據不一致問題重現實戰不可重復讀10.4并發導致的數據不一致問題重現實戰幻讀10.5MySQL并發控制——鎖10.5MySQL并發控制——鎖幾張與鎖相關的系統表：10.5MySQL并發控制——鎖InnoDB的支持的鎖鎖類別描述SharedandExclusiveLocks共享鎖/排他鎖IntentionLocks意向共享鎖/意向排他鎖RecordLocks行鎖GapLocks間隙鎖Next-KeyLocks臨鍵鎖InsertIntentionLocks插入意向鎖AUTO-INCLocks自增鎖PredicateLocksforSpatialIndexes空間索引使用，本處暫不討論10.5MySQL并發控制——鎖InnoDB：間隙鎖create

tablet(

idint(11)

not

null,

cint(11),

dint(11),

primary

key

(id),

keyc(c)

)

ENGINE

=

InnoDB;

insert

intotvalues(0,0,0),(5,5,5),(15,15,15)；(-∞,0)(0,5)(5,15)(15,+∞)0515間隙鎖沖突：是“往這個間隙中插入一個記錄”。select*fromtwherec=5forupdate;限制插入c值=5的左右兩個間隙鎖范圍內的值：[0,15]10.5MySQL并發控制——鎖InnoDB：間隙鎖insert

intot(id,c,d)values(0,0,0),(5,5,5),(15,15,15)；begin;

select*fromtwherec=5forupdate;primarykey(id),keyc(c))ENGINE

=

InnoDB;

T110.5MySQL并發控制——鎖InnoDB：間隙鎖10.5MySQL并發控制——鎖InnoDB：間隙鎖10.5MySQL并發控制——鎖查看鎖的方式：showengineinnodbstatus;MySQL8.0的系統表：innodb_trx,data_locks,innodb_lock_waits.

select*frominformation_schema.innodb_trxselect*fromperformance_schema.data_locks

select*fromsys.innodb_lock_waits;information_schema.innodb_trx表（事務信息表，trx_id）10.5MySQL并發控制——鎖查看鎖的方式：sys.innodb_lock_waits表performance_schema.data_locks表LOCK_TYPE:record/tableLOCK_MODE:S[,GAP],X[,GAP],IS[,GAP],IX[,GAP],AUTO_INC,UNKNOWNLOCK_STATUS:GRANTED/WAITINGLOCK_DATA:與鎖關聯的數據（表鎖時，該值為NULL）。10.6封鎖協議與MySQL的隔離級別10.6封鎖協議與MySQL的隔離級別封鎖協議-各級封鎖區別：什么操作需要申請封鎖以及何時釋放鎖-封鎖協議級別越高，一致性程度越高10.6封鎖協議與MySQL的隔離級別隔離級別讀未提交：ReadUncommitted讀已提交：ReadCommitted可重復讀：RepeatableRead可串行化：Serializable默認隔離級別：MySQL是可重復讀；KingBase、SQLServer、Oracle等為讀已提交注意：事務隔離級別并非越高越好MySQL常用:setsessiontransactionisolationlevelreaduncommittedselect@@transaction_isolation10.7基于隔離級別與鎖解決數據不一致問題10.8MySQL的死鎖10.9MySQL并發控制——MVCC多版本并發控制(MVCC):保留數據的多個版本。寫數據時，舊版本的數據并不刪除，寫一個新的版本，這樣并發的讀操作還能讀到舊版本的數據。讀寫不阻塞，并發度高。傳統封鎖方式MVCC在T1準備寫A的時候，

為A生成一個新版本（A’），T2事務不用等待，可繼續在A’上執行。T2準備提交時，確認事務T1是否已經完成。如果T1已經完成了，T2就可以放心地提交。如果T1還沒有完成，那么T2必須等待直到T1完成。10.9MySQL并