浅谈MySQL事务

一、Mysql锁机制

1.表锁

表级锁是mysql粒度最大的锁,对整张表加锁,资源开销比行锁少,不会出现死锁,发生锁冲突的概率大。系统负面影响最小,获取锁和释放锁的速度快。
MyISAM只支持表锁,因此性能相对Innodb来说相对降低，Innodb默认是行锁但也支持表锁。

2.行锁

行锁的是mysql锁中粒度最小的一种锁,因为锁的粒度很小,所以资源争抢的概率也最小,并发性能最大,但是会造成死锁,每次加锁和释放锁的开销也大。目前主要是Innodb用行锁,行锁按照使用方式也分为共享锁(S锁、读锁)和排它锁(X锁、写锁)。

3.共享锁(S锁,读锁)

若事务A对数据甲加上S锁,则事务A可以读数据甲但不能改,其他事务只能再对数据甲加S锁,而不能加X锁,直到事务A释放数据甲上的S锁。保证了其他事务可以读数据甲,但在事务A释放数据甲上的S锁之前不能对数据甲做修改。
用法: select ... lock in share mode;

4.排它锁(X锁,写锁)

若事务A对数据甲加上X锁,事务A可以读数据甲也可以修改数据甲,其他事务不能再对数据甲加任何锁,直到事务A释放数据甲上的锁。保证了其他事务在事务A释放数据甲上的锁之前不能再读取和修改数据甲。
用法: select ... for update;

5.意向共享锁(IS)和意向排它锁(IX)

意向共享锁(IS): 事务想要在获得表中某些记录的共享锁,需要在表上先加意向共享锁。
意向互斥锁(IX): 事务想要在获得表中某些记录的互斥锁,需要在表上先加意向互斥锁。
意向共享锁和意向排它锁总称为意向锁。意向锁的出现是为了支持Innodb支持多粒度锁。
意向锁设置成表级别的锁的性能比行锁高的多。
有了意向锁之后,事务A在申请行锁(写锁)之前,数据库会自动先给事务A申请表的意向排他锁。
当事务B去申请表的写锁时就会失败,因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。
意向锁的作用:
当一个事务在需要获取资源的锁定时,如果该资源已经被排他锁占用,则数据库会自动给该事务申请一个该表的意向锁。
如果自己需要一个共享锁定,就申请一个意向共享锁。如果需要的是某行(或者某些行)的排他锁定,则申请一个意向排他锁。

6.乐观锁

乐观锁不是数据库自带的,需要自己实现。乐观锁是指操作数据库时(更新操作)想法很乐观。在表中的数据进行操作时(更新),先给数据表加一个版本(version)字段,每操作一次,将那条记录的版本号加1。
也就是先查询出那条记录,获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等,
相等则执行更新，将version字段的值加1;不相等,则说明已有其他程序对其进行操作了,不进行更新操作。
例:
 

    select data AS old_data, version AS old_version FROM …;
    根据获取的数据进行业务操作，得到new_data和new_version
    update SET data = new_data, version = new_version WHERE version = old_version
    if (updated row > 0) {
    // 乐观锁获取成功，操作完成
    } else {
    // 乐观锁获取失败，回滚并重试
    }

7.悲观锁

首先用set autocommit=0关闭mysql的autoCommit属性。查询出数据之后要将该数据锁定。关闭自动提交后,需手动开启事务。

例:

//1.开始事务
	begin; 或者 start transaction;
	//2.查询出商品信息，然后通过for update锁定数据防止其他事务修改
	select status from t_goods where id=1 for update;
	//3.根据商品信息生成订单
	insert into t_orders (id,goods_id) values (null,1);
	//4.修改商品status为2
	update t_goods set status=2;
	//4.提交事务
	commit; --执行完毕，提交事务

8.间隙锁(Next-Key锁)

作用于非唯一索引上,为了防止其他事务在间隔中插入数据,以导致"不可重复读"。如果把事务的隔离级别降级为读提交(Read Committed,RC),间隙锁则会自动失效。

例: 

SELECT * FROM table WHERE id = 8 for update;
	----此时,(7,11)就会被锁定
	SELECT * FROM table WHERE id BETWEN 2 AND 5 for update;
	----此时,(1,4)和(4,7)就会被锁定

