16 MySQL 从入门到精通——事务

示例库1
CREATE TABLE tb_account(
  id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(30),
  balance FLOAT(8,2) unsigned DEFAULT 0
);
INSERT INTO tb_account (name,balance)VALUES
('A',1000),
('B',0);

事务机制
	事务的概念:事务是指一组相互依赖的操作单元的集合，用来保证对数据库的正确修改，保持数据的完整性，如果一个事务的某个单元操作失败，将取消本次事务的全部操作。
		数据库事务所具备的特征:
			原子性（Atomicity）:最小单元，不可分割，整体性，例如一个事务有多个操作组成要不同时操作成功要不不进行操作，不能一个成功一个失败
			一致性（Consistency）：状态一致性，要不都操作成功，要不都不操作
			隔离性（Isolation）：在并发环境中，一个事务所做的修改必须与其它事务所做的修改相隔离。
		持久性（Durability）：事务操作后会更新并记录日志，如果硬盘突然损毁也可以通过日志和备份进行恢复				
	事务机制的必要性:例如银行转账，转出与转入必须都成功
	关闭MySQL自动提交：有的存储过程没有使用事务，如果函数里面有多个操作，每次调用错误的不执行正确的会直接提交到数据库中，所以要关闭自动提交
		显式关闭自动提交功能
			查看自动提交变量：SHOW VARIABLES like 'AUTOCOMMIT';
			设置关闭自动提交:	set AUTOCOMMIT = 0
			设置开启自动提交：set AUTOCOMMIT = 1
		隐式关闭自动提交功能
			开启事务时默认关闭自动提交：START TRANSACTION;
				
	事务回滚：需要关闭MySQL自动提交
		ROLLBACK；
	事务提交
		显式提交
			COMMIT
		隐式提交：当使用以下命令时会默认提交
			BEGIN
			START TRANSACTION
			TRUNCATE TABLE
			RENAME TABLE
			SET AUTOCOMMIT=1
			CREATE DATABASE/TABLE/INDEX/PROCEDURE
			ALTER DATABASE/TABLE/INDEX/PROCEDURE
			DROP DATABASE/TABLE/INDEX/PROCEDURE
			LOCK TABLES
			UNLOCK TABLES							
	MySQL中的事务
		示例：
			CREATE PROCEDURE prog_tran_account(IN id_from INT,IN id_to INT,IN money int)
			MODIFIES SQL DATA
			BEGIN
			DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;	#声明如果异常则回滚
			START TRANSACTION;	#开启事务
			UPDATE tb_account SET balance=balance+money WHERE id=id_to;
			UPDATE tb_account SET balance=balance-money WHERE id=id_from;
			COMMIT;	#提交
			END
			//	
			验证：							
			CALL prog_tran_account(1,2,1500);
			SELECT * FROM tb_account;
							
			CALL prog_tran_account(1,2,200);
			SELECT * FROM tb_account;				
	回退点（保存点）	：设定回滚到哪个状态，哪个点
		设置回退点的语法格式如下：
			SAVEPOINT 回退点名;
		滚到指定的回退点的语法格式如下：
			rollback to savepoint 定义的回退点名;
		
		示例：创建一个名称为prog_savepoint_account的存储过程，在该存储过程中创建一个事务，实现向tb_account表中添加一个账户C，并且向该账户存入1000元。然后从A账户向B账户转账500元。当出现错误时，回滚到提前定义的回退点，否则提交事务。
			DELIMITER //
			CREATE PROCEDURE prog_savepoint_account()
			MODIFIES SQL DATA
			BEGIN
			DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
			BEGIN
			ROLLBACK TO A;	#声明如果出现异常则回滚到回退点A
			COMMIT;
			END;
			START TRANSACTION;
			START TRANSACTION;
			INSERT INTO tb_account (name,balance)VALUES('C',1000);
			savepoint A;	#定义回退点A
			UPDATE tb_account SET balance=balance+500 WHERE id=2;
			UPDATE tb_account SET balance=balance-500 WHERE id=1;
			COMMIT;
			END
			//
			验证：				
			CALL prog_savepoint_account();
			SELECT * FROM tb_account;				
		
