MySQL高级4——MySQL锁机制

概述

定义

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

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

锁的分类

按对数据的操作类型分：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

按对数据操作的粒度分：

• 表锁
• 行锁

三锁

表锁（偏读）

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

案例分析：

建表SQL：

create table mylock(
id int not null primary key auto_increment,
name varchar(20)) 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');

select * from mylock;

手动增加表锁
lock table 表名1 read[write], 表名2 read [write];

查看表上加过的锁
show open tables;

释放表锁
unlock tables;

举例说明
加读锁

加写锁

案例结论：

简而言之，就是读锁会阻塞写，但不会阻塞读，而写锁会把读和写都阻塞。

表锁分析

行锁（偏写）

特点：

• 偏向InnoDB存储引擎，开销大， 加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION)，二是采用了行级锁。

事务复习：

案例分析

建表SQL:

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,'b1');

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

行锁基本演示

无索引行锁升级为表锁

这里session1中的b=4000没有加单引号，导致mysql进行自动类型转换，导致索引失效，使得行锁变表锁。

间隙锁危害

面试题：面试如何锁定一行

这里讲的不好！！！

案例总结

行锁分析

页锁