9.记录锁

封锁索引记录,作用于唯一索引上。
select * from t where id=5 for update;
它会在id=5的索引记录上加锁，以阻止其他事务插入、更新、删除id=5的这一行。
说明:select * from t where id=4;是快照读(SnapShot Read),不加锁,不影响其他事务操作该数据。

10.临键锁

作用于非唯一索引上,记录锁与间隙锁的组合。它的封锁范围,既包含索引记录,又包含索引之前的区间,即（负无穷大，1]，（2,4]，（5,7]，（8,11]，（12，无穷大]。
在事务A中执行:  update table SET name = 'javaHuang' WHERE age = 4;  SELECT * FROM table WHERE age = 4 FOR update。
这两个语句都会锁定（2,4),(4,7)这两个区间。InnoDB 会获取该记录行的临键锁,并同时获取该记录行下一个区间的间隙锁。
临键锁的出现是为了innodb在RR隔离级别下,解决幻读问题。如果把事务的隔离级别降级为RC,临键锁会失效。

11.死锁

死锁是指两个或两个以上事务在执行过程中因争抢锁资源而造成的互相死等的现象。
解决:
1.等待事务超时,主动回滚。
2.进行死锁检查,主动回滚某条事务,让别的事务能继续走下去。
查看正在被锁的事务: SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 杀事务: kill trx_mysql_thread_id。

二、Mysql事务

Mysql的事务管理默认关闭(自动事务,数据改动会立即入库)。
在Innodb中,每开启一个事务,都会为该session分配一个事务对象。ReadView(视图)来判断事务的可见性(ACID中的隔离性),视图中包含事务ID、事务ID数组。

查询当前有多少事务正在运行。
select * from information_schema.innodb_trx;
查看自动事务的开启情况
show variables like 'autocommit';

1、Mysql事务四特性(ACID)

1. 原子性:  事务是最小单元,不可再分隔的一个整体。
2. 一致性:  事务中的方法要么同时成功,要么都不成功,要不都失败。
3. 隔离性:  多个事务操作数据库中同一个记录或多个记录时,对事务进行隔离开来有序执行,避免线程不安全。需要使用锁来解决。
4. 持久性:  当成功操作数据库时,操作的结果永久的写入到数据库磁盘中。

2、Mysql事务四隔离级别

1. 读未提交(read uncommitted)。(写加锁，读不加锁)
   写操作加写锁，读操作不加锁。禁止第一类丢失更新，但是会出现所有其他数据并发问题。
2. 读已提交(read committed)。(写、读加锁)
   写操作加写锁，读操作加读锁。禁止第一类丢失更新和脏读。会有不可重复读、第二类事务丢失。
3. 可重复读(repeatable read)。(写、读加锁,默认隔离级别)
   对读操作加读锁到事务结束，其他事务的更新操作等到事务结束后进行。和提交读的区别在于,提交读的读操作是加读锁到本次读操作结束，可重复读的锁粒度更大。禁止两类丢失更新,禁止脏读和不可重复度,但能出现幻读。
4. 串行化(serializable)。(对表级读、写加锁)
   读操作加表级读锁至事务结束。可以禁止幻读。会导致大量的操作超时和锁竞争，从而大大降低数据库的性能。

事务的状态

远看: 事务就是密不可分的一个整体。
近看: Active(执行中)、Commited(成功)、Failed(失败)
进看: 
    Active：事务的初始状态，表示事务正在执行；
    Partially Commited：在最后一条语句执行之后；
    Failed：发现事务无法正常执行之后；
    Aborted：事务被回滚并且数据库恢复到了事务进行之前的状态之后；
    Commited：成功执行整个事务；

3、数据库的五种BUG读

脏读:(读取了未提交的新事物,然后被回滚)

A读取B尚未提交的数据,B发生错误并执行回滚,A读取到的数据就是脏数据。

不可重复读:(读取了提交的新事物,指更新操作)

A第一次读取数据，B执行更改操作,此时A第二次读取数据时,发现和之前的数据不一样,为不可重复读。

幻读:(也是读取了提交的新事物,指增删操作)

