第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四类隔离级别
1READ UNCOMMITTED
读取未提交内容隔离级别,即所有事务都可以看到其他未提交事务的执行结果。

2READ CONMITTED
该隔离级别满足隔离的简单定义,即一个事务只能看见已经提交事务所做的改变·这种情况下,用户可以避免脏读。

3REPEATABLE READ
可重复读隔离级别,是MySQL的默认事务隔离级别·它确保同一个事务的多个实例在并发读取数据时,会看到同样的数据行。

4SERIALIZABLE
该级别是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读、脏读和重复读的问题。

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数据文件中

 

posted @ 2022-12-01 17:50  小粉优化大师  阅读(32)  评论(0编辑  收藏  举报