锁机制
	MySQL锁机制的基本知识
		读锁：是共享所，自己和其他人都可以读但是不能写
		写锁：排它锁，自己可以写入和读取但其他人不允许再写和读
		锁粒度：是指锁的作用范围。原则是让锁定对象更有选择性。也就是尽量只锁定部分数据，而不是所有的资源。
			锁粒度越小，越适合做并发更新操作。
			锁粒度越大，越适合做并发查询操作。
		锁策略：是指在锁的开销和数据的安全性之间寻求平衡。创建锁时其他人就需要等待需要消耗一定的内存所以涉及到一定的开销
		表级锁：是MySQL中最基本的锁策略，而且是开销最小的策略。
		行级锁：可以最大程度地支持并发处理，同时也带来了最大的锁开销。
		锁的生命周期：是指在一个MySQL会话内，对数据进行加锁到解锁之间的时间间隔。要尽可能的缩短锁的生命周期。
	MyISAM表的表级锁：如果我们创建一个数据表，他的数据类引擎是MyISAM，那么这种数据类型的数据表是不支持事务的，所以也就不支持commit提交和ROLLBACK回滚,当用户对数据库进行插入删除更新这些操作的时候，这些数据的变化就会被保存到磁盘中，所以当用户较多的时候就会导致很多问题，为了避免同一时间多个用户对数据库指定表进行操作，所以就使用表级锁来避免这种情况。当且仅有用户释放表的锁操作时其他的用户才能访问这些修改表的数据
		流程：
			（1）为指定数据表添加锁定。其语法如下：
				LOCK TABLES table_name lock_type,…
					#lock_type：READ/WRITE #读锁或写锁
			其中table_name为被锁定的表名，lock_type为锁定类型，该类型包括以读方式（READ）锁定表； 以写方式（WRITE）锁定表。
			（2）用户执行数据表的操作，可以添加、删除或者更改部分数据。
			（3）用户完成对锁定数据表的操作后，需要对该表进行解锁操作，释放该表的锁定状态。其语法如下：
				UNLOCK TABLES					
	InnoDB表的行级锁：如果一个表他的引擎是innoDB的话，那么它既有表级锁又有行级锁,其中表级锁与MyISAM的表级锁相同
		在查询语句中设置读锁，其语法格式如下：
			SELECT语句 LOCK IN SHARE MODE;
			示例：
				SELECT * FROM tb_account LOCK IN SHARE MODE;
		在查询语句中设置写锁，其语法格式如下：				
			SELECT语句 FOR UPDATE;
			示例：
				SELECT * FROM tb_account FOR UPDATE;
				
		在更新语句中，InnoDB存储引擎自动为更新语句影响的记录添加隐式写锁

		通过事务实现延长行级锁的生命周期：可以在事务里创建一个行级锁，这时候其他用户受锁行级锁影响不能写入操作，等事务进行commit操作后其他用户才能正常操作		
					
	死锁的概念与避免
		死锁：即当两个或者多个处于不同序列的用户打算同时更新某相同的数据库时，因互相等待对方释放权限而导致双方一直处于等待状态。例如事务A一直等待事务B释放id为2的行锁，事务B一直等待事务A释放id为1的行锁，于是就进入了死锁状态
		避免方法：
			1.设置InnoDB的超时等待时间，不能设置太短，太短可能会误杀到一些其它的事务
			2.发起死锁检测，发起死锁检测后会自动回滚死锁链条中的某一个事务，让其他事务得以执行
	事务的隔离级别
		事务的隔离级别与并发问题
			4种事务隔离级别：以下是从高到低四种，一般选择第三种
				Serializable（串行化）
				Repeatable Read（可重复读）
				Read Committed（读已提交数据）
				Read Uncommitted（读未提交数据）
							
		设置事务的隔离级别
			Usage：
				SET {GLOBAL|SESSION} TRANSACTION ISOLATION LEVEL 具体级别;	#隔离级别就是以上四种