上一篇我們聊到了mariadb的鎖，以及怎么手動加鎖和解鎖等等，回顧請參考https://www.cnblogs.com/qiuhom-1874/p/13196905.html；今天我們來聊一聊mariadb的事務隔離級別；在前邊的隨筆中，我們提到到了mysql的存盤引擎，常用的有MyISAM和innodb，其中myisam不支持事務，innodb支持事務；所以我們常說的事務是針對innodb存盤引擎來說的；所謂事務就是在我們在執行大批量陳述句時，為了保證資料庫的完整性，要么陳述句全部執行，要么陳述句全部不執行；所以事務必須滿足ACID這是個條件；A表示Atomicity，原子性，也是不可分性；C表示Consistency，一致性；I表示Isolation，隔離性，又稱獨立性；D表示Durability，持久性；所謂原子性，就是事務中執行的陳述句要么全部執行，要么全部不執行，如果事務在中途發生錯誤，那么前面執行過的陳述句將會回滾到事務前；一致性指的是在執行事務之前和事務執行完成后的資料庫狀態是完整的；也就是說我們執行的陳述句都按照我們預想的結果執行了；隔離性指資料庫允許多個并發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致資料的不一致，事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復讀（repeatable read）和串行化（Serializable），持久性指的是，事務執行完成后，對資料的修改是永久的；
接下來我們來說說怎么在mariadb中開啟事務吧
用戶手動開啟事務用start transaction 或者使用begin陳述句開啟事務；
提示：以上紅框中的陳述句就表示手動開啟一個事務；這里需要注意一點，對于mysql來講，默認在命令列執行的陳述句都是自動提交事務的；也就是說默認情況下我們在mysql shell中執行的陳述句它默認也會開啟一個事務，但我們陳述句執行完成后，它會自動把該事務提交；所以我們手動開啟一個事務可以使用start transaction 陳述句 或者begin 或者執行set autocommit=0來關閉自動提交事務；
提示：提交事務用commit 陳述句，表示結束當前事務；當然結束當前事務也可以用rollback陳述句，表示回滾到事務之前的狀態；回滾以后事務也就結束了；
SAVEPOINT identifier：創建一個保存點，一個事務中可以存在多個保存點，回滾時我們可以指定回滾到某個點上；
MariaDB [first_db]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [first_db]> select * from test_tb; +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | xiaoming | 44 | | 2 | zhangsan | 16 | +------+----------+------+ 2 rows in set (0.001 sec) MariaDB [first_db]> insert into test_tb values (3,"lisi",23); Query OK, 1 row affected (0.002 sec) MariaDB [first_db]> savepoint one; Query OK, 0 rows affected (0.001 sec) MariaDB [first_db]> insert into test_tb values (4,"wangwu",25); Query OK, 1 row affected (0.001 sec) MariaDB [first_db]> savepoint two; Query OK, 0 rows affected (0.000 sec) MariaDB [first_db]> select * from test_tb; +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | xiaoming | 44 | | 2 | zhangsan | 16 | | 3 | lisi | 23 | | 4 | wangwu | 25 | +------+----------+------+ 4 rows in set (0.001 sec) MariaDB [first_db]> rollback to one; Query OK, 0 rows affected (0.001 sec) MariaDB [first_db]> select * from test_tb; +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | xiaoming | 44 | | 2 | zhangsan | 16 | | 3 | lisi | 23 | +------+----------+------+ 3 rows in set (0.001 sec) MariaDB [first_db]>
提示：存在多個保存點，如果回滾到前邊的點以后，后面的保存點就沒有了；
接下來看看事務的隔離級別
對于mysql資料庫 innodb存盤引擎支持的事務隔離級別有4中，READ-UNCOMMITED：讀未提交；這表示事務還沒有結束，其他用戶或行程是可以實時的讀到事務中對資料的改變，因為事務還沒提交，所以我們認為其他用戶或行程讀到的資料就是一個不準確的資料，通常我們把這種叫臟讀；READ-COMMITTED：讀提交；這表示只有事務提交后，其他用戶或行程才可以讀到事務修改后的資料；這種事務隔離級別就要比前面的隔離級別要高一點，讀到的資料也要精準一點；這種隔離級別就是只要事務A提交了，在其他事務里就可以讀取到事務A修改后的資料；通常這種我們叫不可重復讀，不可重復讀表示在其他事務中，讀到的資料是根據事務A是否提交有關系；REPEATABLE-READ：可重復讀，這表示在A事務中修改后的資料，在B中事務上不可見的，即便A中事務已經提交，B中事務沒有提交，那么在B中的事務中讀到的還是A事務修改前的資料；只有當事務B結束后才能讀到A事務修改后的資料；所以通常我們把這種方式也叫幻讀，給人一種幻覺的感覺；SERIALIZABLE：串行化；這種隔離級別是最高的，但同時并發訪問也是最差的，它表示事務A和事務B是順序執行的，什么意思呢，就是說事務B必須要等到事務A完成后才可以執行；
關閉自動提交功能
MariaDB [first_db]> select @@session.autocommit; +----------------------+ | @@session.autocommit | +----------------------+ | 1 | +----------------------+ 1 row in set (0.001 sec) MariaDB [first_db]> set @@session.autocommit=0; Query OK, 0 rows affected (0.001 sec) MariaDB [first_db]> select @@session.autocommit; +----------------------+ | @@session.autocommit | +----------------------+ | 0 | +----------------------+ 1 row in set (0.001 sec) MariaDB [first_db]>
提示：以上陳述句表示設定當前會話關閉自動提交功能，關閉此會話它將恢復原有設定；
設定事務A的隔離級別為read-uncommitted
提示：事務隔離級別字串要加引號，否則會提示語法錯誤
設定會話B的隔離級別為read-uncommitted
在事務A中修改表中資料，在會話B中查看表中資料，看看是否在會話B中看到修改后的資料？
MariaDB [first_db]> BEGIN; Query OK, 0 rows affected (0.000 sec) MariaDB [first_db]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ 1 row in set (0.001 sec) MariaDB [first_db]> set @@session.tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.001 sec) MariaDB [first_db]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | +------------------------+ 1 row in set (0.001 sec) MariaDB [first_db]> select * from test_tb; +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | xiaoming | 44 | | 2 | zhangsan | 16 | +------+----------+------+ 2 rows in set (0.001 sec) MariaDB [first_db]> insert into test_tb values(2,"xiaohong",33); Query OK, 1 row affected (0.001 sec) MariaDB [first_db]> select * from test_tb; +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | xiaoming | 44 | | 2 | zhangsan | 16 | | 2 | xiaohong | 33 | +------+----------+------+ 3 rows in set (0.001 sec) MariaDB [first_db]>
提示：在事務A中插入資料在當前事務中是可以正常看到資料的變化；
提示：可以看到在會話B上是可以正常看到事務A中資料的修改；
提示：從上面的資訊可以看到，當事務A中的修改操作回滾時，在會話B中是可以查看到回滾后的資料；這也意味這在會話B中讀到的資料不是很準確；
設定事務A的事務隔離級別為read-committed
設定會話B的事務隔離級別為read-committed，然后在事務A上修改資料，不提交，看看會話B上是否可以看到？
MariaDB [first_db]> start transaction; Query OK, 0 rows affected (0.001 sec) MariaDB [first_db]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | +------------------------+ 1 row in set (0.001 sec) MariaDB [first_db]> set @@session.tx_isolation='read-committed'; Query OK, 0 rows affected (0.001 sec) MariaDB [first_db]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-COMMITTED | +------------------------+ 1 row in set (0.001 sec) MariaDB [first_db]> select @@session.autocommit; +----------------------+ | @@session.autocommit | +----------------------+ | 0 | +----------------------+ 1 row in set (0.001 sec) MariaDB [first_db]> select * from test_tb; +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | xiaoming | 44 | | 2 | zhangsan | 16 | +------+----------+------+ 2 rows in set (0.001 sec) MariaDB [first_db]> insert into test_tb values(3,"wangwu",43); Query OK, 1 row affected (0.001 sec) MariaDB [first_db]> select * from test_tb; +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | xiaoming | 44 | | 2 | zhangsan | 16 | | 3 | wangwu | 43 | +------+----------+------+ 3 rows in set (0.001 sec) MariaDB [first_db]>
在會話B中查看test_tb表，看看是否有能看到修改后的資料？
提示：在會話B中可以看到的test_tb表中的資料還是事務A開始前的資料；
提交事務A，看看會話B是否能夠看到修改后的資料呢？
提示：可以看到當事務A提交以后，在會話B中就可以看到事務A中修改后的資料；
設定事務A的事務隔離級別為repeatable-read
MariaDB [first_db]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [first_db]> set @@session.tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.000 sec) MariaDB [first_db]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ 1 row in set (0.000 sec) MariaDB [first_db]>
關閉事務B的自動提交功能并設定事務B的隔離級別為repeatable-read
在事務A中修改資料，并提交，看看在事務B中是否能看到修改后的資料？
在事務B中查看test_tb表中的資料，看看是否有變化呢？
提示：可以看到在事務B中無論怎么查看資料都是沒有發生變化；
提交事務B在查看資料看看是否有變化？
提示：可以看到當事務B提交以后，再次查看表中資料，就可以看到事務A更改以后的表資料了；
設定會話A的事務隔離級別為serializable
設定會話B的事務隔離級別為serializable
在事務A，事務B中更新表中同一條資料，看看會發生什么？
提示：在事務B中成功修改了第一條資料；
提示：在事務A上就不能修改第一條資料了，那我們在事務A上是否可修改第二條資料呢？
提示：可以看到第二條資料是能夠修改的；
提交事務B，看看A事務上是否可以修改第一條資料呢？
提示：提交了事務B以后，在會話B上再次查看test_tb表中的資料，發現執行不了，原因是事務A修改了第二條資料，還未完成事務，所以一直阻塞；
在事務A上修改第一條資料，看看是否可修改？
提示：提交了事務B以后，在事務A上還是不能夠修改第一條資料；原因是事務A修改了第二條資料，事務還沒有結束，所以第三個事務就無法執行，所以我們在會話B上是不能夠查看資料，因為默認情況查看資料也會啟動一個事務；
結束事務A，在會話B上看看是否可查詢？
提示：回滾了事務A，事務A也就結束了；在事務B中修改的資料，在事務A上做回滾，是回滾不回去的；這說明回滾操作只針對當前事務；
提示：結束了事務A以后，在會話B上就可以正常查看test_tb上的資料了；
以上就是在mariadb資料庫上的事務的四種隔離級別的區別；從上面的演示可以看到，串行化的隔離級別最高，但是并發連接也是最差的，因為它必須要等到前一個事務結束后才可以執行后面的事務；其次就是可重讀，可重讀必須是兩個事務都結束以后才可以看到真實修改后的資料；再其次就是不可重讀，讀提交，這種隔離級別必須是一方事務提交以后其他事務才可以讀到真實修改資料；而隔離級別最低就是對未提交，這種隔離級別只要是事務中修改了，其他事務上就能夠讀到相應的資料；當然這種讀到的資料也是最不靠譜的；
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/19723.html
標籤：MySQL
上一篇：50道SQL面試題