1. Transaction

事務是由一組SQL語句組成的邏輯處理單元, 事務具有ACID屬性.

資料庫的事務隔離越嚴格,並發副作用越小, 但付出的代價也就越大. 這是因為事務隔離實質上是將事務在一定程度上"串行"進行,這顯然與"並發"是矛盾的. 根據自己的業務邏輯,權衡能接受的最大副作用.從而平衡瞭"隔離" 和 “並發”的問題. MySQL默認隔離級別是可重復讀.

臟讀,不可重復讀,幻讀, 其實都是資料庫讀一致性問題, 必須由資料庫提供一定的事務隔離機制來解決.

2. 四大特性 ACID

1. 原子性(Atomicity)
   • 事務是數據庫的邏輯工作單位, 事務中包括的各操作要麽都做.要麽都不做
   • 當時原子是不可分割的最小元素, 其對數據的修改, 要麼全部成功, 要麼全部都不成功
2. 一致性( Consistency)
   • 數據庫事務不能破壞關聯數據庫的完整性以及業務邏輯上的一致性
   • 事務開始到結束的時間段內, 數據都必須保持一致狀態
3. 隔離性( Isolation)
   • 一個事務的運行不能干擾其他事務. 即一個事務內部的操作及使用的數據對其他併發事務是隔離的, 並發運行的各個事務之間不能互相干擾
4. 持續性( Durability)
   • 永久性. 指一個事務一但提交, 它對數據庫中的數據的改變就應該是永久性的. 接下來的其他操作或故障不應該對其運行結果有什麽影響.

2.1. 情境

對於同一個銀行帳戶A內有200元. 甲進行提款操作100元, 乙進行轉帳操作將A帳號的100元轉到B帳戶.

假設事務沒有進行隔離可能會並發例如以下問題:

2.1.1. 臟讀（Dirty Reads）

一個事務讀到還有一個事務未提交的更新數據. 事務T1更新了數據還未提交,這時事務T2來讀取同樣的數據,則T2讀到的數據事實上是錯誤的數據,即臟數據 基於臟數據所作的操作是不可能正確的.

example:

1. 甲取款100元未提交到DB, 乙進行轉帳查到帳戶內剩有100元, 這時候甲放棄操作回滾, 乙正常操作提交. Ａ帳戶內變成0元(100-100). 乙讀取了甲的臟數據, 客戶損失100元

2. 甲提款時帳戶內有200元, 同一時候乙轉帳也是200元, 然後甲乙同一時候操作. 甲操作成功取走100元. 乙操作失敗回滾, 帳戶內終於為200元. 這樣甲的操作被覆蓋掉了, 銀行損失100元.

2.1.2. 不可重復讀（Non-Repeatable Reads）

一個事務的兩次讀取中, 讀取同樣的資源得到不同的值 事務A第一次讀取最初數據, 第二次讀取事務B已經提交的修改或刪除數據. 導致兩次讀取數據不一致. 不符合事務的隔離性.

example:

1. 甲乙同一時候開始都查到帳戶內為200元, 甲先開始取款100元提交, 這時乙在準備最後更新的時候又進行了一次查詢. 發現結果是100元, 這時乙就會非常困惑. 不知道該將帳戶改為100還是0.

2.1.3. 幻讀（Phantom Reads）

一個事務讀到還有一個事務已提交的新插入的數據 事務A根據相同條件第二次查詢到事務B提交的新增數據, 兩次數據結果集不一致.不符合事務的隔離性.

example:

1. T1刪除符合條件C1的全部數據. T2又插入了一些符合條件C1的數據, 則在T1中再次查找符合條件C1的數據還是能夠查到, 這對T1來說好像是幻覺一樣, 怎麼刪了又出現了

2.1.4. 幻讀和臟讀有點類似

臟讀是事務B裡面更新的數據 幻讀是事務B裡面新增的數據

2.2. 查看當前資料庫的事務隔離級別

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

3. 採用鎖來實現事務的隔離性

3.1. 所的基本原理

