欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL之InnoDB下的鎖問題

 更新時間:2023年08月10日 15:18:28   作者:Redick01  
這篇文章主要介紹了MySQL之InnoDB下的鎖問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

背景知識

InnoDB相比較MyISAM一是支持事務,二是支持了行級鎖,提到InnoDB鎖問題就不得不提到事務,所以在這之前先了解下事務的一些知識

事務及其ACID屬性

事務是由一組SQL語句組成的邏輯處理單元,事務具有以下4個屬性,通常簡稱為事務的ACID屬性。

  • 原子性(Atomicity):事務開始后所有操作,要么全部做完,要么全部不做,不可能停滯在中間環(huán)節(jié)。事務執(zhí)行過程中出錯,會回滾到事務開始前的狀態(tài),所有的操作就像沒有發(fā)生一樣。也就是說事務是一個不可分割的整體,就像化學中學過的原子,是物質(zhì)構(gòu)成的基本單位。
  • 一致性(Consistency):事務開始前和結(jié)束后,數(shù)據(jù)庫的完整性約束沒有被破壞 。比如A向B轉(zhuǎn)賬,不可能A扣了錢,B卻沒收到。
  • 隔離性(Isolation):同一時間,只允許一個事務請求同一數(shù)據(jù),不同的事務之間彼此沒有任何干擾。比如A正在從一張銀行卡中取錢,在A取錢的過程結(jié)束前,B不能向這張卡轉(zhuǎn)賬。
  • 持久性(Durability):事務完成后,事務對數(shù)據(jù)庫的所有更新將被保存到數(shù)據(jù)庫,不能回滾。

并發(fā)事務處理帶來的問題

相對于串行處理來說,并發(fā)事務處理能大大增加數(shù)據(jù)庫資源的利用率,提高數(shù)據(jù)庫系統(tǒng)的事務吞吐量,從而可以支持更多的用戶。但是并發(fā)事務處理也會帶來以下問題。

  • 臟讀:事務A讀取了事務B更新的數(shù)據(jù),然后B回滾操作,那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)
  • 不可重復讀:事務 A 多次讀取同一數(shù)據(jù),事務 B 在事務A多次讀取的過程中,對數(shù)據(jù)作了更新并提交,導致事務A多次讀取同一數(shù)據(jù)時,結(jié)果不一致。
  • 幻讀:系統(tǒng)管理員A將數(shù)據(jù)庫中所有學生的成績從具體分數(shù)改為ABCDE等級,但是系統(tǒng)管理員B就在這個時候插入了一條具體分數(shù)的記錄,當系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來,就好像發(fā)生了幻覺一樣,這就叫幻讀。

小結(jié):不可重復讀的和幻讀很容易混淆,不可重復讀側(cè)重于修改,幻讀側(cè)重于新增或刪除。解決不可重復讀的問題只需鎖住滿足條件的行,解決幻讀需要鎖表

事務隔離級別

MySQL數(shù)據(jù)庫實現(xiàn)事務隔離的方式,基本上可分為兩種。

  • 一種是在讀取數(shù)據(jù)前,對其加鎖,阻止其他事務對數(shù)據(jù)進行修改
  • 不加鎖,通過一定機制生成一個數(shù)據(jù)請求時間點的一致性數(shù)據(jù)快照,并用這個快照來提供一定級別(語句級或事務級)的一致性讀取。從用戶的角度來看,好像是數(shù)據(jù)庫可以提供同意數(shù)據(jù)的多個版本,因此,這種技術(shù)叫做 數(shù)據(jù)多版本并發(fā)控制 簡稱MVCC。

四種事務隔離級別:

  • READ UNCOMMITTED(讀未提交):事務A和B操作同一數(shù)據(jù),事務A能夠讀到事務B未提交的數(shù)據(jù),會產(chǎn)生幻讀,不可重復讀,臟讀
  • READ COMMITTED(讀已提交):事務A和B操作同一數(shù)據(jù),事務A能夠讀到事務B更新的數(shù)據(jù),會產(chǎn)生幻讀和不可重復度
  • REPEATABLE READ(可重復讀):事務A和事務B操作同一數(shù)據(jù),事務A不能讀到事務B已經(jīng)插入的數(shù)據(jù),會產(chǎn)生幻讀
  • SERIALIZABLE(串行化):所有事務都必須保證串行執(zhí)行,不會產(chǎn)生臟讀,幻讀,不可重復度

