mysql锁表查询_Mysql锁机制(1)

在MySQL中不同的存储引擎支持不同的锁。按照锁的粒度来划分，可以分为表锁、行锁和页锁。

MyISAM只支持表锁，InnoDB支持表锁和行锁，而BDB支持表锁和页锁

表锁：顾名思义，锁的是整个表

行锁：锁的是一条记录

页锁：mysql中数据是按页存储的，一页存满，就存储在下一页。而页锁就是锁定整个页

MyISAM的表锁

MyISAM存储引擎只支持表锁。

在执行查询操作（select）前， 会自动给涉及的所有表加读锁；

在 执行更新操作（insert、update、delete等）前， 会自动给涉及的所有表加写锁。

说明：

1）MyISAM表的读操作，不会阻塞其他用户对相同表的读操作，但会阻塞对相同表的写操作；

2）MyISAM表的写操作，会阻塞其他用户对相同表的读操作、写操作；

3）MyISAM表的读、写操作之间，以及写操作之间是串行的。

示例演示：

表创建和数据插入:

create 

模拟读锁

1. 窗口1通过读锁锁表

2. 窗口2搜索该表数据

3. 窗口2修改id为1的数据(阻塞中)

4. 窗口1解锁改表

5. 窗口2自动解阻塞，执行步骤3中的命令

模拟写锁

1.窗口1通过写锁锁表

2. 窗口2查询表数据(阻塞)

3. 窗口1解锁表

4. 窗口2自动执行阻塞的命令

InnoDB的行锁

InnoDB存储引擎既支持行锁，也支持表锁，但默认情况下是采用行锁。InnoDB的行锁是通过锁定索引项来实现的，而不是锁定物理行记录。InnoDB的锁，与索引、事务的隔离级别有关。InnoDB 的锁类型有很多种

1. 共享锁：当一个事务对某行记录上了共享锁，允许其他事务对 该记录进行读操作，但不允许写操作。

#共享锁加锁方式 
SELECT ... LOCK IN SHARE MODE

示例演示：

创建数据：

 create table bank_innodb(
	id int not null auto_increment primary key,
	name varchar(30) comment '姓名',
	age int comment '年龄',
	balance decimal(18, 2) comment '余额'
)engine=InnoDB default charset=utf8;
insert into bank_innodb(name, age, balance) values('zhangsan', 22, 1000);
insert into bank_innodb(name, age, balance) values('lisi', 18, 2000);
insert into bank_innodb(name, age, balance) values('wangwu', 20, 3000);
insert into bank_innodb(name, age, balance) values('zhaoliu', 21, 4000);
insert into bank_innodb(name, age, balance) values('tom', 19, 5000);
insert into bank_innodb(name, age, balance) values('jack', 24, 6000);
insert into bank_innodb(name, age, balance) values('lilei', 22, 7000);
insert into bank_innodb(name, age, balance) values('lily', 18, 8000);

1. 事务A创建共享锁

2. 事务B创建共享锁(执行成功)

3. 事务B执行修改操作(阻塞)

4. 事务A结束事务

5. 事务B自动执行阻塞的任务

2.排他锁：当一个事务对某行记录上了排他锁，其他事务即 不能对该行记录进行读操作，也不能进行写操作。InnoDB会自动对增删改操作加排他锁。

#手动给查询语句加排他锁
SELECT ... FOR UPDATE

示例演示：

1. 事务A加排他锁

2. 事务B加共享锁(阻塞)

3. 事务A提交

4. 事务B自动执行阻塞的任务

自增锁：是当向含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特 殊的表级锁。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务必须等待对该 表执行自己的插入操作，以便第一个事务插入的行的值是连续的。

InnoDB还提供了参数 innodb_autoinc_lock_mode 用于设置自增锁模式，它可以对插入操作实现 性能与并发的平衡。

取值说明：

0：traditional，传统锁模式，语句级锁，保证值分配的可预见性、连续性、可重复性，保证主从复制的一致性。

1： consecutive，连续锁模式，锁在语句得到值后就释放，并发插入性能优于传统模式，MySQL 5.x默认模式。

2：interleaved，交错锁模式，最快最具扩展性的模式，基于binlog的复制与恢复不安全，MySQL 8.0默认模式。

为什么主键通常使用自增？

主要原因有两个：

1）索引本身是有序的，再加上MySQL底层是以页为单位来存储数据的，一页写满继续写下一页。如果使用非自 增id，为了保证索引的有序性，每次插入都需要将数据移动到合适的位置，可能会造成页分裂，影响插入性能。

2）相对而言，字符串作为主键占用的空间大，而且字符串的比较更慢。

分享几本学习mysql的书籍：