1. 當一個事務訪問某種數據庫資源時, 假設運行select語句, 必須先獲得共享鎖. 假設運行 insert、update 或者 delete 語句, 必須獲得排他鎖.
2. 當第二個事務也要訪問同樣的資源時. 假設運行select語句, 也必須先獲得共享鎖. 假設運行inert、update或者delete語句. 也必須先獲得排他鎖. 此時依據已經放置在資源上的鎖的類型, 來決定第二個事務應該等待還是馬上獲取該鎖.

3.2. 鎖的類型和兼容性

3.2.1. 共享鎖

共享鎖,也稱讀鎖,多用於判斷數據是否存在, 多個讀操作可以同時進行而不會互相影響. 當如果事務對讀鎖進行修改操作,很可能會造成死鎖.

來源: https://www.aiwalls.com/mysql/11/32324.html

• Transaction_A

mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4002' where id=4;
Query OK, 1 row affected (31.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0

• Transaction_B

mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4002' where id=4;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

3.2.2. 排他鎖

排他鎖,也稱寫鎖,排他鎖, 當前寫操作沒有完成前,它會阻斷其他寫鎖和讀鎖. 來源: https://www.aiwalls.com/mysql/11/32324.html

• Transaction_A

mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4000 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4001' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

• Transaction_B

mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (9.53 sec)

3.2.3. 更新鎖

• 更新鎖在更新操作的初始化階段用來鎖定可能要被改動的資源, 這樣能夠避免使用共享鎖造成的死鎖現象
• 假設兩個事務獲得了資源上的共享模式鎖,然後試圖同一時候更新數據.則一個事務嘗試將鎖轉換為排它鎖
• 一次僅僅有一個事務能夠獲得資源的更新鎖.假設事務改動資源,則更新鎖轉換為排他鎖.否則, 鎖轉換為共享鎖
• 共享模式到排它鎖的轉換必須等待一段時間,由於一個事務的排它鎖與其他事務的共享模式鎖不兼容；發生鎖等待.第二個事務試圖獲取排它鎖以進行更新.由於兩個事務都要轉換為排它鎖.而且每一個事務都等待還有一個事務釋放共享模式鎖.因此發生死鎖.
• 若要避免這樣的潛 在的死鎖問題,請使用更新鎖.

3.2.4. 共享鎖、排他鎖、更新鎖的特徵總結:

3.2.5. 避免死鎖的方法

1. 合理安排表訪問順序
2. 使用短事務
3. 假設對數據的一致性要求不是非常高,能夠同意臟讀
4. 假設可能的話,錯開多個事務訪問同樣數據資源的時間,以防止鎖沖突
5. 使用盡可能低的事務隔離級別

3.2.6. 行鎖/表級鎖/頁面鎖

3.2.6.1. 行鎖

通過檢查 InnoDB_row_lock 狀態變量分析系統上的行鎖的爭奪情況 show status like 'innodb_row_lock%';

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+

• innodb_row_lock_current_waits: 當前正在等待鎖定的數量
• innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度；非常重要的參數
• innodb_row_lock_time_avg: 每次等待所花平均時間；非常重要的參數
• innodb_row_lock_time_max: 從系統啟動到現在等待最常的一次所花的時間；
• innodb_row_lock_waits: 系統啟動後到現在總共等待的次數；非常重要的參數.直接決定優化的方向和策略.

3.2.6.1.1. 行鎖優化

1. 盡可能讓所有數據檢索都通過索引來完成,避免無索引行或索引失效導致行鎖升級為表鎖.
2. 盡可能避免間隙鎖帶來的性能下降,減少或使用合理的檢索范圍.
3. 盡可能減少事務的粒度,比如控制事務大小,而從減少鎖定資源量和時間長度,從而減少鎖的競爭等,提供性能.
4. 盡可能低級別事務隔離,隔離級別越高,並發的處理能力越低.

3.2.6.2. 表鎖

• 表鎖的優勢：開銷小；加鎖快；無死鎖
• 表鎖的劣勢：鎖粒度大,發生鎖沖突的概率高,並發處理能力低
• 加鎖的方式：自動加鎖.查詢操作（SELECT）,會自動給涉及的所有表加讀鎖,更新操作（UPDATE、DELETE、INSERT）,會自動給涉及的表加寫鎖.也可以顯示加鎖：
• MyISAM引擎只支持表鎖, 當sql執行時自動加上,InnoDB也支持表鎖,是在没有使用索引的時候,就會自動加表鎖
• 表鎖開小小,加鎖快,不會出現死鎖,但發生鎖衝突機率高,併發低；

3.2.6.2.1. 共享讀鎖(MyISAM)

對MyISAM表的讀操作(加讀鎖),不會阻塞其他進程對同一表的讀操作, 但會阻塞對同一表的寫操作. 只有當讀鎖釋放後,才能執行其他進程的寫操作.在鎖釋放前不能取其他表.

Transaction-A:

mysql> lock table myisam_lock read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> select * from innodb_lock;
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES

mysql> update myisam_lock set v='1001' where k='1';
ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Transaction-B:

mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> select * from innodb_lock;
8 rows in set (0.01 sec)

mysql> update myisam_lock set v='1001' where k='1';
Query OK, 1 row affected (18.67 sec)

3.2.6.2.2. 獨佔寫鎖(MyISAM)

對MyISAM表的寫操作（加寫鎖,會阻塞其他進程對同一表的讀和寫操作, 只有當寫鎖釋放後,才會執行其他進程的讀寫操作.在鎖釋放前不能寫其他表.

Transaction-A:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)