獲取InnoDB行鎖爭用情況

可以通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)傷的行鎖的爭奪情況:

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     |
+-------------------------------+-------+
5 rows in set (0.04 sec)

如果鎖爭用比較嚴重,Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高,可以通過查詢information_schema數(shù)據(jù)庫中相關的表來查看鎖情況,或者通過設置InnodDB Monitors來進一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等,并分析其原因。

(1)通過查詢information_schema數(shù)據(jù)庫中的innodb_locks表了解鎖的等待情況:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from innodb_locks;
Empty set, 1 warning (0.01 sec)
mysql>

(2)通過設置InnoDB Monitors觀察鎖沖突情況:

mysql> create table innodb_monitor(a INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

然后通過下面語句來進行查看:

mysql> show engine innodb status;
| Type   | Name | Status                                                                                                                                                                                                                                                                                           
| InnoDB |      |
...
------------
TRANSACTIONS
------------
Trx id counter 6076
Purge done for trx's n:o < 6071 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421657844005624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421657844004704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421657844006544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
...
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140182422546176, state: sleeping
Number of rows inserted 55250, updated 1240, deleted 376, read 22512
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.01 sec)

監(jiān)視器可以通過下列語句來停止:

mysql&gt; drop table innodb_monitor;Query OK, 0 rows affected (0.02 sec)

設置監(jiān)視器后,在show innodb status的顯示內(nèi)容中,會有詳細的當前鎖等待的信息,包括表名、鎖類型、鎖定記錄的情況等,便于進一步分析和定位問題。

InnoDB的行鎖模式及加鎖方法

InnoDB實現(xiàn)了兩種類型的行鎖

  • 共享所(S):允許一個事務去讀一行,阻止其他事務獲得相同數(shù)據(jù)集的排他鎖。
  • 排他鎖(X):允許獲得排他鎖的事務更新數(shù)據(jù),阻止其他事務取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。

另外,為了允許行鎖和表鎖共存,實現(xiàn)多粒度鎖機制,InnoDB還有兩種內(nèi)部使用的意向鎖。

  • 意向共享鎖(IS):事務打算給數(shù)據(jù)行加共享鎖,事務在給一個數(shù)據(jù)行加共享鎖前必須先獲得該表的IS鎖。
  • 意向排他鎖(IX):事務打算給數(shù)據(jù)行加行排他鎖,事務在給一個數(shù)據(jù)行排他鎖前必須先獲得該表的IX鎖。

InnoDB行鎖模式兼容性列表:

XIXSIS
X沖突沖突沖突沖突
IX沖突兼容沖突兼容
S沖突沖突兼容兼容
IS沖突兼容兼容兼容

如果一個事務請求的鎖模式與當前的鎖兼容,InnoDB就將請求的鎖收于授予該事務,否則該事務就要等待鎖的釋放。

意向鎖是InnoDB自動加的,對于update,delete和insert語句,InnoDB是會自動給涉及數(shù)據(jù)集加排他鎖(X);對于普通SELECT語句,InnoDB不會加任何鎖;事務可以通過以下語句顯示給記錄集加共享鎖或排他鎖。

  • 共享鎖(S):select * from table_name where … lock in share mode;
  • 排他鎖(X):select * from table_name where … for update;

用lock in share mode獲得共享鎖,主要用在需要數(shù)據(jù)依存關系時來確認某行記錄是否存在,并確保沒有人對這個記錄進行update或delete。但是如果當前事務也需要對該記錄進行更新操作,則很有可能造成死鎖。

注:session1和session2是兩個連接到MySQL的客戶端,使用的數(shù)據(jù)庫是從mysql官網(wǎng)下載的,下載地址:http://downloads.mysql.com/docs/sakila-db.zip

下面是使用 lock in share mode加共享鎖的例子

session1:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor where actor_id=178;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

session2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor where actor_id=178;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

session1對actor_id=178的記錄加share mode的共享鎖,session2也對actor_id=178加share mode的共享鎖,此時session1和session2能夠加共享鎖,如下:

session1
mysql> select * from actor where actor_id=178 lock in share mode;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
session2
mysql> select * from actor where actor_id=178 lock in share mode;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

