概念
锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。
在数据库中,除传统的计算资源(如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;