简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）
首頁
文章
問答
課程
專題
下載
遊戲
詞典

首頁  >  文章  >  資料庫  >  深入了解MySQL中的事務和鎖

深入了解MySQL中的事務和鎖

青灯夜游
青灯夜游轉載
2021-09-10 19:32:501696瀏覽

深入了解MySQL中的事務和鎖

MySQL資料庫是一個多用戶存取系統，那麼就要面臨當多個用戶同時讀取和更新資料時，資料不會被破壞，所以就誕生了鎖，鎖一種並發控制技術，當一個使用者嘗試修改資料庫中的記錄時，首先要取得鎖，那麼持有這個鎖的使用者還在修改時，其他使用者就不能對這些記錄進行修改了。 【相關推薦：mysql影片教學】

MySQL中的鎖定

##但是相對其他資料庫而言，MySQL的鎖定機制比較簡單， MySQL不同的儲存引擎有不同的鎖定機制，MylSAM和MEMORY儲存引擎採用的是表級鎖，BDB儲存引擎採用的是頁面鎖，而常用的InnoDB儲存引擎支援行級鎖、表級鎖，預設情況下是採用行級鎖。

這3種鎖的特性如下：

  • 表級鎖：開銷小，加鎖快，不會出現死鎖，鎖定粒度大，發生鎖定衝突的機率最高，並發度最低。

  • 行級鎖：開銷大，加鎖慢，會出現死鎖，鎖定粒度最小，發生鎖衝突的機率最低,並發度也最高。

  • 頁面鎖定：開銷和加鎖時間界於表鎖和行鎖之間，會出現死鎖，鎖定粒度界於表鎖和行鎖之間，並發度一般。

MyISAM

#MyISAM表鎖定

MySQL為表提供了兩種類型的鎖，它們是：

  • READ LOCK： 允許使用者只從表中讀取資料。

  • WRITE LOCK： 允許使用者對資料表進行讀取和寫入操作。

MyISAM對資料表的讀取操作，不會阻塞其他使用者對相同資料表的讀取請求， 但是會阻塞相同資料表的寫入請求，MyISAM對資料表的寫入操作，會阻塞其他使用者對同一表的讀取和寫入操作， MyISAM表的讀取操作與寫入操作之間，以及寫入操作之間是串行的。

MyISAM在執行查詢語句(SELECT)前，會自動將所有使用到的資料表加讀鎖定，在執行更新操作(UPDATE、DELETE、INSERT等)前，會自動為涉及的表格加寫鎖，這個過程並不需要我們手動幹預，所以我們一般不需要用LOCK TABLE指令來為MyISAM表明確加鎖，但是顯示加鎖也沒有什麼問題。

還有在用LOCK TABLES給表明確加表鎖時，必須同時取得所有涉及表的鎖，因為在執行LOCK TABLES後，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表，否則會出錯，同時，如果加的是讀鎖，那麼只能執行查詢操作，不能執行更新操作，否則也會報錯，在自動加鎖的情況下也是如此，這也正是MyISAM表不會出現死鎖的原因。

下面看一個列子。

1、建立一張表格

CREATE TABLE test_table (   
      Id INT NOT NULL AUTO_INCREMENT,   
      Name VARCHAR(50) NOT NULL,   
      Message VARCHAR(80) NOT NULL,  
      PRIMARY KEY (Id)  
);

2、會話1取得寫入鎖定

mysql> lock table  test_table write;
Query OK, 0 rows affected (0.01 sec)

3、會話2讀取。

我們知道在某個會話持有WRITE鎖定時，所有其他會話都無法存取該表的數據，所以在第二個會話執行下面語句時，會一直處於等待狀態。 

mysql> select * from test_table;

4、會話1解鎖

unlock table;

並發插入

在MyISAM裡讀寫操作是串列的，但可以根據concurrent_insert的設置，讓MyISAM支援並行查詢和插入。

concurrent_insert取值如下：

  • 0：不允許並發插入功能。

  • 1：允許對沒有空洞的表格使用並發插入，新資料位於資料檔案結尾（預設）。

  • 2：不管表有沒有空洞，都允許在資料檔案結尾並發插入。

