第11章 事務(wù)和鎖

第11章事務(wù)和鎖《MySQL數(shù)據(jù)庫應(yīng)用教程》劉瑞新主編配套資源目錄第11章事務(wù)和鎖11.1事務(wù)11.2鎖11.1.1事務(wù)的概念1.事務(wù)的基本概念2.事務(wù)的基本特性（1）原子性（Atomicity）（2）一致性（Consistcncy）（3）隔離性（Isolation）（4）持久性（Durability）11.1事務(wù)3.事務(wù)的分類（1）自動提交事務(wù)（2）顯式事務(wù)（3）隱式事務(wù)（4）分布式事務(wù)11.1事務(wù)4.事務(wù)的必要性【例11-1】假設(shè)一個銀行的數(shù)據(jù)庫中，有一張賬戶表，保存著兩張借記卡賬戶A和B，并且要求這兩張借記卡賬戶部不能透支，即兩個賬戶的余額不能小于零。A賬戶和B賬戶的余額都是1000元，A向B轉(zhuǎn)賬200元，則需要6個步驟：1）從賬戶A中讀取余額為1000，即A=1000。2）賬戶A的余額減去200，即A=A-200。3）賬戶A的余額寫入為800。4）從賬戶B中讀取余額為l000，即B=1000。5）賬戶B的余額加上200，即B=B+200。6）賬戶B的余額寫入為1200。11.1事務(wù)對應(yīng)以上6個步驟理來解事務(wù)的4個屬性，具體如下。1）原子性2）一致性3）隔離性4）持久性11.1事務(wù)11.1.2事務(wù)的基本操作1.設(shè)置事物的自動提交模式（1）查看系統(tǒng)變量@@autocommit的值SHOWVARIABLESLIKE'autocommit';11.1事務(wù)（2）設(shè)置事務(wù)的自動提交模式語句SETAUTOCOMMIT=0|1|ON|OFF;2.開啟事務(wù)STARTTRANSACTION|BEGINWORK;3.提交（結(jié)束）事務(wù)COMMIT;11.1事務(wù)【例11-2】在studentinfo數(shù)據(jù)庫中新建一個銀行表bank，表列的定義包括賬戶編號id（INT，主鍵）、賬戶名name（VARCHAR(20)）和賬戶余額money（DECIMAL(10,2)）。1）USEstudentinfo;CREATETABLEbank(idINTPRIMARYKEY,nameVARCHAR(20),moneyDECIMAL(10,2));2）INSERTINTObank(id,name,money)VALUES(101,'張三',1000),(102,'李四',1000),(103,'王五',1000);3）SELECT*FROMbank;11.1事務(wù)【例11-3】在bank表中，通過事務(wù)把張三的300元轉(zhuǎn)給李四，模擬自動回滾事務(wù)。1）STARTTRANSACTION;2）UPDATEbankSETmoney=money-300WHEREname='張三';3）UPDATEbankSETmoney=money+300WHEREname='李四';4）SELECT*FROMbank;

11.1事務(wù)5）6）SELECT*FROMbank;

11.1事務(wù)【例11-4】在bank表中，通過事務(wù)把張三的300元轉(zhuǎn)給李四，完成提交事務(wù)。1）2）COMMIT;3）SELECT*FROMbank;11.1事務(wù)4.回滾（撤銷）事務(wù)ROLLBACK;【例11-5】在bank表中，通過事務(wù)把王五的200元轉(zhuǎn)給李四，然后回滾所有事物。1）STARTTRANSACTION;2）UPDATEbankSETmoney=money-200WHEREname='王五';3）UPDATEbankSETmoney=money+200WHEREname='李四';11.1事務(wù)4）SELECT*FROMbank;

5）ROLLBACK;6）SELECT*FROMbank;11.1事務(wù)11.1.3設(shè)置事務(wù)的保存點1.設(shè)置事務(wù)保存點SAVEPOINTidentifier;2.回滾到一個事務(wù)保存點ROLLBACKTOSAVEPOINTidentifier;3.刪除已命名的保存點RELEASESAVEPOINTidentifier;11.1事務(wù)【例11-6】在bank表中，設(shè)置事務(wù)的保存點。1）STARTTRANSACTION;2）UPDATEbankSETmoney=money-500WHEREname='張三';SELECT*FROMbank;3）SAVEPOINTs1;11.1事務(wù)4）UPDATEbankSETmoney=money-100WHEREname='張三';SELECT*FROMbank;5）ROLLBACKTOSAVEPOINTs1;SELECT*FROMbank;11.1事務(wù)6）ROLLBACK;SELECT*FROMbank;

