MYSQL并发控制

post thumb
Mysql
作者 Louis 发表于 2018年6月11日

摘要:MYsql的并发控制,锁,事务了解,涵盖了事务安全,事务原理,事务回滚点,事务特性

并发控制

LOCK TABLES
	tbl_name [[AS] alias] lock_type
	[, tbl_name [[AS] alias] lock_type] ...
lock_type: READ  WRITE
UNLOCK TABLES  -- 解锁
FLUSH TABLES tb_name[,...] [WITH READ LOCK]
-- 关闭正在打开的表(清除查询缓存),通常在备份前加全局读锁
MariaDB [hellodb]> SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
-- 查询时加写或读锁
  • 锁:

    • 读锁:共享锁,只读不可写,多个读互不阻塞,

      MariaDB [hellodb]> LOCK TABLES students READ;

    • 写锁:独占锁,排它锁,一个写锁会阻塞其它读和它锁

      MariaDB [hellodb]> LOCK TABLES students WRITE;

  • 实现

    • 存储引擎:自行实现其锁策略和锁粒度
    • 服务器级:实现了锁,表级锁;用户可显式请求
  • 分类:

    • 隐式锁:由存储引擎自动施加锁
    • 显式锁:用户手动请求
  • 锁机制: innodb默认是行锁,但是如果在事务操做过程中,没有使用到索引,系统会自动进行全表检索苏剧,自动升级为表所,后续事务会讲到。

    • 行锁:只有当前行被锁住,别的用户不能操作当前行
    • 表所:整张表被锁住,别的用户都不能操作
    • 页锁:显示的整页被锁住

read 锁

自己可以读,但是不可以写;其他人可以读,不可以写。

lock tables students read;
update students set classid=3 where stuid=25; -- 均不可写,只可读

write 锁

自己可以读,但是不可以写;其他人不可以读

flush tables students; 		-- 清除缓存
lock tables students write;
select * from students where stuid=12; -- 其他终端不可读此表,自己可读

死锁:

两个或多个事务在同一资源相互占用,并请求锁定对方占用的资源的状态.相当于行级锁。

事务

事务了解

  • 事务Transactions:一组原子性的SQL语句,或一个独立工作单元
  • 事务日志:记录事务信息,实现undo,redo等故障恢复功能
-- 创建一个账户表
use testdb;
create table my_account(
number char(16) not null unique ,
name varchar(20) not null,
money decimal(10,2) default 0.0
)charset utf8;
-- 插入数据
insert into my_account values
('0000000000001','张三',100000),
('0000000000002','李四',200000);
-- 开启事务
start transaction;
-- 更新数据库
alter table my_account add id int primary key auto_increment first;
insert into my_account values ('3','0000000000003','王五',300000);
update my_account set money = money - 100000 where id = 1 ;  -- 另开一个账户查看是否修改了表
update my_account set money = money + 100000 where id = 2 ;  -- 另开一个账户查看是否修改了表
-- 提交事务
commit

事务操作

事务操作分为两种:自动事务(默认的),手动事务

手动事务:

  • 开启事务:告诉系统所有的操作(写)不要直接写到数据库的表中,先放入事务日志中;start transaction;

  • 事务操作:1. 张三账户减少

    MariaDB[testdb]> update my_account set money = money - 100000 where id = 1 ;

  • 事务操作:2. 李四账户增加

    MariaDB[testdb]> update my_account set money = money + 100000 where id = 2 ;

  • 事务关闭:选择性将日志文件中的操作的结果报错到数据表中,清空事务日志

    1. 提交事务:同步数据表(操作成功)commit;

    2. 回滚事务:直接清空日志表(操作失败)rollback;

  • 注意:只有事务型存储引擎方能支持此类操作,如innodb

事务原理

生命周期

回滚点

回滚点:在某个成功的操作完成之后,后续的操作有可能成功也可能失败,但是无论结果如何,可以在当前的成功位置上,设置一个点。可以供后续的失败操作返回到该位置,而不是返回所有操作,这个点称为回滚点。

设置回滚点:savepoint 回滚点名字;一个回滚点,只能回滚一次

release savepoint identified;