緊接著session1對178記錄進行update,此時session1會等待鎖,與此同時session2也對178記錄更新,此時session2發(fā)生死鎖,退出;

session1
mysql> update actor set last_name='monore t' where actor_id=178;
...等待
session2
mysql> mysql> update actor set last_name='monore t' where actor_id=178;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> update actor set last_name='monore t' where actor_id=178' at line 1

session2提交事務,session1獲取到鎖,update成功。

session2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
session1
Query OK, 1 row affected (49.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from actor where actor_id=178;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | monore t  | 2021-09-02 12:47:50 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

下面是使用for update加排他鎖的例子

session1對actor_id=178的行記錄使用for update加排他鎖,此時session2再次對178加排他鎖是不會獲取到鎖的,會等待。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from actor where actor_id=178 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | monore t  | 2021-09-02 12:47:50 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor where actor_id=178 for update;
...等待

session1提交事務,session2獲取到鎖。

session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
session2
mysql> select * from actor where actor_id=178 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | monore t  | 2021-09-02 12:47:50 |
+----------+------------+-----------+---------------------+
1 row in set (4.84 sec)

InnoDB行鎖的實現(xiàn)方式

InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的,如果沒有索引,InnoDB將通過隱藏的聚集索引Row_id來對記錄加鎖,InnoDB行鎖分為3種情形。

  • Record Lock:對索引項加鎖。
  • Gap lock:對索引項之間的“間隙”、第一條記錄前的“間隙”或最后一條記錄后的“間隙”加鎖。
  • Next-key lock:前兩種的組合,對記錄及其前面的間隙加鎖

InnoDB這種行鎖實現(xiàn)特點意味著:如果不通過索引條件檢索數(shù)據(jù),那么InnoDB將對表中的所有記錄加鎖,實際效果跟鎖表一樣!在實際應用中,要特別注意InnoDB行鎖這一特性,否則可能導致大量的鎖沖突,從而影響并發(fā)性能。

在不通過索引條件查詢時,InnoDB會鎖定表中的所有記錄

如下,payment表的amount字段沒有索引

session1加鎖查詢amount=8.99的數(shù)據(jù),然后session2在加鎖查詢amount=3.99的數(shù)據(jù),此時session2就會等待鎖,session1 commit后session2獲取到鎖查詢到數(shù)據(jù)??雌饋韘ession1只給amount=8.99的行加了鎖,但是卻出現(xiàn)了鎖等待,原因就是在沒有索引的情況下InnoDB對所有的記錄加鎖。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where amount=8.99 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         62 |           3 |        1 |   8.99 |
|         81 |           3 |        1 |   8.99 |
|         83 |           3 |        2 |   8.99 |
...
+------------+-------------+----------+--------+
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where amount=3.99 for update;
...等待

下面我們看一下通過索引條件加鎖時的情況,例如session1加鎖查詢payment_id=62,session2加鎖查詢payment_id=81;此時使用了索引,加鎖就只加在符合索引條件的記錄上了,并沒有出現(xiàn)等待鎖情況。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id=62 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         62 |           3 |        1 |   8.99 |
+------------+-------------+----------+--------+
1 row in set (0.00 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id=81 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         81 |           3 |        1 |   8.99 |
+------------+-------------+----------+--------+
1 row in set (0.00 sec)

由于MySQL的行鎖是對索引加的鎖

所以雖然訪問了不同的記錄,但是如果使用相同的索引鍵會出現(xiàn)沖突的

比如payment表staff_id有索引,amount沒有索引,session1加鎖查詢staff_id=1 and amount=8.99的記錄,session2加鎖查詢staff_id=1 and amount=3.99的記錄,session2就會等待獲取鎖,雖然訪問的是不同的行,因為鎖是加在索引上,所以會產(chǎn)生鎖沖突。session1 commit后session2獲取鎖成功。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where staff_id=1 and amount=8.99 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         62 |           3 |        1 |   8.99 |
|         81 |           3 |        1 |   8.99 |
|        122 |           5 |        1 |   8.99 |
|        188 |           7 |        1 |   8.99 |
...
+------------+-------------+----------+--------+
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where staff_id=1 and amount=3.99 for update;
...等待

當表有多個索引的時候

不同事務可以使用不同的索引鎖定不同的行,不論是使用主鍵索引、唯一索引或普通索引,InnoDB都會使用行鎖來對數(shù)據(jù)加鎖

payment表的customer_id和staff_id是索引,session1加鎖查詢customer_id=3,session2加鎖查詢staff_id=1的行,customer_id=3的數(shù)據(jù)中包含staff_id=1的數(shù)據(jù),session2會等待鎖,因為session1鎖了所有customer_id=3的行,包含staff_id=1的,所以session2或等待獲取鎖。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where customer_id=3 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         60 |           3 |        1 |   1.99 |
|         61 |           3 |        1 |   2.99 |
|         62 |           3 |        1 |   8.99 |
|         63 |           3 |        1 |   6.99 |
|         64 |           3 |        2 |   6.99 |
|         65 |           3 |        1 |   2.99 |
|         66 |           3 |        1 |   4.99 |
|         67 |           3 |        1 |   4.99 |
|         68 |           3 |        1 |   5.99 |
|         69 |           3 |        2 |  10.99 |
|         70 |           3 |        2 |   7.99 |
|         71 |           3 |        2 |   6.99 |
|         72 |           3 |        1 |   4.99 |
|         73 |           3 |        2 |   4.99 |
|         74 |           3 |        1 |   2.99 |
|         75 |           3 |        1 |   1.99 |
|         76 |           3 |        2 |   3.99 |
|         77 |           3 |        1 |   2.99 |
|         78 |           3 |        2 |   4.99 |
|         79 |           3 |        2 |   5.99 |
|         80 |           3 |        2 |   4.99 |
|         81 |           3 |        1 |   8.99 |
|         82 |           3 |        2 |   2.99 |
|         83 |           3 |        2 |   8.99 |
|         84 |           3 |        2 |   0.99 |
|         85 |           3 |        1 |   2.99 |
+------------+-------------+----------+--------+
26 rows in set (0.00 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where staff_id=1 for update;
...等待

注: 即便在條件中使用了索引字段,但是否使用索引來檢索數(shù)據(jù)是有MySQL通過判斷不同執(zhí)行計劃的代價來決定的,如果MySQL認為全表掃描效率更高,比如對一些很小的表就不會使用索引,這種情況InnoDB會對所有的行加鎖 ,因此在分析鎖沖突時別忘了分析sql執(zhí)行計劃。

Next-Key鎖

當用范圍條件而不是相等條件檢索數(shù)據(jù),并請求共享鎖或排他鎖時,InnoDB會給符合條件的數(shù)據(jù)行的索引項加鎖,對于鍵值在條件范圍內(nèi)但并不存在的記錄,叫做“間隙(GAP)”,InnoDB會對這個“間隙”加鎖,這種鎖機制就是所謂的Next-Key鎖。

舉個例子,加鎖查詢payment_id>16048的數(shù)據(jù),16049的記錄會加鎖,大于16049的記錄(不存在)的“間隙”也會加鎖。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id>16048 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|      16049 |         599 |        2 |   2.99 |
+------------+-------------+----------+--------+
1 row in set (0.00 sec)
session2
mysql> insert into payment (payment_id, customer_id, staff_id, amount) value (16050, 3, 2, 1.99);
... 等待

還要特別說的是,InnoDB除了通過范圍條件加鎖時使用Next-Key鎖外,如果使用相等條件請求一個不存在的記錄加鎖,InnoDB也會使用Next-Key。例如session1加鎖請求payment_id=16051的記錄(該記錄不存在),session2 插入payment_id=16051的記錄就會等待鎖。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id=16051 for update;
Empty set (0.01 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into payment (payment_id, customer_id, staff_id, amount) value (16051, 3, 2, 1.99);
...等待

什么時候用表鎖

  • 事務需要更新大部分或全部數(shù)據(jù),表比較大,如果使用行鎖,不僅這個事務執(zhí)行效率低,而且可能造成其他事務長時間鎖等待和鎖沖突,這種情況下可以考慮使用表鎖來提高事務的執(zhí)行效率。
  • 事務涉及到多個表,比較復雜,很可能引起死鎖,造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表,從而避免死鎖,減少數(shù)據(jù)庫因事務回滾帶來的開銷。

當然這兩種情況事務不能太多,否則,就應該考慮使用MyISAM表了。

死鎖

死鎖示例,session1加鎖查詢payment表payment_id=15866記錄,session2加鎖查詢actor表actor_id=200記錄,之后session1加鎖查詢actor表actor_id=200,此時因為session2持有鎖所以session1等待鎖,然后session2加鎖查詢payment表payment_id=15866記錄,這時InnoDB檢測到了死鎖,session2退出,session1查詢到actor_id=200的記錄。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from payment where payment_id=15866 for update;
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
| payment_id | customer_id | staff_id | rental_id | amount | payment_date        | last_update         |
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
|      15866 |         592 |        1 |     11410 |   8.99 | 2005-08-02 19:29:01 | 2006-02-15 22:23:29 |
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
1 row in set (0.00 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor where actor_id=200 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.01 sec)
session1
mysql> select * from actor where actor_id=200 for update;
... 等待鎖
session2,InnoDB檢測到死鎖,退出事務
mysql> select * from payment where payment_id=15866 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
session2退出事務,session1獲取到鎖
mysql> select * from actor where actor_id=200 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (6.53 sec)

通過過例子可以看出,InnoDB一般能自動檢測到死鎖,并且使一個事務釋放鎖并回退,另一個事務獲取到鎖后可以繼續(xù)正常執(zhí)行,但是涉及外部鎖以及表鎖的情況下,InnoDB并不能完全自動檢測到死鎖,這就需要設置鎖等待的超時時間innodb_lock_wait_timeout來解決。

這個參數(shù)并不是只用來結(jié)局死鎖問題,在并發(fā)比較高的情況下如果大量事務因無法立即獲得所需的鎖而掛起,會占用大量計算機資源,造成嚴重的性能問題,甚至拖垮數(shù)據(jù)庫。通過設置合適的鎖等待超市閾值可以避免這種情況發(fā)生。

避免死鎖的常用方法

  • 在應用中,如果不同的程序會并發(fā)存取多個表,應盡量約定以相同的順序來訪問表,這樣可以大大降低產(chǎn)生死鎖的機會。
  • 在程序以批量的方式處理數(shù)據(jù)的時候,如果實現(xiàn)對數(shù)據(jù)排序,保證每個線程按照固定的順序來處理記錄,也可以大大降低出現(xiàn)死鎖的可能。
  • 在事務中,如果要更新記錄,應該直接申請足夠級別的鎖,即排他鎖,不應該先申請共享鎖再在更新時申請排他鎖,防止其他事務在更新時獲取到共享鎖,造成鎖沖突甚至是死鎖。
  • 在REPEATABLE-READ隔離級別下,如果兩個線程同時對相同記錄用select … for update加排他鎖,在沒有符合記錄的情況下,兩個線程都會加鎖成功。程序發(fā)現(xiàn)記錄不存在,就試圖插入一條新紀錄,如果兩個線程都這么做,就會出現(xiàn)死鎖。
  • 當隔離級別為READ COMMITED時,如果兩個線程都先執(zhí)行select … for update,判斷是否存在符合條件記錄,如果沒有,就插入記錄。此時,只有一個線程能插入成功,另一個線程會出現(xiàn)鎖等待,當?shù)谝粋€線程提交后,第二個線程因主鍵重復出錯,雖然出錯了,卻會獲得一個排他鎖!這時如果第3個線程又來申請排他鎖,也會出現(xiàn)死鎖。

總結(jié)

  • InnoDB的行鎖是基于索引實現(xiàn)的,如果不通過索引加鎖訪問數(shù)據(jù),InnoDB會對所有數(shù)據(jù)加鎖。
  • 在不同隔離級別下,InnoDB的鎖機制和一致性讀取策略不通。
  • 介紹了Next-Key鎖機制,以及InnoDB使用Next-Key鎖的原因。
  • 精心設計索引,并盡量使用索引訪問數(shù)據(jù),使加鎖的粒度更小,從而減少鎖沖突的機會。
  • 選擇合理的事務,小事務發(fā)生死鎖的概率小。
  • 盡量使用相等的條件訪問數(shù)據(jù),避免Next-Key鎖對并發(fā)插入的影響。
  • 避免死鎖的一些常用方法
  • 對于一些特定的事務,可以利用表鎖來提高處理速度
  • 不要申請超過實際需要的鎖級別;除非必須,查詢時不要顯示加鎖。

以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。

相關文章

  • 淺談MySQL在cmd和python下的常用操作

    淺談MySQL在cmd和python下的常用操作

    下面小編就為大家?guī)硪黄獪\談MySQL在cmd和python下的常用操作。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧
    2017-06-06
  • MySQL8.0數(shù)據(jù)庫開窗函數(shù)圖文詳解

    MySQL8.0數(shù)據(jù)庫開窗函數(shù)圖文詳解

    開窗函數(shù)為將要被操作的行的集合定義一個窗口,它對一組值進行操作,不需要使用GROUP BY子句對數(shù)據(jù)進行分組,能夠在同一行中同時返回基礎行的列和聚合列,這篇文章主要給大家介紹了關于MySQL8.0數(shù)據(jù)庫開窗函數(shù)的相關資料,需要的朋友可以參考下
    2023-06-06
  • 一文教你MySQL如何優(yōu)化無索引的join

    一文教你MySQL如何優(yōu)化無索引的join

    所謂索引就是為特定的mysql字段進行一些特定的算法排序,比如二叉樹的算法和哈希算法,哈希算法是通過建立特征值,然后根據(jù)特征值來快速查找,下面這篇文章主要給大家介紹了關于MySQL如何優(yōu)化無索引join的相關資料,需要的朋友可以參考下
    2022-01-01
  • idea 設置MySql主鍵的實現(xiàn)步驟

    idea 設置MySql主鍵的實現(xiàn)步驟

    在IDE開發(fā)工具中也是可以使用mysql的,本文主要介紹了idea 設置MySql主鍵的實現(xiàn)步驟,文中通過圖文的非常詳細,需要的朋友們下面隨著小編來一起學習學習吧
    2024-03-03
  • mysql入門之1小時學會MySQL基礎

    mysql入門之1小時學會MySQL基礎

    今天剛好看到了SYZ01的這篇mysql入門文章,感覺對于想學習mysql的朋友是個不錯的資料,腳本之家特分享一下,需要的朋友可以參考下
    2018-01-01
  • MySQL(基于GTID方式)實現(xiàn)主從復制和單主復制詳細教程

    MySQL(基于GTID方式)實現(xiàn)主從復制和單主復制詳細教程

    在分布式數(shù)據(jù)庫系統(tǒng)中,主從復制是實現(xiàn)高可用性和數(shù)據(jù)冗余的重要手段,基于GTID的復制模式可以提供更強的復制一致性和簡化故障轉(zhuǎn)移過程,本文將詳細介紹如何配置單主復制的GTID模式,以便在MySQL數(shù)據(jù)庫中實現(xiàn)穩(wěn)定可靠的數(shù)據(jù)復制,需要的朋友可以參考下
    2024-07-07
  • 數(shù)據(jù)庫中間件MyCat的介紹

    數(shù)據(jù)庫中間件MyCat的介紹

    今天小編就為大家分享一篇關于數(shù)據(jù)庫中間件MyCat的介紹,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧
    2019-01-01
  • MySQL?Community?Server?8.0.29安裝配置方法圖文教程

    MySQL?Community?Server?8.0.29安裝配置方法圖文教程

    這篇文章主要為大家詳細介紹了MySQL?Community?Server?8.0.29安裝配置方法圖文教程,文中安裝步驟介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2022-06-06
  • 一文搞懂MySQL索引頁結(jié)構(gòu)

    一文搞懂MySQL索引頁結(jié)構(gòu)

    本文主要介紹了MySQL索引頁結(jié)構(gòu),文中通過示例代碼介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2022-02-02
  • 將mysql腳本轉(zhuǎn)化為oracle腳本的攻略與細節(jié)點

    將mysql腳本轉(zhuǎn)化為oracle腳本的攻略與細節(jié)點

    前段時間公司項目數(shù)據(jù)庫需要從mysql轉(zhuǎn)為oracle,所以需要修改下原有的mysql腳本,這篇文章主要給大家介紹了關于將mysql腳本轉(zhuǎn)化為oracle腳本的攻略與細節(jié)點,需要的朋友可以參考下
    2023-09-09

最新評論