58數(shù)據(jù)庫3_transaction

?

?

?

transaction：

事務(wù)，由若干條語句組成的，指要做的一系列操作；

InnoDB引擎，支持事務(wù)；

?

ACID：

atomicity，一個事務(wù)是一個不可分割的工作單位，事務(wù)中包括的所有操作要么全部做完，要做什么都不做；

consistency，事務(wù)必須是使數(shù)據(jù)庫從一個一致性狀態(tài)變成另一個一致性狀態(tài)，一致性和原子性是密切相關(guān)的；

isolation，一個事務(wù)的執(zhí)行不能被其它事務(wù)干擾，即一個事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對并發(fā)的其它事務(wù)是隔離的，并發(fā)執(zhí)行的各個事務(wù)之間不能互相干擾；

durability，持久性也稱永久性permanence，指一個事務(wù)一旦提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就應(yīng)該是永久性的，接下來的其它操作或故障不應(yīng)該對其有任何影響；

?

注：

atomicity要求事務(wù)中的所有操作，不可分割，不能做了一部分操作，還剩一部分操作；

consistency，多個事務(wù)并行執(zhí)行的結(jié)果，應(yīng)該和事務(wù)排隊執(zhí)行的結(jié)果一致，如果事務(wù)的并行執(zhí)行和多線程讀寫共享資源一樣不可預(yù)期，就不能保證一致性；

isolation，指多個事務(wù)訪問共同的數(shù)據(jù)了，應(yīng)該互不干擾，隔離性指究竟在一個事務(wù)處理期間，其它事務(wù)能不能訪問的問題；

durability，事務(wù)提交后，數(shù)據(jù)不能丟失；

?

?

隔離性不好，帶來的問題：

1、lost update更新丟失：

不允許事務(wù)A和事務(wù)B并行；

例，事務(wù)A和事務(wù)B，更新同一個數(shù)據(jù)，它們都讀取了初始值100，A要減10，B要加100，A減去10后更新為90，B加100更新為200，A的更新丟失了；

?

2、dirty read臟讀：

只用于展示可以，用于計算可以；

事務(wù)B讀到了事務(wù)A未提交的數(shù)據(jù)，這個數(shù)據(jù)可能是一個中間值，也可能事務(wù)A之后回滾事務(wù)，事務(wù)A是否最后提交并不關(guān)心，只要讀到了這個被修改的數(shù)據(jù)就是臟讀，隔離不好，讀到了未提交的數(shù)據(jù)（中間狀態(tài)值）；

?

3、unrepeatable read不可重復(fù)讀：

允許后一次可讀到正確的內(nèi)容；

不能保證同一條查詢語句重復(fù)讀相同的結(jié)果，就是不可重復(fù)讀；

例，事務(wù)A在同一事務(wù)中執(zhí)行相同查詢語句（先后查詢了2次），得到了不同的結(jié)果（不能重復(fù)的獲取相同數(shù)據(jù)）；

例，事務(wù)A在查詢了一次后，事務(wù)B修改了數(shù)據(jù)，事務(wù)A又查詢一次，發(fā)現(xiàn)數(shù)據(jù)不一致了；沒說提交；

注：臟讀是可以讀到相同數(shù)據(jù)的，但讀取的是一個未提交的數(shù)據(jù)，不是提交的最終結(jié)果；

?

4、phantom read幻讀：

例，事務(wù)A中同一個查詢要進行多次，事務(wù)B插入數(shù)據(jù)，導(dǎo)致事務(wù)A返回了不同的結(jié)果集，如同幻覺；

數(shù)據(jù)集有記錄增加了，可以看作是增加了記錄的不可重復(fù)讀；

?

有以上問題，數(shù)據(jù)庫必須要解決，解決辦法：1、隔離級別；2、加鎖；

?

隔離級別

由低到高，依次為：

read uncommitted，讀取到未提交數(shù)據(jù)，讀不受約束；

read commited，讀已經(jīng)提交的數(shù)據(jù)，oralce默認；

repeabable read，可以重復(fù)讀，MySQL默認，解決不可重復(fù)讀；

serializable，串行化，事務(wù)間完全隔離，不能并發(fā)只能串行，解決了所有問題；

?

隔離級別越高，串行化越高，數(shù)據(jù)庫執(zhí)行效率越低，當前事務(wù)處理的中間結(jié)果對其它事務(wù)不可見程度越高；

隔離級別越低，并行度越高，性能越高；

?

會話級別|全局級別：

>set [session|global] transaction isolation level LEVEL;?? #生產(chǎn)中慎用global

>select @@global.tx_isolation;

>select @@tx_isolation;

>set session transaction isolation level read committed;

