MySQL的锁机制

概念

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供多用户共享的资源。

从对数据操作的粒度分
表锁：操作时，会锁定整个表
行锁：操作时，会锁定当前操作行

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

不同存储引擎支持不同的锁机制

存储引擎	表级锁	行级锁
MyISAM	支持	不支持
InnoDB	支持	支持
MEMORY	支持	不支持
BDB	支持	不支持

MySQL锁特性

锁类型	特点
表级锁	偏向MyISAM存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行级锁	偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高

MyISAM表锁

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用lock table命令给MyISAM表显示加锁。

方法：
加读锁
LOCK TABLE table_name READ;
加写锁
LOCK TABLE table_name WRITE;

例如（这里的代码建议在B站上看一下相关视频学习）：

DROP DATABASE IF EXISTS mydb14_lock;
CREATE DATABASE mydb14_lock;

USE mydb14_lock;

CREATE TABLE tb_book(
 id INT(11) auto_increment,
 name VARCHAR(50) DEFAULT NULL,
 publish_time DATE DEFAULT NULL,
 status CHAR(1) DEFAULT NULL,
 PRIMARY KEY(id)
);

INSERT INTO `mydb14_lock`.`tb_book`(`id`, `name`, `publish_time`, `status`) VALUES (1, 'java编程思想', '2088-08-01', '1');
INSERT INTO `mydb14_lock`.`tb_book`(`id`, `name`, `publish_time`, `status`) VALUES (2, 'solr编程思想', '2088-08-08', '0');

CREATE  TABLE tb_user(
id INT(11) auto_increment,
name VARCHAR(50) DEFAULT NULL,
PRIMARY KEY(id)
);

INSERT INTO `mydb14_lock`.`tb_user`(`id`, `name`) VALUES (1, '令狐冲');
INSERT INTO `mydb14_lock`.`tb_user`(`id`, `name`) VALUES (2, '田伯光');

-- 读锁
LOCK TABLE tb_book READ;

SELECT * FROM tb_book;
UPDATE tb_book SET status = '1' ;-- 此时不能修改会报错

UNLOCK TABLES;

-- 写锁 同时只能加一个
LOCK TABLE tb_book WRITE;

SELECT * FROM tb_book;
UPDATE tb_book SET status = '1'; -- 此时能修改不会报错

InnoDB行锁

行锁特点

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

行锁模式

InnoDB实现了两种类型的行锁

• 共享锁（S）：又称为读锁 ，简称S锁，共享锁就是多个事务对于统一数据可以共享一把锁，都能访问到数据，但是只能读不能修改
• 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事物就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X）
对于普通SELECT语句，InnoDB不会加任何锁

方法：
共享锁（S）
SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X）
SELECT * FROM table_name WHERE … FOR UPDATE
例如（（这里的代码建议在B站上看一下相关视频学习））：

DROP TABLE IF EXISTS test_innodb_lock;
CREATE TABLE test_innodb_lock(
id INT(11),
name VARCHAR(16),
sex VARCHAR(1)
);

INSERT INTO `mydb14_lock`.`test_innodb_lock`(`id`, `name`, `sex`) VALUES (1, '100', '1');
INSERT INTO `mydb14_lock`.`test_innodb_lock`(`id`, `name`, `sex`) VALUES (3, '3', '1');
INSERT INTO `mydb14_lock`.`test_innodb_lock`(`id`, `name`, `sex`) VALUES (4, '400', '0');
INSERT INTO `mydb14_lock`.`test_innodb_lock`(`id`, `name`, `sex`) VALUES (5, '500', '1');
INSERT INTO `mydb14_lock`.`test_innodb_lock`(`id`, `name`, `sex`) VALUES (6, '600', '0');
INSERT INTO `mydb14_lock`.`test_innodb_lock`(`id`, `name`, `sex`) VALUES (7, '700', '0');
INSERT INTO `mydb14_lock`.`test_innodb_lock`(`id`, `name`, `sex`) VALUES (8, '800', '1');
INSERT INTO `mydb14_lock`.`test_innodb_lock`(`id`, `name`, `sex`) VALUES (9, '900', '1');
INSERT INTO `mydb14_lock`.`test_innodb_lock`(`id`, `name`, `sex`) VALUES (1, '200', '0');


-- 关闭事务的自动提交
SET autocommit = 0;

-- 开启事务
BEGIN;

SELECT * FROM test_innodb_lock;-- select不会加任何锁

UPDATE test_innodb_lock SET sex = '2' WHERE id=1;