mysql锁机制详解

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。

MySQL有三种锁的级别：页级、表级、行级。

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
BDB存储引擎采用的是页面锁（page-levellocking），但也支持表级锁；
InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM表锁

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table WriteLock）。
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。

对MyISAM表进行操作，会有以下情况：
a 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
b 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

查询表级锁争用情况

show status like 'table_locks%';

+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 2170021 |
| Table_locks_waited    | 435149  |
+-----------------------+---------+
2 rows in set (0.00 sec)

Table_locks_immediate:表示可以立即获取锁的次数
Table_locks_waited:表示不能立即获取锁，需要等待锁的次数
Table_locks_waited/(Table_locks_immediate+Table_locks_waited)
这个比例值越大说明表级锁争用的情况越严重

死锁处理方式 方法一 杀掉进程

show processlist;#查看正在执行的sql
kill id; #杀死sql进程

方法二 重启mysql

sudo service mysql restart

myisam引擎表锁演示

创建myisam存储引擎表

create table lock_myisam(
    id int auto_increment, 
    name varchar(100),
    primary key(id)
)engine=MyISAM default charset=utf8;

插入数据

insert into lock_myisam(name) values('huyongjian');
insert into lock_myisam(name) values('huyongjian2');
insert into lock_myisam(name) values('huyongjian3');

添加读锁

lock table lock_myisam read;

添加写锁

lock table lock_myisam write;

查看数据库锁情况

show open tables from school_info;

+-------------+---------+--------+-------------+
| Database    | Table   | In_use | Name_locked |
+-------------+---------+--------+-------------+
| school_info | school  |      0 |           0 |
| school_info | class   |      0 |           0 |
| school_info | student |      0 |           0 |
+-------------+---------+--------+-------------+
3 rows in set (0.00 sec)

删除表锁

unlock tables;

InnoDB存储引擎的行锁和表锁

行锁包括两种锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

表锁分为三种

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
自增锁(AUTO-INC Locks)：特殊表锁，自增长计数器通过该“锁”来获得子增长计数器最大的计数值。

三种行锁

1 record lock锁：单个行记录的锁（锁数据，不锁Gap）
2 gap lock锁：间隙锁，锁定一个范围，不包括记录本身（不锁数据，仅仅锁数据前面的Gap）
3 next-key lock锁：同时锁住数据，并且锁住数据前面的Gap。所以：next-key lock = record lock + gap lock

行锁的实现

InnoDB通过给索引项上的索引加锁来实现行锁。这也意味着只有通过索引条件检索数据才会被加上行锁，否则InnoDB将使用表锁。
索引不仅包括主键索引，普通索引、唯一索引都可以使用行锁

InnoDB 加行锁

select语句显式加锁：
//共享锁（读锁） select ... lock in share mode
//互斥锁（写锁） select ... for update
更新操作（update）和插入（insert）操作会自动加锁

InnoDB 四种隔离级别

-read uncommitted 应用可以读取到其他应用未提交的数据
-read committed 应用只能读取其他应用已经提交的数据
-repeatable read 应用保证同一事务中，每次读取的数据都是一样的
-serializable 强制序列化读写操作，即不允许读写操作同时进行

Innodb存储引擎行锁演示

查看隔离级别

show variables like '%transaction_isolation%';

设置隔离级别

set session transaction isolation level read uncommitted;

创建innodb存储引擎数据表

create table linelock( 
    id int auto_increment, 
    name char(100), 
    primary key(id) 
)engine=innoDB default charset=utf8;

插入数据

insert into linelock(name) values('huyongjian1'),('huyongjian2'),('huyongjian3');

查看是否自动提交

show variables like '%autocommit%';

关闭自动提交

set autocommit=off;

添加共享锁

会话1 id=1

select * from linelock where id=1 lock in share mode;

会话2 id=1 可读,update等待，id=2 select和update可执行

select * from linelock where id=1;

select * from linelock where id=2;

update linelock set name='session2' where id=1;

update linelock set name='session2' where id=2;

添加排他锁

会话1

select * from linelock where id=1 for update;

会话2 id=1 可读,update等待，id=2 select和update可执行

select * from linelock where id=1;

select * from linelock where id=2;

update linelock set name='session2' where id=1;

update linelock set name='session2' where id=2;

自动加锁

会话1

update linelock set name='session1' where id=1;

会话2 id=1 可读,update等待，id=2 select和update可执行

select * from linelock where id=1;

select * from linelock where id=2;

update linelock set name='session2' where id=1;

update linelock set name='session2' where id=2;

行锁变表锁

show index from linelock;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| linelock |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

会话1 name不是索引列,update会提升表锁

update linelock set name='huyongjian' where name='session1';

会话2 update需要等待

update linelock set name='huyongjian1' where name='session1';

update linelock set name='huyongjian2' where name='session2';