mysql> lock table myisam_lock write;
Query OK, 0 rows affected (0.03 sec)

mysql> update myisam_lock set v='2001' where k='2';
Query OK, 1 row affected (0.00 sec)

mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> update innodb_lock set v='1001' where k='1';
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Transaction-B:

mysql> select * from myisam_lock;
9 rows in set (42.83 sec)

3.2.6.2.3. 查看加鎖情況

show open tables; 1表示加鎖,0表示未加鎖.

mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock     | myisam_lock |      1 |           0 |
+----------+-------------+--------+-------------+

3.2.6.2.4. 分析表鎖定

可以通過檢查table_locks_waited 和 table_locks_immediate 狀態變量分析系統上的表鎖定：show status like 'table_locks%'

mysql> show status like 'table_locks%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 104   |
| Table_locks_waited         | 0     |
+----------------------------+-------+

• able_locks_immediate: 表示立即釋放表鎖數.
• table_locks_waited: 表示需要等待的表鎖數.此值越高則說明存在著越嚴重的表級鎖爭用情況.

此外,MyISAM的讀寫鎖調度是寫優先,這也是MyISAM不適合做寫為主表的存儲引擎.因為寫鎖後,其他線程不能做任何操作,大量的更新會使查詢很難得到鎖,從而造成永久阻塞.

3.2.6.2.5. 什麼場景下用表鎖

InnoDB默認采用行鎖,在未使用索引字段查詢時升級為表鎖. MySQL這樣設計並不是給你挖坑.它有自己的設計目的.

即便你在條件中使用瞭索引字段,MySQL會根據自身的執行計劃,考慮是否使用索引(所以explain命令中會有possible_key 和 key). 如果MySQL認為全表掃描效率更高,它就不會使用索引,這種情況下InnoDB將使用表鎖,而不是行鎖. 因此,在分析鎖沖突時,別忘瞭檢查SQL的執行計劃,以確認是否真正使用瞭索引.

1. 第一種情況：全表更新.事務需要更新大部分或全部數據,且表又比較大.若使用行鎖,會導致事務執行效率低,從而可能造成其他事務長時間鎖等待和更多的鎖沖突.
2. 第二種情況：多表查詢.事務涉及多個表,比較復雜的關聯查詢,很可能引起死鎖,造成大量事務回滾.這種情況若能一次性鎖定事務涉及的表,從而可以避免死鎖、減少資料庫因事務回滾帶來的開銷.

3.2.6.2.6. 總結：表鎖,讀鎖會阻塞寫,不會阻塞讀.而寫鎖則會把讀寫都阻塞.

3.2.6.3. 頁鎖

