mysql锁的基本知识

参数查询

#死锁检测 
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

4. 如果锁定表时使用了别名，则锁定后的语句中也要使用表的别名

读锁

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;