MySQL学习笔记第5课（共10课）

最新推荐文章于 2021-12-04 11:52:43 发布

DataGPT

最新推荐文章于 2021-12-04 11:52:43 发布

阅读量2.6k

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/jiangshouzhuang/article/details/52144950

版权

MySQL 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

27、阻塞

当一个资源已被一个事务占有时，另一个事务执行mutex_enter函数会发生等待，这个就是阻塞。阻塞并不是一件坏事，阻塞是为了保证事务可以并发且正常运行。

在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来控制等待的时间（默认是50s）。

mysql> select@@innodb_lock_wait_timeout;

+----------------------------+

|@@innodb_lock_wait_timeout |

+----------------------------+

| 50 |

+----------------------------+

innodb_rollback_on_timeout参数用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是OFF，代表不回滚）。参数innodb_lock_wait_timeout是动态的，可以在MySQL数据库运行时进行调整，而innodb_rollback_on_timeout是静态的，不可在启动时进行修改。

mysql> set@@innodb_lock_wait_timeout=60;

Query OK, 0 rowsaffected (0.00 sec)

mysql> set@@innodb_rollback_on_timeout=on;

ERROR 1238(HY000): Variable 'innodb_rollback_on_timeout' is a read only variable

举例：

SessionA：

mysql> begin;

Query OK, 0 rowsaffected (0.01 sec)

mysql> select* from t for update;

+----+---------+

| id | account |

+----+---------+

| 1 | 1000 |

| 2 | 2000 |

+----+---------+

Session B：

mysql> begin;

Query OK, 0 rowsaffected (0.00 sec)

mysql> insertinto t select 2,3000;

ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

如果超过默认50s时，就出现上面的超时。

需要牢记的是：默认情况下InnoDB存储引擎不会回滚超时引发的错误异常。其实InnoDB存储引擎在大部分情况下都不会对异常进行回滚，但是会对死锁进行回滚，下面会介绍。

如果在上面的Session A中，

mysql> select* from t where id < 6 for update;

则在Session B中，

mysql> insert intot select 9,5000;

Query OK, 1 rowaffected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insertinto t select 4,5000;

插入id为4时，会被阻塞。

但是在超时后，我们选择select会发现，9这个记录并没有回滚，但是既没有commit，也没有rollback，这会十分危险的，用户必须判断是需要commit还是需要rollback，然后再进行下一步操作。

发现死锁后，InnoDB存储引擎会马上回滚一个事务，这点是需要注意的。如果在应用程序中捕获到1213这个错误，其实并不需要对其进行回滚。

Oracle数据库中产生死锁的常见原因是没有对外键添加索引，而InnoDB存储引擎会自动对其进行添加，因为可以很好地避免这种问题的发生，人为地删除外键上的索引数据库会抛出一个异常。

mysql> createtable p (a int ,primary key(a));

Query OK, 0 rowsaffected (0.38 sec)

mysql> createtable c (b int,foreign key(b) references p(a))engine=innodb;

Query OK, 0 rowsaffected (0.12 sec)

mysql> showindex from c\G

***************************1. row ***************************

Table: c

Non_unique: 1

Key_name: b

Seq_in_index: 1

Column_name: b

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set(0.00 sec)

可以看到，虽然在建立子表时指定了外键，但是InnoDB存储引擎还是自动再外键列上建立了索引b，而人为删除这个列却是不允许的。

mysql> dropindex b on c;

ERROR 1553(HY000): Cannot drop index 'b': needed in a foreign key constraint

29、锁升级

InnoDB存储引擎不存在锁升级的问题。在InnoDB存储引擎中，1个锁的开销与1000000个锁是一样的，都没有开销。这一点和Oracle数据比较类似。

30、事务

事务会把数据库从一种状态转换为另一种一致状态。保证数据库的完整性，要么都做修改，要么什么都不做。

InnoDB存储引擎中的事务完全符合ACID的特性。ACID是：