-- 回滚点操作
-- 开启事务
start transaction;
select * from my_account;
-- 事务处理1:张三加钱
update my_account set money = money + 10000 where id = 1;

-- 设置回滚点
savepoint sp1;

-- 银行扣税
update my_account set money = money - 10000 * 0.05 where id = 2 ; -- 扣错了
select * from my_account;
-- 回滚
rollback to sp1;

-- 继续操作
update my_account set money = money - 10000 * 0.05 where id = 1 ; 
select * from my_account;
-- 提交
commit

自动事务处理

在mysql中:默认的都是自动事务处理,用户操作完立即同步到数据表中。

-- 系统默认自动事务
show variabes like 'autocommit';
set autocommit=0
-- 李四加工资
start transaction;
update my_account set money = money + 10000 where id = 2;
-- 手动提交
commit
-- 一般使用自动事务
set autocommit=1
-- 扣税
update my_account set money = money - 10000*0.05 where id = 2;
  • 注意:通常都会使用自动事务. 减少重复的工作

事务的特性

  • ACID特性:
  • A:atomicity原子性;整个事务中的所有操作要么全部成功执行,要么全部失败后回滚
  • C:consistency一致性;数据库总是从一个一致性状态转换为另一个一致性状态
  • I:Isolation隔离性;一个事务所做出的操作在提交之前,是不能为其它事务所见,存于数据的日志中,彼此之间不受影响;隔离有多种隔离级别,实现并发
  • D:durability持久性;一旦事务提交,其所做的修改会永久保存于数据库中

事务隔离级别:

从上至下更加严格

  • READ-UNCOMMITTED 可读取到未提交数据,产生脏读
  • READ-COMMITTED 可读取到提交数据,但未提交数据不可读,产生不可重复读,即可读取到多个提交数据,导致每次读取数据不一致
  • REPEATABLE-READ 可重复读,多次读取数据都一致,产生幻读,即读取过程中,即使有其它提交的事务修改数据,仍只能读取到未修改前的旧数据。此为MySQL默认设置
  • SERIALIZABILE 可串行化,未提交的读事务阻塞修改事务,或者未提交的修改事务阻塞读事务。导致并发性能差

MVCC: 多版本并发控制,和事务级别相关

事务的隔离性:READ-UNCOMMITTED

在两个会话中

set tx_isolation='READ-UNCOMMITTED';
start transaction;
update my_account set money = money + 10000 where name = '王五';
select * from my_account; 	-- 未提交,产生脏读

事务的隔离性:READ-COMMITTED

在两个会话中

set tx_isolation='READ-COMMITTED';
start transaction;
update my_account set money = money + 10000 where name = '李四';
select * from my_account; 	-- 未提交,另外一边不能读到未提交的数据
commit;	-- 提交之后,可以读取新数据

事务的隔离性:REPEATABLE-READ

set tx_isolation='REPEATABLE-READ';
start transaction;
update my_account set money = money + 10000 where name = '张三';
select * from my_account; 	
commit		
select * from my_account; 	--不管是否提交,都看不到新的数据

事务的隔离性:SERIALIZABILE

不用索引,系统会进行全表索引,会导致事务升级为表级锁,并发性能差

set tz_isolation='SERIALIZABILE';
start transaction;
update my_account set money = money + 10000 where name = '张三';
select * from my_account ; -- 数据未更新
update my_account set money = money + 10000 where id = 2;
-- 显示等待,事务升级为表级锁

总结:支持事务的引擎有innodb,而myisam是不支持事务的,一般涉及到钱的,基本会用到事务。

start transaction;
update students set gender='M'; -- 未提交,导致全表锁,造成大量用户无法访问
show processlist;
+----+--------+-----------+---------+---------+------+----------+------------------+----------+
| Id | User   | Host      | db      | Command | Time | State    | Info             | Progress |
+----+--------+-----------+---------+---------+------+----------+------------------+----------+
| 10 | root   | localhost | hellodb | Query   |    0 | init     | show processlist |    0.000 |
| 11 | root   | localhost | hellodb | Sleep   | 4962 |          | NULL             |    0.000 |
+----+--------+-----------+---------+---------+------+----------+------------------+----------+
kill id -- 杀掉进程
上一篇
MYSQL数据引擎