1 Mysql锁问题
1.1 锁概述
锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。
在数据库中,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
1.2 锁分类
从对数据操作的粒度分 :
1) 表锁:操作时,会锁定整个表。MyISAM 5.6 后InnoDB支持
2) 行锁:操作时,会锁定当前操作行。
从对数据操作的类型分:
1) 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。
2) 写锁(排它锁):当前操作没有完成之前,它会阻断其他写操作和读操作。
1.3 Mysql 锁
相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况:
MySQL这2种锁的特性可大致归纳如下 :
从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web 应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
2 MyISAM 表锁
MyISAM 存储引擎只支持表锁,这也是MySQL开始几个版本中唯一支持的锁类型。
2.1 如何加表锁
MyISAM 在执行查询语句(SELECT)前,会==自动给涉及的所有表加读锁==,在执行更新操作(UPDATE、DELETE、INSERT 等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。 但是我们为了给你能演示出效果人为加锁。
显示加表锁语法:
加读锁 : lock table table_name read;
加写锁 : lock table table_name write;
2.2 读锁案例
准备环境
create database demo_03 default charset=utf8mb4;
use demo_03;
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`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');
CREATE TABLE `tb_user` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');
客户端 一 :
1)获得tb_book 表的读锁
lock table tb_book read;
2) 执行查询操作
select * from tb_book;
可以正常执行 , 查询出数据。
客户端 二 :
3) 执行查询操作
select * from tb_book;
客户端 一 :
4)查询未锁定的表
select name from tb_user;
因为当前用户已经对别的表上锁了 只有解锁后才可以对其他的表进行读操作。
unlock tables;
客户端 二 :
5)查询未锁定的表
select name from tb_user;
可以正常查询出未锁定的表;
客户端 一 :
6) 执行插入操作
insert into tb_book values(null,'Mysql高级','2088-01-01','1');
执行插入, 直接报错 , 由于当前tb_book 获得的是 读锁, 不能执行更新操作。
客户端 二 :
7) 执行插入操作
insert into tb_book values(null,'Mysql高级','2088-01-01','1');
当在客户端一中释放锁指令 unlock tables 后 , 客户端二中的 inesrt 语句 , 立即执行 ;
2.3 写锁案例
写锁(排他锁): 不允许其他会话进行任意操作
客户端 一 :
1)获得tb_book 表的写锁
lock table tb_book write ;
2)执行查询操作
查询操作执行成功;
3)执行更新操作
update tb_book set name = 'java编程思想(第二版)' where id = 1;
更新操作执行成功 ;
客户端 二 :
4)执行查询操作
当在客户端一中释放锁指令 unlock tables 后 , 客户端二中的 select 语句 , 立即执行 ;
总结
3 InnoDB 行锁 支持事务
3.1 行锁介绍
行锁特点 :偏向InnoDB 存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
InnoDB 与 MyISAM 的最大不同有两点:一是支持事务;二是 采用了行级锁。
3.2 背景知识
什么事务?
事务是应用程序中一系列严密的操作,所有操作必须成功完成,否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性,一个事务中的一系列的操作要么全部成功,要么一个都不做。
事务及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元。
事务具有以下4个特性,简称为事务ACID属性
并发事务处理带来的问题
事务隔离级别
为了解决上述提到的事务并发问题,数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行,这显然与“并发” 是矛盾的。
数据库的隔离级别有4个,由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable,这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。
备注 : √ 代表可能出现 , × 代表不会出现 。
Mysql 的数据库的默认隔离级别为 Repeatable read , 查看方式:
3.3 InnoDB 的行锁模式
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
3.4 案例准备工作 innodb 支持事务 行级锁
create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);
3.5 行锁基本演示 ----支持事务
以上, 操作的都是同一行的数据,接下来,演示不同行的数据 :