參數查詢

#死鎖檢測 
show variables like '%innodb_deadlock_detect%';
#鎖超時時間
show variables like '%innodb_lock_wait_timeout%';
#立即獲得表鎖請求的次數
SHOW STATUS LIKE 'Table_locks_immediate%';
#必須等待表鎖請求的次數
SHOW STATUS LIKE 'Table_locks_waited%';

表鎖

  1. mysql服務器支持表鎖(MyISAM、MEMORY等)
  2. MySQL對除InnoDB以外的所有存儲引擎使用表鎖定（而不是頁面鎖定，行鎖定或列鎖定）
  3. 特點：鎖定整個表；所需內存相對較少；比較適合只讀；速度較快
  4. 語句：
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES
  1. 如果鎖定表時使用了別名，則鎖定後的語句中也要使用表的別名
讀鎖
  1. 持有鎖的會話可以讀取表但不能寫入表
  2. 多個會話可以同時獲得讀鎖
  3. 其他會話可以在不顯示獲得讀鎖的情況下讀取表
  4. 會話只能訪問已獲得鎖的表，其他表不能訪問
  5. 沒有會話可以修改被讀鎖鎖定的表（包括持有讀鎖的會話）
  6. READ LOCAL和READ之間的區別是READ LOCAL允許在保持鎖的同時執行無衝突的INSERT語句（併發插入）
寫鎖
  1. 持有鎖的會話可以讀取和寫入表
  2. 只有持有鎖的會話可以訪問表，在鎖釋放之前其他會話無法訪問表
  3. 會話持有鎖時其他會話請求獲得鎖將會阻塞（等待當前會話釋放鎖後才能獲得鎖）
釋放鎖
  1. 顯示釋放UNLOCK TABLES
  2. 隱式釋放，會話持有鎖時使用LOCK TABLES獲得新的鎖時會釋放之前獲得的鎖
  3. 隱式釋放，會話開始事務之前，比如START TRANSACTION語句
# InnoDB中對事務表使用鎖進行操作
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
# 使用鎖避免其他會話在select和update之間修改表
LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
  SET total_value=sum_from_previous_statement
  WHERE customer_id=some_id;
UNLOCK TABLES;
併發插入
# 0 never ,1 auto,2 always
show variables like '%concurrent_insert%';

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no holes in the data file (deleted rows in the middle), an INSERT statement can be executed to add rows to the end of the table at the same time that SELECT statements are reading rows from the table. If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements. The results of a concurrent INSERT may not be visible immediately.

行鎖

  1. InnoDB 支持行鎖
  2. 特點：鎖定某些行（索引）；訪問不同行時鎖衝突減少；回滾更改較少；可以長時間鎖定某個單行
  3. 語句：SELECT … FOR UPDATE；SELECT …LOCK IN SHARE MODE;