- 支持事務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) | | +--------------------------------------------+--------------------------------------+
·串行讀也稱序列化(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)
可以設置改變讀鎖和寫鎖的優先級：
＊進行數據更新時，有使用LOCK TABLES的速度，會比沒有使用來得快！
另一種情形，MYISAM表如果不讓INSERT語句發生衝突可用參考以下設置
當concurrent_insert設置為0時，不允許並發插入。
當concurrent_insert設置為1時，如果MyISAM表中間沒有被刪除的行，MyISAM允許在一個線程讀表的同時，另一個線程從表尾插入記錄。這也是MySQL的默認設置。
當concurrent_insert設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾並發插入記錄。
語法說明
相關語法：
- LOW_PRIORITY_UPDATES通過指定啟動參數，使MyISAM引擎默認給予讀請求以優先的權利。
- SET LOW_PRIORITY_UPDATES=1通過執行命令，發出的更新請求時，不再是最優先。
- INSERT、UPDATE、DELETE通過指定語句的LOW_PRIORITY屬性，降低該語句的優先級。
＊進行數據更新時，有使用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 |
. . 略
沒有留言:
張貼留言