?

serializable，串行了，解決所有問題；

?

repeatable read：

事務(wù)A中同一條查詢語句返回同樣的結(jié)果，就是可以重復(fù)讀數(shù)據(jù)了，解決辦法有：

1、對select的數(shù)據(jù)加鎖，不允許其它事務(wù)有刪除、修改操作，如for update；

2、第一次select時，對最后一次確切提交的事務(wù)的結(jié)果的快照；

以上解決了不可重復(fù)讀，但有可能出現(xiàn)幻讀；

?

read committed：

在事務(wù)中，每次select可以讀到別的事務(wù)剛提交成功的新的數(shù)據(jù)，因為讀到的是提交后的數(shù)據(jù)，解決了臟讀，但不能解決不可重復(fù)讀的問題；

?

read uncommitted：

能讀取到別的事務(wù)還沒提交的數(shù)據(jù)，完全沒有隔離性可言，出現(xiàn)了臟讀；

?

事務(wù)語法：

>start transaction?? #或>begin開始一個事務(wù)，>start transaction是標準sql語法；

>commit?? #提交事務(wù)后，變更成為永久變更；

>rollback?? #可在提交事務(wù)之前，回滾變更，事務(wù)中的操作就如同沒有發(fā)生過一樣；

>set autocommit=0?? #默認autocommit模式，可禁用或啟用，用于當前連接，出錯也可自動回滾，0禁用自動提交事務(wù)，如果開啟自動提交，若有一個修改表的語句，執(zhí)行后會立即把更新存儲到磁盤；開發(fā)時一般會關(guān)掉此項，性能問題，是一批批的提交，而不是一句句的提交；

?

?

數(shù)據(jù)倉庫和數(shù)據(jù)庫的區(qū)別：

本質(zhì)上來說沒有區(qū)別，都是存放數(shù)據(jù)的地方；

數(shù)據(jù)庫支持在線業(yè)務(wù)，需要頻繁增刪改查；數(shù)據(jù)倉庫一般囤積歷史數(shù)據(jù)支持用于分析的SQL，一般不建議刪改；

?

數(shù)據(jù)庫關(guān)注數(shù)據(jù)的持久化、數(shù)據(jù)的關(guān)系，為業(yè)務(wù)系統(tǒng)提供支持、事務(wù)支持；

OLTP，在線交易數(shù)據(jù)，數(shù)據(jù)庫；

?

數(shù)據(jù)倉庫，存儲的數(shù)據(jù)是為了分析或者發(fā)掘而設(shè)計的表結(jié)構(gòu)，可以存儲海量數(shù)據(jù)；

數(shù)據(jù)倉庫存儲歷史數(shù)據(jù)用于分析OLAP；

?

?

其它概念：

cursor游標：

操作查詢的結(jié)果集的一種方法；

可將游標當作一個指針，指向結(jié)果集中的某一行；

?

stored procedure存儲過程、trigger觸發(fā)器：

這兩種技術(shù)是DB的高級內(nèi)容，但基本很少用了，邏輯前移，BS|CS放在B或C上了；

stored procedure，數(shù)據(jù)庫系統(tǒng)中，一段完成特定功能的SQL語句，編寫成類似函數(shù)的方式，可以傳參并調(diào)用，支持流程控制語句；

trigger，由事件觸發(fā)的特殊的存儲過程，如Insert數(shù)據(jù)時觸發(fā)；trigger功能雖強大，但會有性能問題；

?

?

例：

mysql> set autocommit=0;

mysql> show variables like 'autocommit';

mysql> show variables like 'tx_isolation';

mysql> select * from t;

mysql> set session transaction isolation level read committed;

?

注：

set autocommit=0;關(guān)閉自動提交；

兩個窗口均默認級別REPEATABLE-READ，

A端update t set id=4 where id=2，

A端未commit，A端查詢是改變后的狀態(tài)，B端查詢沒變化，

A端commit后，A端查詢是改變后的狀態(tài)，B端查詢沒變化，

B端commit后，B端查詢是改變后的狀態(tài)；

?

兩個窗口用READ-COMMITTED，>set session transaction isolation level read committed;

A端insert into t values(6,'ftp',28);，

A端未commit，A端查詢是改變后的狀態(tài)，B端查詢沒變化；

A端commit，A端查詢是改變后的狀態(tài)，B端查詢是改變后的狀態(tài)；

?

mysql> select * from t for update;?? #InnoDB是行級鎖，此句相當于表級鎖；使用時，用幾行加幾行鎖，且加鎖時間越短越好

mysql> commit;?? #未commit，其它窗口的mysql> update t set id=5 where id=6;更新語句會卡住

?

?

?