Mysql数据库优化—6（锁机制）

1、分类

按照操作类型分：

读锁（共享锁）：对同一数据，多个读操作可以同时进行，互不影响。

写锁（互斥锁）：如果当前写操作没有完成，则其他不能进行读操作、写操作

 

按操作范围分：

表锁：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突，并发度低。

行锁：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大、加锁慢；容易出现死锁；锁的范围小，不易发生锁冲突，并发度高。

页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

 

2、测试表锁

使用MyISAM存储引擎进行测试。首先创建表

create table tablelock
(
    id int primary key auto_increment , 
    name varchar(15)
)engine myisam;


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

现在开启两个会话，来测试读锁

会话1中对表进行加锁：

lock table tablelock read;

在会话1中，可以对该表进行读操作。

但在会话1中，不能对该表进行写操作。

再创建一张表，测试一下会话1可不可以操作该表（该表没有添加表锁）

create table emp
(
    eid int(5) primary key,
    ename varchar(20) not null default '',
    job varchar(20) not null default '',
    deptno int(5) not null default 0
)engine=innodb default charset=utf8;

发现在会话1中，无法对其他表进行读写操作。

小结：在该会话内对该表加读锁，该会话可以读该表的数据，但无法写该表数据。同时该会话无法读写其他表。（即，只可以读自己的。其他操作都不行）

在会话2中：

从被锁的表中读取数据:：发现可以读数据

select * from tablelock;

往被锁的表中写数据：发现写数据被阻塞（在等待会话1释放锁）

delete from tablelock where id=1; 

当然在会话2中，可以对其他表进行读写操作。

小结：会话1给该表加了读锁。会话2中可以读取该表的数据，但是写数据时会被阻塞。会话2可以读取其他表的数据（即，其他会话写数据会被阻塞，其他都可以）

最后在会话1中对表进行解锁：unlock tables ;

 

现在开启两个会话，来测试写锁

lock table tablelock write;

结论：会话1中对该表进行加写锁，会话1可以对该表进行增删改查。但会话1不能对其他表进行操作。

会话2中不能操作该表（需要等待会话1释放写锁）。

 

3、行锁

首先创建表

create table linelock(
    id int(5) primary key auto_increment,
    name varchar(15)
)engine=innodb ;

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

研究行锁的时候需要关闭自动提交

set autocommit=0

在会话1中，写入一条数据，但是不提交

insert into linelock values(6,"f");

 

在会话2中，对会话1的数据进行更新

会话2的操作被阻塞。当1提交后，2才执行（可能会超时返回）。

行锁由于锁的是一行数据，当两个会话操作的是不通过的记录时，二者互不影响。

表锁：通过unlock tables解锁，也可以通过事务解锁

行锁：通过事务解锁

注意：①、如果没有索引，行锁会转变为表锁

   ②、行锁的一种特殊情况：间隙锁：值在范围内，但却不存在

对于第一种情况：

重新创建一张表，为表的name添加索引

alter table linelock add index idx_name(name);

 在会话1中，修改name=3的数据。由于name是varchar类型，这里给的整数类型，会发生隐式类型转换导致索引失效。故此处name索引失效。

 在会话2中发现，操作name=4的记录被阻塞，说明索引失效导致表被锁了。

针对于第二种情况

比如有如下sql语句(前提：数据库中没有id=7的记录)

update linelock set name="x" where id>1 and id<9;

此时sql会自动加间隙锁，在未提交的情况下，另一个会话不能插入id=7的记录。即加锁的会话将id=2~8之间的都锁了。

 

通过for update可以对query语句进行加锁

当会话1对query语句加锁后，会话2就不能去修改该记录会被阻塞。