（1）原子性整个数据库事务是不可分割的工作单位。

（2）一致性事务将数据库从一种状态转换为下一种一致状态

（3）隔离性一个事务的影响在该事务提交前对其他事务都不可见-通过锁实现。其他三个包括原子性，一致性，持久性是通过数据库的redo和undo实现的。

（4）持久性事务一旦提交，其结果就是永久性的。

30.1 redo

在InnoDB存储引擎中，事务日志通过重做日志文件和InnoDB存储引擎的日志缓冲来实现。

当开始一个事务时，会记录该事务的一个LSN（Log SequenceNumber,日志序列号）

当事务执行时，会往InnoDB存储引擎的日志缓冲里插入事务日志

当事务提交时，必须将InnoDB存储引擎的日志缓冲写入磁盘（默认的实现方式：innodb_flush_log_at_trx_commit=1）。也就是在数据前，需要先写日志。这种方式称为预写日志方式（WAL，Write-AheadLogging）

InnoDB存储引擎通过预写日志的方式来保证事务的完整性。在内存缓冲中页的修改，先是写入重做日志文件，然后再写入磁盘，因此是一种异步的方式。通过命令show engine innodb status来观察当前磁盘和日志的差别。

mysql> showengine innodb status\G

***************************1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

120907 17:17:59 INNODB MONITOR OUTPUT

Per secondaverages calculated from the last 2 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_threadloops: 2421 1_second, 2421 sleeps, 220 10_second, 221 background, 221 flush

srv_master_threadlog flush and writes: 2424

---

LOG

---

Log sequencenumber 3383730717 -----表示当前的LSN

Log flushed upto 3383730717 -------表示刷新到重做日志文件的LSN

Last checkpointat 3383730717 --------表示刷新到磁盘的LSN

0 pending logwrites, 0 pending chkp writes

24 log i/o'sdone, 0.00 log i/o's/second

mysql> callload_test(10000);

Query OK, 0 rowsaffected (0.64 sec)

mysql> showengine innodb status\G

***************************1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

120907 17:42:13INNODB MONITOR OUTPUT

=====================================

Per secondaverages calculated from the last 19 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_threadloops: 2705 1_second, 2704 sleeps, 245 10_second, 248 background, 248 flush

srv_master_threadlog flush and writes: 2707

……………..

---

LOG

---

Log sequence number 3384239781

Log flushed up to 3384239781

Last checkpoint at 3383735233

0 pending logwrites, 0 pending chkp writes

34 log i/o'sdone, 0.16 log i/o's/second

注：可以将Log flushed up to和Last checkpoint at的差值504548（~492.7K）理解为重做日志产生的增量（以字节为单位）

其实Log sequence number和Log flushed up to在实际的生产环境中，该值有可能不同的。

30.2 undo

undo存放在数据库内部的一个特殊段中，这称为undo段，undo段位于共享表空间内。

30.3 事务控制语句

在MySQL命令行的默认设置下，事务都是自动提交的。因此开始一个事务，必须使用BEGIN、Start Transaction，或者执行set autocommit=0，以禁止用当前会话的自动提交。

COMMIT和COMMIT WORK语句基本是一致的，都是用来提交事务的。不同之处在于，COMMITWORK用来控制事务结束后的行为，是CHAIN还是RELEASE的。可以通过参数completion_type来进行控制，默认情况下该参数为0，表示没有任何操作。在这种设置下，COMMIT和COMMIT WORK是完全等价的。当参数completion_type的值为1时，COMMIT WORK等同于COMMIT AND CHAIN，表示马上自动开启一个相同隔离级别的事务，

举例：

mysql> createtable t (a int,primary key(a)) engine=innodb;

Query OK, 0 rowsaffected (0.07 sec)

mysql> select@@autocommit;

+--------------+

| @@autocommit |

+--------------+

| 1 |

+--------------+

1 row in set(0.00 sec)

mysql> set@@completion_type=1;

Query OK, 0 rowsaffected (0.00 sec)