Mysql锁机制

概述

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

在数据库中，除传统的计算资源（CPU,RAM,I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言现得尤其重要，也更加复杂。

锁的分类

• 数据操作的类型分----读锁（共享锁）、写锁（排他锁）
• 数据操作的粒度分----表锁、行锁

表锁
偏向于MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
#建表

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

手动增加表锁

lock table 表名 read(write),表名2 read(write),其它；

查看表上加过的锁（0–没上锁，1–上锁）

show open tables;

释放锁

unlock tables;

加入read锁后

两个进程都可以查询，但当前进程不可以修改，其他进程修改会阻塞等待，当前进程不可以访问其他表

加入write锁后

当前进程

其他进程

案例结论
MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：

• 表共享读锁
• 表独占写锁
  
  结论：

1.对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放时，才会执行其他线程的写操作
2.对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放时，才会执行其他线程的读写操作
读锁会阻塞写，但不会阻塞读，写锁会把读和写都阻塞

如何分析表锁定

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定；
show status like 'table%';

table_locks_immediate：表示可以立即获取锁的查询次数，每立即获取锁值加1
table_locks_waited：不能立即获取锁的次数，每等待一次锁值加1，值高说明存在严重的表级锁争用情况

行锁
偏向于InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高；

InnoDB于MyISAM的最大不同有两点：

1. 支持事务（TRANSACTION)
2. 采用了行级锁

#建表

CREATE TABLE test_innodb_lock (
a INT(11),
b VARCHAR(16)
)ENGINE=INNODB;

insert into test_innodb_lock values(1, b2);
insert into test_innodb_lock values(3, 3);
insert into test_innodb_lock values(4, 4000);
insert into test_innodb_lock values(5, 5000);
insert into test_innodb_lock values(6, 6000);
insert into test_innodb_lock values(7, 7000);
insert into test_innodb_lock values(8, 8000);
insert into test_innodb_lock values(9, 9000);
insert into test_innodb_lock values(1, b1000);

create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_b_ind on test_innodb_lock(b);

关闭自动提交

set autocommit=0;

当前线程修改值

两个线程都修改值（其他线程阻塞）

两个线程修改不同值

索引失效行锁变表锁


发生了阻塞，说明类型转换导致了索引失效，行锁变表锁；

间隙锁危害


当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，Innodb会给符合条件的已有数据记录的索引加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙”（GAP），InnoDB也会对这个“间隙”加锁，就是所谓的间隙锁。

【危害】
因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定情况下无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

如何锁定一行

for update

分析行锁定

通过检查innodb_row_lock状态变量来分析系统上的行锁的争夺情况
show status like ‘innodb_row_lock%’；

innodb_row_lock_current_waits：当前正在等待锁定的数量
innodb_row_lock_time：从系统启动到现在锁定总时间长度
innodb_row_lock_time_avg:每次等待所花平均时间
innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花时间
innodb_row_lock_waits:从系统启动后总共等待的次数

优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理涉及索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离