開銷和加鎖時間介於表鎖和行鎖之間；會出現死鎖；鎖定粒度介於表鎖和行鎖之間,並發處理能力一般.只需瞭解一下.

3.2.7. 總結

1. InnoDB 支持表鎖和行鎖,使用索引作為檢索條件修改數據時采用行鎖,否則采用表鎖.
2. InnoDB 自動給修改操作加鎖,給查詢操作不自動加鎖
3. 行鎖可能因為未使用索引而升級為表鎖,所以除瞭檢查索引是否創建的同時,也需要通過explain執行計劃查詢索引是否被實際使用.
4. 行鎖相對於表鎖來說,優勢在於高並發場景下表現更突出,畢竟鎖的粒度小.
5. 當表的大部分數據需要被修改,或者是多表復雜關聯查詢時,建議使用表鎖優於行鎖.
6. 為瞭保證數據的一致完整性,任何一個資料庫都存在鎖定機制.鎖定機制的優劣直接影響到一個資料庫的並發處理能力和性能.

來源: https://www.aiwalls.com/mysql/11/32324.html

3.2.8. 意向共享鎖/意向排它鎖

1. 意向鎖(Intention Locks)：表明一個事務稍後要獲取表中某一行的共享鎖或排它鎖；
2. 意向共享鎖(IS)：事務打算給數據行加共享鎖時,那麽事務在給一個數據行加S鎖前必須先取得該表的IS鎖；
3. 意向排它鎖(IX)：事務打算給數據行加排它鎖時,那麽事務在給一個數據行加X鎖前必須先取得該表的IX鎖；
4. 意向鎖是由存儲引擎自己維護的,用户無法手動操作意向鎖,在為數據行加共享鎖或排它鎖之前, InnoDB會先獲取該數據行所在的數據表對應的意向鎖；

來源: https://www.cnblogs.com/ruhuanxingyun/p/11615513.html

3.2.9. 自增鎖

指事務插入自增列(ID)的時候需要的鎖, 一個事務正在插入紀錄,其他事務插入紀錄需要等待釋放自增鎖；

3.2.10. 悲觀鎖

指在應用程序中顯式的為數據資源加鎖.會影響並發性能.

實現方式：

1. 在應用程序中顯示指定採用數據庫系統的獨佔鎖來鎖定數據源
2. 通過在數據庫表中添加一個標記字段, 來推斷事務能否夠訪問

利用數據庫系統的獨佔鎖来實現悲觀鎖：

1. SQL语句控制： select .. for update;
2. Hibernate中使用get()或者load()方法時： Account account = (Account)session.get(Account.class,new Long(1),LockMode.UPGRADE);

3.2.11. 樂觀鎖

樂觀鎖假定當前事務操作數據資源時, 不會有其它事務同一時候訪問該數據資源. 因此全然依靠數據庫的隔離級別來自己主動管理鎖

利用Hibernate的版本控制来實現樂觀鎖: 基本的實現邏輯是：在數據資料庫裡定義一個表示版本的字段, 或者一個時間戳字段,然後在映射文件中配置一個元素,或者一個元素.

3.2.12. 紀錄鎖/間隙鎖/臨鍵鎖

3.2.12.1. 紀錄鎖(Record Locks)

• 僅僅鎖住索引紀錄的一行, 也叫行鎖
• 鎖住的永遠是索引紀錄而非紀錄本身

3.2.12.2. 間隙鎖(Gap Locks)

1. 定義: 是没有匹配紀錄,鎖住索引紀錄中的間隙,或者第一條索引紀錄之前的範圍,又或者最後一條索引紀錄之後的範圍,是鎖住一個索引區間(左開右閉)； 間隙鎖會封鎖該條紀錄相鄰兩個鍵之間的空白區域,防止其它事務在這個區域内插入、修改、删除數據,這是為了防止出現幻讀現象.
2. 特色：間隙鎖只在事務隔離級别RR下才有,一方面是防止產生幻讀的問題,另一方面是為了滿足恢復和復制的需要；
3. 產生條件：使用普通索引鎖定、使用多列唯一索引、使用唯一索引鎖定多行紀錄；
   1. 唯一索引只有鎖住多條紀錄或者一條不存在的紀錄的时候,才會產生間隙鎖,指定给某條存在的紀錄加鎖的时候,只會加紀錄鎖,不會產生間隙鎖；
   2. 普通索引不管是鎖住單條,還是多條紀錄,都會產生間隙鎖.