A在读操作,在查询数据总量后,此时B执行了新增数据的操作并提交后,这时A读取的数据总量和之前统计的不一样,像产生幻觉,成为幻读。

第一类事务丢失:(回滚丢失)

A和B同时在执行一个数据,B已经提交,A回滚，B的操作因A的回滚而丢失了。

第二类事务丢失:(提交覆盖丢失)

称为覆盖丢失,A和B一起执行一个数据,同时取到一个数据,然后B先提交,A再提交,A覆盖了B事物,为覆盖丢失。

4、MVCC(多版本并发控制)解决幻读

InnoDB给每行增加两个隐藏字段来实现MVCC为记录数据行的创建事务的版本号、记录行的过期事务的版本号(删除时间),每开启一个新事务,事务的版本号就递增。
在RR级别下: SELECT读取创建版本小于或等于当前事务版本号,且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
insert 新插入一行,并以当前事务的版本号作为新行的创建版本号。
update 将当前事务的版本号保存至行的创建版本号,同时将原记录行的删除版本号设置为当前事务版本号。
delste 将当前事务的版本号保存至行的删除版本号。
查找数据行版本早于当前事务版本的数据行(行的系统版本号小于或者等于当前事务版本号)这样可以确保事务读取的行要么在事务开始之前就已经存在了，要么是事务本身插入或者修改的。
查找事务删除版本号要么为null,要么大于当前事务版本号。确保取出来的行记录在事务开始前没有被删除。

5、事务启动方式:

1、显式: begin、start transaction read only、start transaction read write、start transaction with consistent snapshot。
2、隐式: set autocommit=0,该命令会把这个线程的自动提交关掉。执行一个select语句,事务就启动,并不会自动提交,直到主动执行commit或rollback或断开连接(事务长时间不提交,导致后续对该表的DDL堵塞住)。

begin	首先检查是否有活跃的事务还未提交,如果没有提交,则调用ha_commit_trans提交之前的事务,并释放之前事务持有的MDL锁。 执行 begin 命令并不会真的去引擎层开启一个事务,仅仅是为当前线程设定标记,表示为显式开启的事务。和BEGIN等效的命令还有 begin work、start transaction。
start transaction read only	从5.6版本开始引入。将全局事务链表拆成了两个链表：一个用于维护只读事务,一个用于维护读写事务。这样我们在构建一个一致性视图时,只需要遍历读写事务链表即可。约束一个事务为只读的,而只读事务在引擎层可以走优化过的逻辑,相比读写事务的开销更小,不用分配事务id、不用分配回滚段、不用维护到全局事务链表中。当Server层接受到任何数据更改的SQL时,都会直接拒绝请求,返回错误码ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION,不会进入引擎层。5.7版本引入了模式自动转换的功能(start transaction read only-->start transaction),但该语法依然保留了。
start transaction read write	开启读写事务。
start transaction with consistent snapshot	开启事务时还会顺便创建一个视图(Read View),在InnoDB中,视图用于描述一个事务的可见性范围,也是多版本特性的重要组成部分。

6、事务启动时机:

5.7版本InnoDB里总是默认一个事务开启时的状态为只读的,begin/start transaction 命令并不是一个事务的起点,如果事务第一条SQL是只读查询,那么在InnoDB层,它的事务状态是只读的,如果第二条SQL是更新操作,就将事务转换成读写模式。要马上启动一个事务,用 start transaction with consistent snapshot 这个命令。

7、事务提交

隐式提交: 当显式开启一个新事务,或执行一条非临时表的DDL语句时,会隐式的将上一个事务提交掉。
显式提交: 执行 'commit' 语句提交事务;回滚rollback。

Sql语句:

1.关闭、开启自动事务
set autocommit =0;
set autocommit =1;
2.显式开启
start transaction;
select balance from bank where name="JunSouth"; //事务开始,不分配事务ID,不分配回滚段.
update bank set balance = 500;                 //分配事务ID并插入全局事务数组和事务对象集合中,分配回滚段.
update finance set amount = 600;
commit;

部分回滚(avepoint)
replace into account(username,money) values('AA',300);
replace into account(username,money) values('BB',500);
savepoint 'jun';
replace into account(username,money) values('CC',600);
rollback to savepoint 'jun';

