2019年3月29日 星期五

【MYSQL】INNODB與MyISAM事務及鎖

INNODB與MYISAM的最大不同有兩點

  • 支持事務ACID
  • 使用行級鎖

INNODB行級鎖模式行級鎖模式可以在同時不同線程進按索引條件進行更新,同時又有可進行查詢的使用
  • 線程中訪問不同的行時只存在少量鎖定沖突
  • 回滾時只有少量的更改
  • 可以長時間鎖定單一個行
  • 行鎖必須有索引才能實現,否則會自動鎖全表,那麼就不是行鎖了
  • 兩個事務不能鎖同一個索引,例如兩個線程都執行同一個SQL,會鎖在同一個
  • insert ,delete , update在事務中都會自動默認加上排它鎖

行鎖分共享鎖和排它鎖

共享鎖又稱讀鎖:。當一個事務對某幾行上讀鎖時,允許其他事務對這幾行進行讀操作,但不允許其進行寫操作,也不允許其他事務給這幾行上排它鎖,但允許上讀鎖。


排它鎖又稱寫鎖:。當一個事務對某幾個上寫鎖時,不允許其他事務寫,但允許讀。更不允許其他事務給這幾行上任何鎖。包括寫鎖。

例如:select age from table_name where age >60 for update

MYSQL的事務介紹:

事務的特性ACID :由一組SQL語句(交易)組成的邏輯處理單元,事務具有ACID屬性

原子性(Atomicity):事務是一個整體被執行。包含事務對數據資料的操作,要麼全部成功,要麼全部都不成功。
一致性(Consistent):事務開始到結束的時間段內,資料都必須保持一致狀態。
隔離性(Isolation):資料庫系統提供一定的隔離機制,保證事務在不受外部併發操作影響的"獨立"環境執行。

永續性(Durable):事務完成後,它對於資料的修改是永久性的,即使出現系統故障也能夠保持。

事務的隔離級別(INNODB不建議更改默認REPEATABLE-READ)

·未提交讀(Read Uncommitted):允許Dirty Read(臟讀、亂讀、錯讀),

簡單的解釋就是可能讀取到其他會話中未提交事務修改(就是其他人的事務還未結束的情形下,還可以讀到他提交)的數據

·提交讀(Read Committed):只能讀取到已經提交的數據。Oracle等多數數據庫默認都是該級別(不重複讀)
·可重複讀(Repeated Read):可重複讀。在同一個事務內的查詢都是事務開始時刻一致的(同SQL查詢兩次數據應該是一樣的),InnoDB默認級別
,但是還存在幻讀。幻讀範例如下


+--------------------------------------------+--------------------------------------+
|          | A使用者                         | B使用者                              |
+--------------------------------------------+--------------------------------------+
| 第一步  | select * from test;             | start transaction;                   |
|          | Empty set  (0.01 sec)           | Query OK, 0 rows affected (0.00 sec) |
+--------------------------------------------+--------------------------------------+
| 第二步  |                                 | insert into test values(1,'C');      |
|          |                                 | Query OK, 1 row affected (0.01 sec)  |
+--------------------------------------------+--------------------------------------+
| 第三步  |                                 | commit;                              |
|          |                                 | Query OK, 1 row affected (0.01 sec)  |
+--------------------------------------------+--------------------------------------+
| 第四步  | select * from test;             |                                      |
|          | Empty set  (0.01 sec)           |                                      |
+--------------------------------------------+--------------------------------------+
| 第五步  | insert into test values(1,'C'); |                                      |
|          | ERROR 1062 (23000): Duplicate   |                                      |
|          | entry '1' for key 'PRIMARY'     |                                      |
+--------------------------------------------+--------------------------------------+
| 第六步  | select * from test;             |                                      |
|          | Empty set  (0.01 sec)           |                                      |
+--------------------------------------------+--------------------------------------+

說明:在A查詢時,沒資料,B開啟交易模式並插入一筆資料然後直到完成交易,在完成交易後A再次查詢仍返回沒資料,於是A也插入相同資料,但卻返回資料重複,但是再查詢,確還是沒有資料。

·串行讀也稱序列化(Serializable):完全串行化的讀,每次讀都需要獲得表級共享鎖,讀寫相互都會阻塞,會在讀取每一行數據都加鎖,所以可能導致大量的超時和鎖爭用問題。實際應用中也很少用到這個隔離級別,只有在非常需要確保數據的一致性而且可以接受沒有並發的情況下,才考慮採用該級別。


檢視當前資料庫的事務隔離級別
mysql> show variables like 'tx_isolation';
+---------------+-----------------+ | Variable_name | Value           | +---------------+-----------------+ | tx_isolation  | REPEATABLE-READ | +---------------+-----------------+
1 row in set (0.00 sec)

