如上圖,四種隔離級(jí)別分別實(shí)現(xiàn)了不同的功能,看是級(jí)別越高也代表著需要的鎖越多,更容易產(chǎn)生阻塞。
接下來通過實(shí)驗(yàn)介紹這幾種讀
(1)臟讀
把隔離級(jí)別調(diào)為READ-UNCOMMITTED
root@localhost:MySQL.sock 01:30:05 [(none)]>set global tx_isolation = 'READ-UNCOMMITTED';
root@localhost:mysql.sock 01:30:22 [(none)]>show global variables like '%iso%';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
會(huì)話1:
root@localhost:mysql.sock 01:34:06 [lala]>select * from score;
Empty set (0.00 sec)
root@localhost:mysql.sock 01:34:53 [lala]>begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost:mysql.sock 01:35:23 [lala]>insert into score values(1,'xiaohong',99);
Query OK, 1 row affected (0.00 sec)
還沒有提交
會(huì)話2:
root@localhost:mysql.sock 01:37:08 [lala]>select * from score;
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 1 | xiaohong | 99 |
+------+----------+-------+
1 row in set (0.00 sec)
會(huì)話2可以直接讀到會(huì)話1還沒有提交的數(shù)據(jù)。
(2)不可重復(fù)讀
root@localhost:mysql.sock 01:30:05 [(none)]>set global tx_isolation = 'READ-COMMITTED';
root@localhost:mysql.sock 01:30:22 [(none)]>show global variables like '%iso%';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-COMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
會(huì)話1:
root@localhost:mysql.sock 01:49:00 [lala]>begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost:mysql.sock 01:49:08 [lala]>update score set id=3;
Query OK, 1 row affected (0.00 sec)
會(huì)話2:
root@localhost:mysql.sock 01:49:14 [lala]>select * from score;
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 1 | xiaohong | 99 |
+------+----------+-------+
1 row in set (0.00 sec)
會(huì)話1:
root@localhost:mysql.sock 01:49:20 [lala]>commit;
Query OK, 0 rows affected (0.00 sec)
會(huì)話2:
root@localhost:mysql.sock 01:49:47 [lala]>select * from score;
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 3 | xiaohong | 99 |
+------+----------+-------+
1 row in set (0.00 sec)
會(huì)話2第一次只讀到id的值是1,但以為會(huì)話1提交,會(huì)話2第二次讀到id的值是3.
(3)幻讀
會(huì)話1:
root@localhost:mysql.sock 01:49:00 [lala]>begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost:mysql.sock 01:49:08 [lala]>insert into score values(2,'xiaoming',65);
Query OK, 1 row affected (0.00 sec)
會(huì)話2:
root@localhost:mysql.sock 01:49:14 [lala]>select * from score;
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 1 | xiaohong | 99 |
+------+----------+-------+
1 row in set (0.00 sec)
會(huì)話1:
root@localhost:mysql.sock 01:49:20 [lala]>commit;
Query OK, 0 rows affected (0.00 sec)
會(huì)話2:
root@localhost:mysql.sock 01:50:27 [lala]>select * from score;
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 3 | xiaohong | 99 |
| 2 | xiaoming | 65 |
+------+----------+-------+
2 rows in set (0.00 sec)
會(huì)話2第一次只讀到1條數(shù)據(jù),但以為會(huì)話1提交,會(huì)話2第二次讀到兩條記錄
ps:不可重復(fù)讀是因?yàn)閿?shù)據(jù)的update導(dǎo)致,而幻讀是因?yàn)閿?shù)據(jù)的delete或者insert導(dǎo)致
mysql中的innodb的事務(wù)默認(rèn)隔離級(jí)別是Repeatable read(可重復(fù)讀),但是它不是普通的Repeatable read,它在可重復(fù)讀的基礎(chǔ)上避免了幻讀。是通過臭名昭著的gop鎖實(shí)現(xiàn)不可重復(fù)讀。
root@localhost:mysql.sock 01:17:04 [(none)]>show global variables like '%iso%';
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源:
創(chuàng)新互聯(lián)