第16节-MySQL锁与事务
1、事务的介绍
1、事务是一组有着内在逻辑联系的SQL命令。 2、支持事务的数据库系统要么执行一个事务里的所有SQL命令,要么把它们当作整体全部放弃。 3、事务永远不会只完成一部分。 4、事务可以由一条非常简单的SQL语句组成,也可以由一组复杂的SQL语句组成。在事务中的操作,要么都执行,要么都不执行,这就是事务的目的,也是事务的重要特征之一。 4、使用事务可以大大提高数据安全性和执行效率,因为在执行多条SQL命令的过程中不必再使用LOCK命令锁定整个数据表
2、支持事务的存储引擎
MySQL目前只支持 InnoDB 和 BDB 存储引擎数据表上的事务。
3、事务的特征
事务必须同时满足4个特征,俗称为ACID标准。 1、原子性(Atomicity ) 原子性( Atomicity)是指整个数据库事务是不可分割的操作单位。 2、一致性( Consistency ) 是指事务将数据库从一种状态变成另一种一致的状态,在事务开始之前和事务结束之后,数据库的完整性约束没有被破坏。 3、隔离性( Isolation ) 隔离性(Isolation)要求每个读写事务的对象与其他事务的操作对象能互相分离,即该事务提交前对其他事务都不可见,这通常使用锁来实现。 4、持久性( Durability ) 事务一旦提交,其结果就是永久性的,即使发生死机等故障,数据库也能将数据恢复О持久性 ( Durability〉只能从事务本身的角度来保证结果的永久性,如事务提交后,所有的变化都是永久的,
即使当数据库由于崩溃而需要恢复时,也能保证恢复后提交的数据都不会丢失。
4、事务的隔离级别
MySQL中的隔离级别,用来限定事务内外的哪些改变是可见的,哪些是不可见的,级别的隔离一般支持高级别的并发处理,并拥有更低的系统开销。 MySQL四类隔离级别 1、READ UNCOMMITTED 读取未提交内容隔离级别,即所有事务都可以看到其他未提交事务的执行结果。 2、READ CONMITTED 该隔离级别满足隔离的简单定义,即一个事务只能看见已经提交事务所做的改变·这种情况下,用户可以避免脏读。 3、REPEATABLE READ 可重复读隔离级别,是MySQL的默认事务隔离级别·它确保同一个事务的多个实例在并发读取数据时,会看到同样的数据行。 4、SERIALIZABLE 该级别是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读、脏读和重复读的问题。
4.0、设置隔离级别
4.0.1、查看隔离级别
-- 全局的隔离级别 mysql> select @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | -- 默认的隔离级别 +-----------------------+ -- 当前会话的隔离级别 mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ -- 或 mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
4.0.2、设置隔离级别
-- Windows系统的设置方法 mysql> set transaction isolation level read uncommited; -- linux系统的设置方法 -- 当前会话 mysql> set @@session.tx_isolation='read-uncommitted'; -- 全局 mysql> set @@global.tx_isolation='read-uncommitted';
4.0.3、创建测试表
CREATE TABLE `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(40) DEFAULT NULL, `money` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
4.1、READ UNCOMMITTED
-- 设置读未提交【read-uncommitted】 -- 脏读、不可重复读、虚读都有可能发生。 set @@global.tx_isolation='read-uncommitted'; -- 会话1 start transaction; insert into account(name,money) values('cyc',1000); -- 回滚 rollback; -- 提交 commit; -- 会话2 -- 此时会话1,还没有提交,会导致脏读 mysql> select * from account; +----+------+-------+ | id | name | money | +----+------+-------+ | 2 | cyc | 1000 | +----+------+-------+
4.2、READ CONMITTED
-- 设置读未提交【READ CONMITTED】 能避免脏读,不可重复读、虚读都有可能发生。 set @@session.tx_isolation='read-committed'; -- 会话1 start transaction; insert into account(name,money) values('cyc2',2000); -- 回滚 rollback; -- 提交,还没有commit完成,会话2是看到不到,事务里面的数据 commit; -- 会话2 -- 此时会话1,还没有提交,是看不到数据的 mysql> select * from account;
4.3、REPEATABLE READ
-- 默认的模式 能避免脏读,不可重复读、虚读都有可能发生。 软件默认的事务隔离级别 Windows设置方法: mysql> set transaction isolation LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) Linux设置方法: mysql> set tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)
4.4、SERIALIZABLE
-- 能避免脏读、不可重复度、虚读。 Windows设置方法: mysql> set transaction isolation LEVEL SERIALIZABLE; Linux设置方法: mysql> set tx_isolation='serializable'; 事务一: mysql> set tx_isolation='serializable'; mysql> start transaction; mysql> insert into t1(name) values('1'); 事务二: mysql> start transaction; mysql> insert into t1(name) values('2'); 此时插入卡住,等事务一结束了再提交;
5、MySQL锁机制
5.1、锁的分类
1、锁用于解决数据库并发控制问题. 2、MySQL中锁的分类 2.1、共享锁的锁粒度是行或者多行。一个事务获取了共享锁之后,可以对锁定范围内的数据执行读操作。 2.2、排他锁的粒度与共享锁相同,也是行或者多行。一个事务获取了排他锁之后,可以对锁定范围内的数据执行写操作。 2.3、意向锁是一种表锁,锁定的粒度是整张表,分为意向共享锁和意向排他锁两类。意向共享锁表示一个事务有意对数据上共享锁或者排他锁。
“有意”表示事务想执行操作但还没有真正执行。锁和锁之间的关系,要么是相容的,要么是互斥的。
5.2、锁关系图
5.3、锁粒度分类
锁的粒度主要分为表锁和行锁。 1、表锁管理锁的开销最小,同时允许的并发量也是最小的锁机制。 2、MyISAM存储引擎使用该锁机制,当要写入数据时,整个表记录被锁,此时其他读写操作一律等待,行锁可以支持最大的并发。 3、InnoDB存储引擎使用该锁机制,如果要支持并发读写,建议采用lnnoDB存储引擎。
5.4、死锁的处理
1、InnoDB存储引擎自动检测事务的死锁,并回滚一个或几个事务来防止死锁。
2、InnoDB存储引擎不能在MySQL设定表锁的范围或者涉及InnoDB之外的存储引擎所设置锁定的范围检测死锁。通过设置innodb_lock_wait_timeout系统变量的值来解决这些情况。
如果要依靠锁等待超时来解决死锁问题,对于更新事务密集的应用,
3、将有可能导致大量事务的锁等待,导致系统异常,所以不推荐在一个事务中混合更新不同存储类型的表,也不推荐相同类型的表采用不同的锁定方式加锁。
6、事务日志
InnoDB存储引擎引入了与事务相关UNDO日志和REDO日志。
6.1、REDO日志
1、事务执行时需要将执行的事务日志写入到日志文件里,对应的文件为REDO日志。 2、当每条SQL进行数据库更新操作时,首先将REDO日志写入到日志缓冲区。 3、当客户端执行COMMIT命令提交时,日志缓冲区的内容将被刷新到磁盘 4、日志缓冲区的刷新方式和时间间隔通过innodb_f1ush_log_at_trx_commit控制REDO日志对应磁盘上的ib_1ogfi1eN文件,该文件默认为5MB,建议设置为512MB,以便容纳较大的事务·在MySQL崩溃恢复时会重新执行REDO日志中的记录。
6.2、UNDO日志
主要用于事务异常时的数据回滚,具体内容就是复制事务前的数据库内容到UNDO缓冲区,然后在合适的时间将内容刷新到磁盘。 与REDO日志不同的是,磁盘不存在单独的UNDO日志文件,所有的UNDO日志均存放在表空间对应的*.ibd数据文件中