8）COMMIT;11.1事務(wù)11.1.4事務(wù)的隔離級別MySQL的4種隔離級別1）READUNCOMMITTED（讀未提交）2）READCOMMITTED（讀已提交）3）REPEATABLEREAD（可重復(fù)讀）4）SERIALIZABLE（可串行化）11.1事務(wù)2.查看隔離級（1）查看全局隔離級SELECT@@global.transaction_isolation;（2）查看當(dāng)前會話中的隔離級別SELECT@@session.transaction_isolation;11.1事務(wù)（3）查看下一個事務(wù)的隔離級別SELECT@@transaction_isolation;11.1事務(wù)3.設(shè)置隔離級別SET[{SESSION|GLOBAL}]TRANSACTIONISOLATIONLEVEL參數(shù)值;【例11-7】將當(dāng)前會話事務(wù)的隔離級別修改為READUNCOMMITTED，然后將當(dāng)前會話的事務(wù)級別修改為默認(rèn)的REPEATABLEREAD。1）SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;SELECT@@session.transaction_isolation;11.1事務(wù)2）SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;SELECT@@session.transaction_isolation;11.1事務(wù)4.定義事務(wù)的訪問模式（1）設(shè)置只讀事務(wù)SET[{SESSION|GLOBAL}]TRANSACTIONREADONLY;（2）恢復(fù)成讀寫事務(wù)SET[{SESSION|GLOBAL}]TRANSACTIONREADWRITE;11.1事務(wù)5.事務(wù)的隔離級別示例（1）讀取未提交（READUNCOMMITTED）-“臟讀”問題【例11-8】臟讀問題演示示例。王五轉(zhuǎn)賬給李四100元貨款，李四看到貨款到賬后給王五發(fā)貨，王五撤銷了轉(zhuǎn)賬。1）#客戶端A，李四所在的客戶端SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;2）USEstudentinfo;SELECT*FROMbankWHEREname='李四';11.1事務(wù)11.1事務(wù)3）#客戶端B，王五所在的客戶端USEstudentinfo;STARTTRANSACTION;SAVEPOINTs1; #創(chuàng)建轉(zhuǎn)賬前的事務(wù)保存點UPDATEbankSETmoney=money-100WHEREname='王五';UPDATEbankSETmoney=money+100WHEREname='李四';#為了讀者看到數(shù)據(jù)的全貌，同時顯示二者的記錄SELECT*FROMbankWHEREname='李四'ORname='王五';11.1事務(wù)11.1事務(wù)4）#為了讀者看到數(shù)據(jù)的全貌，同時顯示二者的記錄SELECT*FROMbankWHEREname='李四'ORname='王五';11.1事務(wù)5）ROLLBACKTOSAVEPOINTs1;COMMIT;SELECT*FROMbankWHEREname='李四'ORname='王五';11.1事務(wù)6）SELECT*FROMbankWHEREname='李四'ORname='王五';11.1事務(wù)（2）讀已提交（READCOMMITTED）-不可重復(fù)讀問題、幻讀問題【例11-9】不可重復(fù)讀問題演示示例。王五取出資金500元，資金管理者在同一個事務(wù)的兩個時刻讀出了不同的數(shù)據(jù)。1）#客戶端A，資金管理者SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;11.1事務(wù)2）USEstudentinfo;STARTTRANSACTION;SELECT*FROMbank;SELECTSUM(money)FROMbank;11.1事務(wù)3）#客戶端B，王五所在的客戶端USEstudentinfo;STARTTRANSACTION;UPDATEbankSETmoney=money-500WHEREname='王五';COMMIT;11.1事務(wù)4）SELECT*FROMbankt;SELECTSUM(money)FROMbank;11.1事務(wù)【例11-10】幻讀問題演示示例。在客戶端A中登錄資金管理者。在客戶端B中操作新增賬戶丁一，存款3000元。1）#客戶端A，資金管理者SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;2）USEstudentinfo;SELECTCOUNT(*)AS賬戶數(shù),SUM(money)AS余額總數(shù)FROMbank;11.1事務(wù)3）USEstudentinfo;STARTTRANSACTION;INSERTINTObank(id,name,money)VALUES(105,'丁一',3000);COMMIT;SELECT*FROMbank;11.1事務(wù)4）SELECTCOUNT(*)AS賬戶數(shù),SUM(money)AS余額總數(shù)FROMbank;11.1事務(wù)（3）可重復(fù)讀（REPEATABLEREAD）【例11-11】可重復(fù)讀演示示例。在客戶端A登錄資金管理者，在客戶端B中操作賬戶李四，李四取款1000元。1）#客戶端A，資金管理者SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;2）USEstudentinfo;SELECTCOUNT(*)AS賬戶數(shù),SUM(money)AS余額總數(shù)FROMbank;SELECT*FROMbank;11.1事務(wù)3）#客戶端B，李四取款1000元USEstudentinfo;STARTTRANSACTION;UPDATEbankSETmoney=money-1000WHEREname='李四';SELECT*FROMbank;11.1事務(wù)4）SELECTCOUNT(*)AS賬戶數(shù),SUM(money)AS余額總數(shù)FROMbank;SELECT*FROMbank;11.1事務(wù)5）COMMIT;6）SELECTCOUNT(*)AS賬戶數(shù),SUM(money)AS余額總數(shù)FROMbank;SELECT*FROMbank;11.1事務(wù)【例11-12】在可重復(fù)讀隔離級別下演示幻讀問題的示例。在客戶端A中登錄資金管理者，在客戶端B中操作刪除賬戶丁一。1）SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;2）USEstudentinfo;SELECT*FROMbank;11.1事務(wù)3）USEstudentinfo;STARTTRANSACTION;DELETEFROMbankWHEREname='丁一';SELECT*FROMbank;11.1事務(wù)4）SELECT*FROMbank;11.1事務(wù)5）COMMIT;6）SELECT*FROMbank;11.1事務(wù)（4）SERIALIZABLE（可串行化）-可串行化問題【例11-13】在可串行化隔離級別下數(shù)據(jù)庫運行過程的示例。在客戶端A登錄張三，設(shè)置未可串行化，查詢張三記錄；在客戶端B登錄李四，李四給