13. 事务¶
1. 基本概念(略)¶
# 事务是innodb存储引擎的核心特性
2. 事务的作用¶
# 事务是保证数据的ACID特性
3. 标准事务控制语句¶
set autocommit=0;/set global autocommit=0;
begin;
DML1
DML2
...
commit;/rollback;
# 关闭自动提交
set autocommit=0;
set global autocommit=0;
# 永久修改
vim /etc/my.cnf
[mysqld]
autocommit=0;
4. 隐式提交¶
4.1 用于隐式提交的SQL语句¶
start transaction
set autocommit=1;
4.2 导致隐式提交的非事务语句¶
1.DDL语句:(alter、create、drop)
2.DCL语句:(grant、revoke、set password)
3.锁定语句:(lock tables、unlock tables)
4.3 导致隐式提交的语句示例¶
truncate table
load data infile
select for update(#这个是什么???)
5. innodb存储引擎物理存储层¶
表空间:t1.ibd
段:一个表就是一个段(分区表除外) #分区表是什么???
区:连续的多个页
页:page(16kb)
6. 存储引擎事务日志¶
6.1 redo:重做日志¶
1.作用:在事务acid过程中,实现d持久化的作用,ac的特性也与redo有关
2.存储位置:
数据路径下存放,默认2个日志,默认大小48M一个
ib_logfile0
ib_logfile1
3.查看:
select variables like 'innodb_log_file%';
innodb_log_file_size=50331648
innodb_log_files_in_group=2
4.redo记录的内容:
1.内存数据页的变化
2.lsn日志序列号
数据库启动时,必须保证data page和logfile中lsn一致,才能正常打开数据,否则就需要自动恢复
可以在csr过程中,实现前滚功能
6.2 undo:回滚日志¶
1.作用:
在事务acid过程中,实现aci原子性,一致性等作用,隔离级别的实现
2.undo记录的内容:
1.数据页修改之前的状态
2.txid
3.作用:(为何又有一个)
1.rollback
2.csr过程中,实现回滚
7. 锁与隔离级别¶
锁的作用:
在事务acid过程中,锁和隔离级别一起实现I隔离性的作用
7.1 查看默认锁等待时间¶
show variables like '%wait%';
innodb_lock_wait_timeout=50
mysql> show variables like '%wait%';
+---------------------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| innodb_spin_wait_delay | 6 |
| lock_wait_timeout | 31536000 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| wait_timeout | 28800 |
+---------------------------------------------------+----------+
6 rows in set (0.00 sec)
# 要避免锁等待
7.2 锁测试¶
1.关闭自动提交
set autocommit=0;
set global autocommit=0;
2.打开一个窗口1,在表中插入一条数据
use world;
update city set population=100 where id=10;
3.另外打开一个窗口2,再次在同一表中插入一条数据
use world;
update city set population=100 where id=10;
发现窗口2卡在这里
4.窗口1 可以查看连接状态
show processlist;
可以看到2条语句。等待50秒后,窗口2自动失败
mysql> show processlist;
+--------+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------+------+---------+------+----------+------------------+
| 172689 | root | localhost | NULL | Query | 0 | starting | show processlist |
+--------+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update city set population=100 where id=10;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> show processlist;
+--------+------+-----------+-------+---------+------+----------+--------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------+-------+---------+------+----------+--------------------------------------------+
| 172689 | root | localhost | world | Query | 0 | starting | show processlist |
| 197103 | root | localhost | world | Query | 13 | updating | update city set population=100 where id=10 |
+--------+------+-----------+-------+---------+------+----------+--------------------------------------------+
2 rows in set (0.00 sec)
# 等待50秒后,窗口2自动关闭连接!
mysql> show processlist;
+--------+------+-----------+-------+---------+------+----------+--------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------+-------+---------+------+----------+--------------------------------------------+
| 172689 | root | localhost | world | Query | 0 | starting | show processlist |
| 197103 | root | localhost | world | Query | 48 | updating | update city set population=100 where id=10 |
+--------+------+-----------+-------+---------+------+----------+--------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> show processlist;
+--------+------+-----------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------+-------+---------+------+----------+------------------+
| 172689 | root | localhost | world | Query | 0 | starting | show processlist |
| 197103 | root | localhost | world | Sleep | 50 | | NULL |
+--------+------+-----------+-------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
# 此时,查看窗口2,已断开连接!
mysql> update city set population=100 where id=10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
7.3 隔离级别¶
read uncommitted: 出现脏读
read committed: 容易出现幻读(提交前,提交后,查询结果不一致)
repeatable read: 读取undo日志中的内容,避免幻读
serializable: 不常用。
最后更新:
2022-02-20 08:44:07