不能事务处理的命令(直接提交,直接commit)
drop database; 
drop table;
drop;
alter table;

查看当前会话隔离级别

select @@tx_isolation;
查看系统当前隔离级别
select @@global.tx_isolation;
设置当前会话隔离级别
set session transaction isolation level read uncommitted ;
设置系统当前隔离级别
set global transaction isolation level read repeatable ;

ACID事务的实现(回滚日志)

SQL Server由两个文件组成:数据库文件、日志文件, 日志文件比数据库文件大,写操作都先写日志。执行事务时会记录下这个事务的redo日志并把日志文件写入磁盘,然后才操作数据库。
恢复机制通过回滚日志(undo log)实现,是逻辑日志,当用回滚日志,会按日志逻辑将数据库中的修改撤掉,如每条 insert 都对应了一条 delete, update对应一条相反的 update 语句。
1、能在发生错误或者用户执行 rollback 提供回滚相关的信息。
2、能够在整个系统发生崩溃后,再次启动数据库时,能通过查询回滚日志,将之前未完成的事务进行回滚。

undo关键结构体

1、所有回滚段都记录在trx_sys->rseg_array,数组大小为128,分别对应不同的回滚段。
2、rseg_array数组类型为trx_rseg_t,用于维护回滚段相关信息。
3、每个回滚段对象trx_rseg_t还要管理undo log信息,对应结构体为trx_undo_t,使用多个链表来维护trx_undo_t信息。
4、事务开启时,会专门给他指定一个回滚段,以后该事务用到的undo log页,就从该回滚段上分配。
5、事务提交后,需要purge的回滚段会被放到purge队列上(purge_sys->purge_queue)。

分布式系统理论

1、CAP(Consistency、Availability、Partition tolerance)

一致性(Consistency):   数据更新是一致的,所有数据节点的变动都是同步的,同时发生,同时生效。
可用性(Availability):      性能好+可靠性,集群中一部分节点故障后,集群整体还能响应客户端的请求。
分区容错性(Partition tolerance):  系统可跨网络分区线性的伸缩和扩展, 一个分布式系统里面,节点组成的网络应是连通的。因故障,有些节点之间不连通了,整个网络分成几块区域。数据就散布在这些不连通的区域中。
当一个数据项只在一个节点中保存,那么分区出现后,和这个节点不连通的部分就访问不到这个数据了。(分区容错性差)
数据项复制到多个节点上,出现分区之后,数据项就分到各个区里。把数据复制到多个节点,可能数据是不一致。(分区容错性好,一致性就差)
保证一致,每次写操作就要等节点全写成功,又会带来可用性的问题。(分区容错性好,一致性好,可用性就差)。任何分布式系统只可同时满足两点,无法三者兼顾。

2、BASE

Basically Available(基本业务可用性(支持分区失败))，Soft state(软状态,状态允许有短时间不同步,异步)，Eventuallyconsistent(最终一致性(最终数据是一致的,但不是实时一致))
无法做到强一致,但每个应用都可以根据自身的业务特点,采用适当的方式来使系统达到最终一致性。

分布式事务解决方案

1、两阶段提交(2PC)

优点:  尽量保证了数据的强一致,适合对数据强一致要求很高的关键领域。
缺点:  实现复杂,牺牲了可用性(牺牲一部分可用性来换取的一致性),对性能影响较大,不适合高并发高性能场景。

2、补偿事务(TCC)

优点:  跟2PC比起来,实现以及流程相对简单,数据的一致性比2PC要差。
缺点:  在事务发起方远程调用事务参与方的confirm、cancel方法中都有可能失败。TCC属于应用层的一种补偿方式,实现的时多写很多补偿的代码,在一些场景中,一些业务流程可能用TCC不好定义及处理。

3、本地消息表(异步确保)

遵循BASE理论,采用最终一致性。使用独立消息服务 + MQ实现最终消息一致性(业界使用最多)。不会出现复杂的实现(当调用链很长的时候,2PC的可用性是非常低的),也不会像TCC那样可能出现确认或者回滚不了的情况。
优点: 一种非常经典的实现,避免了分布式事务,实现了最终一致性。
缺点: 消息表会耦合到业务系统中,如果没有封装好的解决方案,有很多杂活要处理。