MyISAM表級鎖模式:就是整個表鎖起來
適用:以查詢為主,只有少量按索引更新資料的應用

  • 表共享讀鎖(Table Read Lock):可同時讀取表資料,但阻塞同一個表寫的請求,只有寫鎖釋放後,才會執行其他進程的寫操作。

    A使用者B使用者
    將table_name加入讀鎖
    mysql> lock table table_name read;
    Query OK, 0 rows affected (0.00 sec)
    連線進入資料庫
    可讀table_name表
    mysql> select * from table_name;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | c    |
    |  4 | d    |
    |  5 | e    |
    +----+------+
    5 rows in set (0.00 sec)
    可讀table_name表
    mysql> select * from table_name;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | c    |
    |  4 | d    |
    |  5 | e    |
    +----+------+
    5 rows in set (0.00 sec)
    不可以查看其他表
    mysql> select * from test_char;
    ERROR 1100 (HY000): Table 'test_char' was not locked with LOCK TABLES
    可以查看其他表
    mysql> select * from test_char;
    +------------------+
    | s                |
    +------------------+
    | 0120            |
    | 一二十一          |
    +------------------+
    2 rows in set (0.00 sec)
    不能更新或插入,會跳出錯誤訊息
    mysql> update table_name set name="cc" where id=2;
    ERROR 1099 (HY000): Table 'table_name' was locked with a READ lock and can't be updated
    不能更新或插入 按exter後會卡住,但沒有出錯誤訊息
    mysql> update table_name set name="cc" where id=1;
    釋放鎖後
    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)

    更新的部份不卡了,但時間花費很久
    mysql> update table_name set name="cc" where id=1;
    Query OK, 1 row affected (29.06 sec)
    Rows matched: 1  Changed: 1  Warnings: 0 

  • 表獨占寫鎖(Table Write Lock):又稱排它鎖,一個表只能一個人寫,兩個以上會阻塞。

    A使用者B使用者
    將table_name加入寫鎖
    mysql> lock table table_name write;
    Query OK, 0 rows affected (0.00 sec)
    連線進入資料庫
    不可以查看其他表
    mysql> select * from test_char;
    ERROR 1100 (HY000): Table 'test_char' was not locked with LOCK TABLES
    可以查看其他表
    mysql> select * from test_char;
    +------------------+
    | s                |
    +------------------+
    | 0120            |
    | 一二十一          |
    +------------------+
    2 rows in set (0.00 sec)
    可讀table_name表
    mysql> select * from table_name;
    +----+------+
    | id | name |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    |  3 | c    |
    |  4 | d    |
    |  5 | e    |
    +----+------+
    5 rows in set (0.00 sec)
    table_name表 ,按exter後會卡住,但沒有出錯誤訊息
    mysql> select * from table_name;

    可以更新或插入 table_name表
    mysql> update table_name set name="dd" where id=2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    不能更新或插入,因為連select都無法了
    釋放鎖後
    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)
    原本卡住的顯示出結果了
    mysql> select * from table_name;
    +----+------+
    | id | name |
    +----+------+
    |  1 | cc   |
    |  2 | dd   |
    |  3 | c    |
    |  4 | d    |
    |  5 | e    |
    +----+------+
    5 rows in set (50.90 sec)
結論:總之就是讀鎖會阻塞寫,不會阻塞讀,而寫鎖則會把讀和寫都阻塞。

可以設置改變讀鎖和寫鎖的優先級:

  • LOW_PRIORITY_UPDATES通過指定啟動參數,使MyISAM引擎默認給予讀請求以優先的權利。
  • SET LOW_PRIORITY_UPDATES=1通過執行命令,發出的更新請求時,不再是最優先。
  • INSERT、UPDATE、DELETE通過指定語句的LOW_PRIORITY屬性,降低該語句的優先級。
給系統參數max_write_lock_count設置一個合適的值,當一個表的讀鎖達到這個值後,MySQL就暫時將寫請求的優先級降低,給讀進程一定獲得鎖的機會。
*進行數據更新時,有使用LOCK TABLES的速度,會比沒有使用來得快!

另一種情形,MYISAM表如果不讓INSERT語句發生衝突可用參考以下設置
當concurrent_insert設置為0時,不允許並發插入。
當concurrent_insert設置為1時,如果MyISAM表中間沒有被刪除的行,MyISAM允許在一個線程讀表的同時,另一個線程從表尾插入記錄。這也是MySQL的默認設置。
當concurrent_insert設置為2時,無論MyISAM表中有沒有空洞,都允許在表尾並發插入記錄。
語法說明
 mysql> show status like 'table%';   

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 198   |
| Table_locks_waited         | 2     |
| Table_open_cache_hits      | 2     |
| Table_open_cache_misses    | 126   |
| Table_open_cache_overflows | 3     |
+----------------------------+-------+
5 rows in set (0.00 sec)



通過檢查table_locks_waited和table_locks_immediate狀態變量來分析系統上的表鎖定爭奪

  Table_locks_immediate:表示可以立即獲取鎖的次數

  Table_locks_waited:表示不能立即獲取鎖,需要等待鎖的次數



  Table_locks_waited/(Table_locks_immediate+Table_locks_waited)

  這個比例值越大說明表級鎖爭用的情況越嚴重。

  例:2/198=0.01比例值=0.01說明100次進程裏就有一次是需要等待鎖的進程



mysql> lock table tableName read;   ———共享讀鎖,表示其他人可以讀但無法寫

mysql> lock table tableName write; ————獨佔寫鎖,表示其他人不可讀也不可寫

mysql> unlock tables;              ————批量解鎖,表示釋放鎖



mysql> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | test  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

1表示加鎖,0表示未加鎖。




相關語法:

  • 查看目前table鎖的指令show open tables;
in_use顯示1就是有表鎖,顯示0就是沒鎖

mysql> show open tables;
+--------------------+------------------------------------------+--------+-------------+
| Database           | Table                                    | In_use | Name_locked |
+--------------------+------------------------------------------+--------+-------------+
| sys                | session_ssl_status                       |      0 |           0 |
| sys                | x$session                                |      0 |           0 |
| sys                | session                                  |      0 |           0 |
| sys                | x$processlist                            |      0 |           0 |
. . 略



沒有留言:

張貼留言

【MYSQL】MYSQL的SYS表說明(版本8.0)

mysql> use sys Reading table information for completion of table and column names You can turn off this feature to get a quicker s...