空洞指的是表格的中間沒有被刪除的行。

InnoDB

InnoDB不同於MyISAM，他有兩個特點，一是支援事務，二是採用了行級鎖，行級鎖和表鎖有很多不同的地方。

交易特性

  • 原子性

    交易是一個原子操作單元， 對資料的修改，要麼全部執行，要么全都不執行。

  • 一致性

    在交易開始和完成時，資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改，以保持資料的完整性。

  • 隔離性

    資料庫系統保證事務在不受外部並發操作影響，可以"獨立"環境執行，這意味著事務處理過程中的中間狀態對外部是不可見的。

  • 持久性

    交易完成之後，它對於資料的修改是永久性的，即使出現系統故障也能夠維持。

並發事務處理帶來的問題

相對於串行處理來說，雖然提高了資源利用率，可以支援更多的用戶，但並發事務處理也會帶來些問題， 主要包括以下幾種情況。

更新遺失#

由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，也就是最后的更新覆盖了由其他事务所做的更新。

脏读

脏读又称无效数据的读出，当事务1将某一值修改后，然后事务2读取该值，后面事务1又因为一些原因撤销对该值的修改，这就导致了事务2所读取到的数据是无效的。

不可重复读

指的是一个事务在读取某些数据后，再次读取之前读过的数据，却发现读出的数据已经发生了改变。

幻读

当事务1按相同的查询条件重新读取以前查询过的数据时，却发现其他事务插入了满足这个条件的新数据。

事务隔离级别

上面说的"更新丢失"是应该完全避免的，但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁。

而脏读、不可重复读、幻读，都是数据库读一致性问题，必须由数据库提供事务隔离机制来解决。数据库实现事务隔离的方式，可分为以下两种，一种是在读取数据前加锁，阻止其他事务对数据进行修改，另一种不需要锁，通过MVCC或MCC来实现，这种技术叫做数据多版本并发控制，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行。

InnoDB有四个事务隔离级别： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默认隔离级别是REPEATABLE READ。

隔离级别脏读不可重复性幻读
读未提交√√√
读已提交×√√
可重复读取××√
可序列化（serializable）×××

查询/更改隔离级别

显示隔离级别
show global variables like '%isolation%';
select @@transaction_isolation;

设置隔离级别
set global transaction_isolation ='read-committed';
set session transaction isolation level read uncommitted;

READ UNCOMMITTED（读未提交）

在这个隔离级别，所有事务都可以看到其他未提交事务的执行结果。这种隔离级别在实际应用中很少使用，读取未提交的数据也称为脏读。

例子

启动两个会话，并设置隔离级别为READ UNCOMMITTED。

mysql> select * from user;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |     100 |
| 李四      |     100 |
| 王五      |      80 |
+-----------+---------+
时间事务1事务2
T1begin;begin;
T2select * from user where user_name="张三";
此时张三余额100
T3
select * from user where user_name="张三";
此时张三余额100
T4update user set balance =80 where user_name ="张三";
T4
select * from user where user_name="张三";
此时张三余额80
T5commitcommit

可以看到，在T4时刻，事务1没有提交，但是事务2可以看到被事务1锁更改的数据。

READ COMMITTED （读已提交）

这是大多数数据库系统的默认隔离级别，但不是MySQL的默认级别，他避免了脏读现象，因为在任何未提交的事务前，对任何其他事务都是不可见的，也就是其他事务看不到未提交的数据，允许不可重复读。

例子

将两个会话中隔离级别设置为读已提交
set session transaction isolation level read committed;
时间事务1事务2
T1begin;begin;
T2select * from user where user_name="张三";
此时张三余额100
T3
select * from user where user_name="张三";
此时张三余额100
T4update user set balance =80 where user_name ="张三";
T4
select * from user where user_name="张三";
此时张三余额100
T5commit
T5
select * from user where user_name="张三";
此时张三余额80

可以看到，在T4时刻，事务1没有提交，但是事务2读取到的数据还是100,当事务1提交后，事务2才可以看到。

