SQL學(xué)習(xí)筆記13——SQL中事務(wù)控制語(yǔ)言TCL

• 事務(wù)控制語(yǔ)言TCL：Transaction Control Language
• 事務(wù)：一條或多條sql語(yǔ)句組成一個(gè)執(zhí)行單位，一組sql語(yǔ)句要么都執(zhí)行要么都不執(zhí)行
• MySQL中的存儲(chǔ)引擎：
  • 概念：在MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲(chǔ)在文件(或內(nèi)存)中
  • 通過(guò)show engines;來(lái)查看MySQL支持的存儲(chǔ)引擎
  • 在MySQL中用得最多的存儲(chǔ)引擎有：innodb，myisam，memory等，其中innodb支持事務(wù)，而myisam，memory等不支持事務(wù)

特點(diǎn)（ACID）

• A 原子性：一個(gè)事務(wù)是不可再分割的整體，要么都執(zhí)行要么都不執(zhí)行
• C 一致性：一個(gè)事務(wù)可以使數(shù)據(jù)從一個(gè)一致狀態(tài)切換到另外一個(gè)一致的狀態(tài)
• I 隔離性：一個(gè)事務(wù)不受其他事務(wù)的干擾，多個(gè)事務(wù)互相隔離的
• D 持久性：一個(gè)事務(wù)一旦提交了，則永久的持久化到本地

分類

• 隱式（自動(dòng)）事務(wù)：沒(méi)有明顯的開啟和結(jié)束，本身就是一條事務(wù)可以自動(dòng)提交，比如insert、update、delete
• 顯式事務(wù)：具有明顯的開啟和結(jié)束，前提：必須先設(shè)置自動(dòng)提交功能為禁用(set autocommit=0;只針對(duì)當(dāng)前事務(wù)有效，也就是每次都得set一下)

事務(wù)的使用步驟

• 使用顯式事務(wù)：
  1. 開啟事務(wù)

  set autocommit=0;
  start transaction;#可以省略
  

  2. 編寫事務(wù)中的sql語(yǔ)句（select insert update delete）（注意：DDL語(yǔ)言，drop等沒(méi)有事務(wù)之說(shuō)），設(shè)置回滾點(diǎn)：savepoint 回滾點(diǎn)名;
  3. 結(jié)束事務(wù)：commit;提交事務(wù) rollback;回滾事務(wù) rollback to 回滾點(diǎn)名;回滾到指定的地方

演示事務(wù)的使用步驟

#開啟事務(wù)
set autocommit=0;
start transaction;
#編寫事務(wù)中的sql語(yǔ)句
UPDATE account SET balance=500 WHERE username='張無(wú)忌';
UPDATE account SET balance=1500 WHERE username='趙敏';
#結(jié)束事務(wù)
COMMIT;

#開啟事務(wù)
set autocommit=0;
start transaction;
#編寫事務(wù)中的sql語(yǔ)句
UPDATE account SET balance=1000 WHERE username='張無(wú)忌';
UPDATE account SET balance=1000 WHERE username='趙敏';
#回滾事務(wù)
ROLLBACK;

并發(fā)事務(wù)

1. 事務(wù)的并發(fā)問(wèn)題是如何發(fā)生的？
   多個(gè)事務(wù)同時(shí)操作同一個(gè)數(shù)據(jù)庫(kù)的相同數(shù)據(jù)時(shí)
2. 并發(fā)問(wèn)題都有哪些？
   臟讀：一個(gè)事務(wù)讀取了其他事務(wù)還沒(méi)有提交的數(shù)據(jù)，讀到的是其他事務(wù)“更新”的數(shù)據(jù)
   不可重復(fù)讀：一個(gè)事務(wù)多次讀取，結(jié)果不一樣
   幻讀：對(duì)于兩個(gè)事務(wù)T1,T2,T1從一個(gè)表中讀取了一個(gè)字段，然后T2在該表中插入了一些新的行，之后，如果T1再次讀取同一個(gè)表，就會(huì)多出幾行
3. 如何解決并發(fā)問(wèn)題
   通過(guò)設(shè)置隔離級(jí)別來(lái)解決并發(fā)問(wèn)題

演示事務(wù)的隔離級(jí)別

• select @@transaction_isolation;查看當(dāng)前默認(rèn)隔離級(jí)別
• set 【session】 transaction isolation level read uncommitted設(shè)置當(dāng)前mysql連接的隔離級(jí)別為read uncommitted（省略session可能會(huì)導(dǎo)致不能立馬生效）（也可以用set 【session】 transaction_isolation = 'read-uncommitted',后面講變量時(shí)會(huì)講解 ）
• set global transaction isolation level read committed;設(shè)置數(shù)據(jù)庫(kù)系統(tǒng)的全局的隔離級(jí)別（一般需要重啟才生效）

演示savepoint的使用

SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#設(shè)置保存點(diǎn)
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滾到保存點(diǎn)

演示delete和truncate在事務(wù)使用時(shí)的區(qū)別

SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#delete可以回滾
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;
#truncate沒(méi)法回滾了