刚性事务:     满足 ACID 理论,工作在数据资源层,也就是比如数据库,MQ中间件。以 xa 2pc为代表。
柔性事务:     满足 BASE 理论,数据最终一致。工作在数据业务层,如应用服务、代码层面上。TCC补偿型为代表。

主从复制

1、原理

通过3个线程实现同步操作:
主库线程:           主服务器将数据的更新记录到二进制日志中(记录被称作二进制日志事件)。
从库I/O线程:      从库将主库的二进制日志复制到本地的中继日志(relay log)。
从库SQL线程:    从库读取中继日志中的事件,将更新到数据中。

2、配置主库

1).创建用户

建新用户用于从库连接主库
    # 创建用户 
    create user 'repl'@'%' identified by 'repl'; 
    # 授权，只授予复制和客户端访问权限 
    grant replication slave,replication client on *.* to 'repl'@'%' identified by 'repl';

2).修改配置文件

vim /etc/my.cnf 在[mysqld]下添加：
    log-bin         = mysql-bin 
    log-bin-index   = mysql-bin.index 
    binlog_format   = mixed 
    server-id       = 21 
    sync-binlog     = 1 
    character-set-server = utf8
    配置文件说明
    log-bin: 设置二进制日志文件的基本名； 
    log-bin-index: 设置二进制日志索引文件名； 
    binlog_format: 控制二进制日志格式，进而控制了复制类型，三个可选值 
        -STATEMENT: 语句复制 
        -ROW: 行复制 
        -MIXED: 混和复制,默认选项 
    server-id: 服务器设置唯一ID,默认为1,推荐取IP最后部分； 
    sync-binlog: 默认为0,为保证不会丢失数据,需设置为1,用于强制每次提交事务时,同步二进制日志到磁盘上。 
保存文件并重启主库：
    service mysqld restart 

3).备份主数据库数据

若主库可停机,则直接拷贝所有数据库文件。
若主库是在线生产库,可采用 mysqldump 备份数据,它对所有存储引擎均可使用。
    1、为了获取一个一致性的快照，需对所有表设置读锁:
    flush tables with read lock; 
    2、获取二进制日志的坐标:
    show master status; 
    3、备份数据:
    # 针对事务性引擎 
    mysqldump -uroot -ptiger --all-database -e --single-transaction --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql 
    # 针对 MyISAM 引擎，或多引擎混合的数据库 
    mysqldump -uroot --all-database -e -l --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql 
    4、恢复主库的写操作:
    unlock tables;

3、配置从库

1.修改配置文件

   1、vim /etc/my.cnf 在[mysqld]下添加：
        log-bin             = mysql-bin
        binlog_format       = mixed
        log-slave-updates   = 0
        server-id           = 22
        relay-log           = mysql-relay-bin
        relay-log-index     = mysql-relay-bin.index
        read-only           = 1
        slave_net_timeout   = 10
        log-slave-updates：控制 slave 上的更新是否写入二进制日志，默认为0；若 slave 只作为从服务器，则不必启用；若 slave 作为其他服务器的 master，则需启用，启用时需和 log-bin、binlog-format 一起使用，这样 slave 从主库读取日志并重做，然后记录到自己的二进制日志中；
        relay-log：设置中继日志文件基本名；
        relay-log-index：设置中继日志索引文件名；
        read-only：设置 slave 为只读，但具有super权限的用户仍然可写；
        slave_net_timeout：设置网络超时时间，即多长时间测试一下主从是否连接，默认为3600秒，即1小时，这个值在生产环境过大，我们将其修改为10秒，即若主从中断10秒，则触发重新连接动作。
    2、保存文件并重启从库
        service mysqld restart

2.导入备份数据

    如果主库没备份,忽略此步骤。
    mysql -uroot -p < /data/all_db.sql

3.统一二进制日志的坐标

    主库统一到从库中：
    change master to
    master_host='192.168.2.21',
    master_user='repl',
    master_password='repl',
    master_port=3306,
    master_log_file='mysql-bin.000001',
    master_log_pos=120;

4.启动主从复制

    1、启动从库 slave 线程：
    start slave;
    2、查看从服务器复制功能状态：
    show slave status\G;