REPEATABLE READ （可重复读）

这是 MySQL 的默认事务隔离级别，它确保同一事务读取数据时，将看到相同的数据行，但是会出现幻读，当事务1按条件进行查询后，另一个事务在该范围内插入一个新数据，那么事务1再次读取时，就会读到这个新数据。InnoDB 和 Falcon 存储引擎通过 mvcc（多版本并发控制）机制解决了这个问题。

例子

设置两个会话隔离级别为可重复读
set session transaction isolation level repeatable read;
时间事务1事务2
T1begin;begin;
T2update user set balance =80 where user_name ="张三";
T3commit;
T4
select * from user where user_name="张三";
张三余额为100

可以看到，在T3时刻，事务1已经提交更改，但是在T4时刻的事务2中，还是读取到了原来的数据，但是如果事务2在原来的基础上再减10元，那么最终余额是90还是70呢？,答案是70。.

mysql> update user set balance=balance-10 where user_name="张三";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user where user_name="张三";
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |      70 |
+-----------+---------+
1 row in set (0.00 sec)

SERIALIZABLE （序列化）

他是最高的隔离级别，InnoDB将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE，所有事务按照顺序依次执行，因此，脏读、不可重复读、幻读都不会出现。但是，由于事务是串行执行，所以效率会大大下降，

例子
设置隔离级别为序列化
set session transaction isolation level serializable;
时间事务1事务2
T1begin;begin;
T2select * from user where user_name="张三";
T3
update user set balance =80 where user_name ="张三";

这一次，有趣的是，事务2在T3时刻更新被阻止了,原因是在serializable隔离级别下，MySQL隐式地将所有普通SELECT查询转换为SELECT FOR SHARE， 持有SELECT FOR SHARE锁的事务只允许其他事务对SELECT行进行处理，而不允许其他事务UPDATE或DELETE它们。

所以有了这个锁定机制，我们之前看到的不一致数据场景就不再可能了。

但是，这个锁具有超时时间，在等待一会后，如果其他事务在这段时间内没有提交或回滚释放锁，将抛出锁等待超时错误，如下所示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB行锁

InnoDB 的行级锁也分为共享锁和排他锁两种。

  • 共享锁允许持有锁的事务读取行。

  • 独占锁允许持有锁事务的更新或删除行。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁。

  • 意向共享锁 事务想要获得一张表中某几行的共享锁。
  • 意向排他锁 事务想要获得一张表中某几行的排他锁。

InnoDB 行锁是通过锁定索引上的索引条目来实现的，因此，InnoDB 只有在通过索引条件检索到数据时才使用行级锁；否则，InnoDB 将使用表锁。

我们可以显示的加锁，但对于update、delete、insert语句，InnoDB会自动给涉及的数据集加排他锁，对于普通的 select 语句，InnoDB 不会加任何锁，下面是显示的加锁方式：

  • 共享锁：SELECT  FROM table_name WHERE … LOCK IN SHARE MODE
  • 排他锁：SELECT * FROM table_name WHERE … FOR UPDATE

Next-Key锁

当我们使用范围条件而不是相等条件检索数据，并请求其共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于在条件范围内但并不存在的记录，叫做间隙(GAP), InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的Next-Key锁。

举例来说，假如user表中只有101条记录，其user_id的值分别是1.2. ..100. 101，当查找大于100的user_id时，使用下面SQL。

select.* from emp where user_id > 100 for update;

这就是一个范围条件的查询， InnoDB不仅会对user_id为101的记录加锁，也会对user_id大于101的"间隙"加锁，虽然这些记录并不存在。

InnoDB使用Next-Key锁的目的，一方面是为了防止幻读，另一方面， 是为了满足恢复和复制的需要。

更多编程相关知识，请访问：编程视频！！

以上是深入了解MySQL中的事務和鎖的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：
本文轉載於：juejin.cn。如有侵權，請聯絡admin@php.cn刪除
上一篇：MySQL中怎麼啟用並分析慢查詢日誌？下一篇：MySQL中怎麼啟用並分析慢查詢日誌？

相關文章

看更多