4. 查看鎖：show variables like 'innodb_locks_unsafe_for_binlog',默認值為OFF,即啟用間隙鎖.

3.2.12.3. 臨鍵鎖(Next-key Locks)

1. 定義：是匹配到了紀錄,指鎖住索引紀錄和索引間隙,是innodb引擎默認的加鎖方式；
2. 是紀錄鎖和間隙鎖的組合；
3. 使用在不同的場景會演變成不同的鎖類型(紀錄鎖還是間隙鎖)

3.2.12.4. 插入意向鎖(Insert Intention Locks)

1. 是一種間隙鎖,而非意向鎖,在insert操作時產生,屬於行鎖；
2. 他不會阻止任何鎖,對於插入的紀錄會持有一個紀錄鎖

3.2.13. 鎖相關

1. 查看當前運行的所有事務：select * from information_schema.innodb_trx;
   1. 删除相應進程：kill trx_mysql_thread_id；
2. 查看正在鎖的事務：select * from information_schema.innodb_locks;
3. 查看等待鎖的事務：select * from information_schema.innodb_lock_waits;

3.3. 鎖的多粒度性及自己主動鎖升級

數據庫系統可以鎖定的資源包含:數據庫、表、區域、頁面、鍵值（指帶有索引的行數據）、行.

3.4. 依照鎖定資源的粒度, 鎖能夠分為下面類型: 顆粒大->小

1. 數據庫級鎖:鎖定整個數據庫.
2. 表級鎖:鎖定一張數據庫表.
3. 區域級鎖:鎖定數據庫的特定區域.
4. 頁面級鎖:鎖定數據庫的特定頁面.
5. 鍵值級鎖:鎖定數據庫表中帶有索引的一行記錄.
6. 行級鎖:鎖定數據庫表中的單行記錄.

鎖的粒度越大, 事務間的隔離性就越高, 事務間的並發性能就越低. 鎖升級是指調整鎖的粒度.將多個低粒度的鎖替換成少數更高粒度的鎖.以此來減少系統負荷.

4. 數據庫的隔離級別

數據庫提供了4種事務隔離級別供用戶選擇

4.1. 未提交讀(Read Uncommited)

一個事務在運行過程中能夠看到其它事務沒有提交的新插入的記錄, 並且能看到其它事務沒有提交的對已有記錄的更新.

4.2. 已提交讀(Read Commited)

一個事務在運行過程中能夠看到其它事務已經提交的新插入的記錄. 並且能看到其它事務已經提交的對已有記錄的更新.

4.3. 可重復讀(Repeatable Read)

一個事務在運行過程中能夠看到其它事務已經提交的新插入的記錄, 可是不能看到其它其它事務對已有記錄的更新.

4.4. 可序列化(Serializable)

一個事務在運行過程中全然看不到其它事務對數據庫所做的更新（事務運行的時候不同意別的事務並發運行.事務串行化運行, 事務僅僅能一個接著一個地運行, 而不能並發運行）

理解資料庫『悲觀鎖』和『樂觀鎖』的觀念

https://medium.com/dean-lin/%E7%9C%9F%E6%AD%A3%E7%90%86%E8%A7%A3%E8%B3%87%E6%96%99%E5%BA%AB%E7%9A%84%E6%82%B2%E8%A7%80%E9%8E%96-vs-%E6%A8%82%E8%A7%80%E9%8E%96-2cabb858726d

5. Reference

• https://www.itread01.com/content/1501232306.html
• https://www.aiwalls.com/mysql/11/32324.html
• https://www.cnblogs.com/tq03/p/3789434.html?utm_source=tuicool&utm_medium=referra
• http://blog.chinaunix.net/uid-22741583-id-127431.html
• https://www.cnblogs.com/ruhuanxingyun/p/11615513.html
• https://kknews.cc/code/3apjm2a.html