7.mysql 锁

1.锁：

Innodb支持行锁，有时也会升级为表锁；MyISAM只支持表锁。

表锁特点：开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突概率高，并发度相对低。

行锁特点：开销大，辊锁慢；会出现死锁，锁粒度小，发生锁冲突概率低，并发度也相对行锁较高。

 

Innodb锁类型：读锁（共享锁）、写锁（排他锁）、意向锁和MDL锁。

 

读锁：(1)自动提交方式下select语句，不需要加锁，叫一致性非锁定读；

(2)通过select …….lock in share mode在被读取的行记录或行记录范围上加一个锁，让其他事务可以读，但是申请加写锁，会阻塞。

 

写锁：X锁，比较特殊的为select  for update，对读取的行记录上加锁。

 

MDL锁：mysql5.5引入meta data lock,保证表中元数据的信息：

begin:

select * from tt

会话B:

begin:

alter table tt add num tinyint(1) not null default  0;

show full processlist;

 

意向锁：意向共享锁（IS）:

意向排他锁（IX）指在给一个数据行加排他锁前必须先取得该表的IX锁。

 

2.Innodb行锁各类：

默认为RR事务隔离级，并且参数innodb_blocks_unsafe_for_binlog=0的模式下，行锁有三种：

1. 单个行记录的锁（record lock）
2. 间隙锁（GAP lock）
3. 记录锁和间隙锁的组合叫作next-key lock(普通索引默认模式)

Innodb的行锁是加在索引上的，若在没有索引的表中update 两个窗口不同的值，会锁表中整个行。

 

加间隙锁（只针对RR隔离级别）：

begin;

select * from tt where score<80 lock in share mode;

会话B:

Select * from tt;

Beging;

Insert into tt (name,score) values(‘dd’,75); 会锁

锁等待：

通过innodb_lock_wait_timeout参数控制，单位为秒

mysql> show variables like '%innodb_lock_wait%';

例：死锁：

A会话：

begin;

update tt set name=’aaa’ where score=60;  （1次）

update tt set name=’bb’ where score=70; （2次）锁

B会话：

begin:

update tt set name=’a’ where score=70; （1次）  正常

update tt set name=a’ where score=60; 　（2次）  锁

通过show engine innodb status 查看死锁状态的展示信息

避免死锁：

1. 不同程序多表存取并发或涉及多行记录时，尽量以相同顺序访问表；
2. 业务中尽量采用小事务，避免使用大事务，及时提交或回滚事务；
3. 同一个事务中，尽可能做到一次锁定所需要资源，减少死锁；
4. 对非常容易产生死锁的业务部分，尝试使用升级锁粒度，通过表锁定减少死锁。

 

锁监控：

通常情况：show full processlist 和show engine innodb status命令来判断事务中锁问题情况，还有特别重要三张表：information_schema库下innodb_trx、innodb_locks、innodb_lock_waits。

例：会话A:

begin;

select * from tt where score <85 for update ; 加上写锁。

会话B:出现锁等待超时。

begin;

insert into tt (name,score) values (‘dd’,84);

分析：

use information_schema;

select * from innodb_trx\G;

trx_id:唯一事务id号。

trx_state:当前事务的状态

trx_wait_started:事务开始等待的时间

trx_mysql_thread_id:线程id，与show full processlist相对应

trx_query:事务运行的sql语句

trx_operation_state:事务运行的状态