第十章

數(shù)據(jù)庫中的事務(wù)與鎖Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫目錄10.1

實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備10.2事務(wù)概述與常用命令10.3

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

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

事務(wù)的特性(ACID)原子性（Atomicity）一致性（Consistency）隔離性（Isolation）持續(xù)性（Durability）要么全做，要么全不做事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個(gè)一致性狀態(tài)變到另一個(gè)一致性狀態(tài)一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾事務(wù)一旦提交，它對(duì)數(shù)據(jù)庫中數(shù)據(jù)的改變就應(yīng)該是永久性的。10.2事務(wù)概述與常用命令commit10.2事務(wù)概述與常用命令rollback10.2事務(wù)概述與常用命令starttransaction;insertintotvalues(1);insertintotvalues(2);savepoints1;insertintotvalues(3);insertintotvalues(4);savepoints2;insertintotvalues(5);insertintotvalues(6);select*fromt;rollbacktosavepoints2;select*fromt;savepoint10.2事務(wù)概述與常用命令功能例子查看當(dāng)前活動(dòng)事務(wù)select*frominformation_schema.innodb_trx;設(shè)置/查看是否自動(dòng)提交set@@autocommit=0;setglobal@@autocommit=0;setautocommit=0;setglobalautocommit=0;showglobal/sessionvariableslike'autocommit’;(省略global/session修飾符時(shí)，默認(rèn)為session范圍)設(shè)置/查看全局隔離級(jí)別setglobaltransactionisolationlevelrepeatableread;showglobalvariableslike'transaction_isolation';設(shè)置/查看當(dāng)前連接(session)隔離級(jí)別settransactionisolationlevelrepeatableread;setsessiontransactionisolationlevelrepeatableread;showvariableslike'transaction_isolation’;showsessionvariableslike'transaction_isolation’;select@@transaction_isolation;10.3MySQL中ACID特性驗(yàn)證事務(wù)的原子性與一致性示例10.3MySQL中ACID特性驗(yàn)證事務(wù)的隔離性與持久性示例10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實(shí)戰(zhàn)第一類丟失更新問題10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實(shí)戰(zhàn)第二類丟失更新問題10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實(shí)戰(zhàn)讀臟數(shù)據(jù)問題10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實(shí)戰(zhàn)不可重復(fù)讀10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實(shí)戰(zhàn)幻讀10.5MySQL并發(fā)控制——鎖10.5MySQL并發(fā)控制——鎖幾張與鎖相關(guān)的系統(tǒng)表：10.5MySQL并發(fā)控制——鎖InnoDB的支持的鎖鎖類別描述SharedandExclusiveLocks共享鎖/排他鎖IntentionLocks意向共享鎖/意向排他鎖RecordLocks行鎖GapLocks間隙鎖Next-KeyLocks臨鍵鎖InsertIntentionLocks插入意向鎖AUTO-INCLocks自增鎖PredicateLocksforSpatialIndexes空間索引使用，本處暫不討論10.5MySQL并發(fā)控制——鎖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間隙鎖沖突：是“往這個(gè)間隙中插入一個(gè)記錄”。select*fromtwherec=5forupdate;限制插入c值=5的左右兩個(gè)間隙鎖范圍內(nèi)的值：[0,15]10.5MySQL并發(fā)控制——鎖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并發(fā)控制——鎖InnoDB：間隙鎖10.5MySQL并發(fā)控制——鎖InnoDB：間隙鎖10.5MySQL并發(fā)控制——鎖查看鎖的方式：showengineinnodbstatus;MySQL8.0的系統(tǒng)表：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表（事務(wù)信息表，trx_id）10.5MySQL并發(fā)控制——鎖查看鎖的方式：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:與鎖關(guān)聯(lián)的數(shù)據(jù)（表鎖時(shí)，該值為NULL）。10.6封鎖協(xié)議與MySQL的隔離級(jí)別10.6封鎖協(xié)議與MySQL的隔離級(jí)別封鎖協(xié)議-各級(jí)封鎖區(qū)別：什么操作需要申請(qǐng)封鎖以及何時(shí)釋放鎖-封鎖協(xié)議級(jí)別越高，一致性程度越高10.6封鎖協(xié)議與MySQL的隔離級(jí)別隔離級(jí)別讀未提交：ReadUncommitted讀已提交：ReadCommitted可重復(fù)讀：RepeatableRead可串行化：Serializable默認(rèn)隔離級(jí)別：MySQL是可重復(fù)讀；KingBase、SQLServer、Oracle等為讀已提交注意：事務(wù)隔離級(jí)別并非越高越好MySQL常用:setsessiontransactionisolationlevelreaduncommittedselect@@transaction_isolation10.7基于隔離級(jí)別與鎖解決數(shù)據(jù)不一致問題10.8MySQL的死鎖10.9MySQL并發(fā)控制——MVCC多版本并發(fā)控制(MVCC):保留數(shù)據(jù)的多個(gè)版本。寫數(shù)據(jù)時(shí)，舊版本的數(shù)據(jù)并不刪除，寫一個(gè)新的版本，這樣并發(fā)的讀操作還能讀到舊版本的數(shù)據(jù)。讀寫不阻塞，并發(fā)度高。傳統(tǒng)封鎖方式MVCC在T1準(zhǔn)備寫A的時(shí)候，

為A生成一個(gè)新版本（A’），T2事務(wù)不用等待，可繼續(xù)在A’上執(zhí)行。T2準(zhǔn)備提交時(shí)，確認(rèn)事務(wù)T1是否已經(jīng)完成。如果T1已經(jīng)完成了，T2就可以放心地提交。如果T1還沒有完成，那么T2必須等待直到T1完成。10.9MySQL并