跳转至

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语句:(altercreatedrop
2.DCL语句:(grantrevokeset password
3.锁定语句:(lock tablesunlock tables

4.3 导致隐式提交的语句示例

truncate table
load data infile
select for update#这个是什么???)

5. innodb存储引擎物理存储层

表空间:t1.ibd
段:一个表就是一个段(分区表除外)   #分区表是什么???
区:连续的多个页
页:page16kb

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