Linux下的MySQL基础学习五

Linux下的MySQL基础学习一：https://blog.csdn.net/Zhang_Yixuan_ss/article/details/85953933

Linux下的MySQL基础学习二：https://blog.csdn.net/Zhang_Yixuan_ss/article/details/86077111

Linux下的MySQL基础学习三：https://blog.csdn.net/Zhang_Yixuan_ss/article/details/86220678

Linux下的MySQL基础学习四：https://blog.csdn.net/Zhang_Yixuan_ss/article/details/86299759

一、触发器

前面学习到一些约束机制都属于被动的约束机制，在检查对数据库的操作违反约束后，只能做些比较简单的动作，比如拒绝操作。比较复杂的操作，还需要程序员去安排。如果我们希望在某个操作后，系统能自动根据条件转去执行某种操作，则可以采用触发机制来实现。

定义：

触发器是根据数据库服务器中发生事件时自动执行的特殊存储过程，其特殊性在于它不需要由用户调用执行，而是当用户对表进行insert、update和delete操作时，自动执行触发器所定义的SQL语句。触发器有时也称为主动规则。触发器由DBMS自动调用对数据库的特定改变进行响应。

触发器由三个部分组成，分别是事件、条件、动作。其中事件是指对数据库的插入，删除，修改等操作。条件指的是触发器将测试条件是否成立。动作指的是如果触发器满足预定条件，那么就由DBMS执行这些动作。

触发器的条件可以看成是一个监视数据库的守护程序，当对数据库的修改满足触发器的事件触发条件时，执行触发器。

优点：

• 触发器是自动的，在对表的数据做了修改之后，立即被激活。
• 触发器可以实现数据库中的相关表的级联修改。
• 实现比check约束定义的限制更为复杂的限制。
• 可比较数据库修改前后数据的状态。
• 维护非规范化数据。

 触发器的定义：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

多语句执行的触发器定义：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

触发器的例子：

mysql> DELIMITER ||
mysql> CREATE TRIGGER demo BEFORE DELETE
    -> ON users FOR EACH ROW
    -> BEGIN
    -> INSERT INTO logs VALUES(NOW());
    -> INSERT INTO logs VALUES(NOW());
    -> END
    -> ||


mysql> DELIMITER ;

#上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作

#就会执行BEGIN和END中的语句，接着使用||结束

#最后使用DELIMITER ; 将结束符号还原

二、并发控制

数据库是一个共享资源，可以为多个用户使用，通常为了充分利用数据库资源，允许多个用户并行的存取数据库。在我们生活中常见的飞机，火车订票系统，银行数据库系统。都是多用户数据库系统。在这样的系统中，在同一时刻并发运行的事务数可达数百万个。

多个用户并行的存取数据库系统就会发生多个用户并发的存取同一数据的情况。如果对这些并发不加控制，数据库就可能存取和存储不正确的数据，破坏数据库的一致性。并发控制机制也是衡量数据库管理系统性能的一个重要指标。

事务的并发执行：

事务可以一个一个的串行执行，即每个事务每时刻执行一个事务，其他事务仅在当前事务执行完成后才开始。事务在执行过程中需要不同的资源，如果是事务串行执行，则很多资源将处于空闲状态。为了充分利用系统资源，发挥数据库共享资源的特点，事务处理系统应该允许多个事务并发执行。

        好处：

• 提高吞吐量和资源利用率。一个事务由多个步骤组成，一些步骤涉及io活动，而另一些涉及CPU活动，计算机系统中CPU与磁盘可以并行运行，因此io活动可以与CPU处理并行进行。利用CPU与io系统的并行性，多个事务可以并行执行。
• 减少等待时间。系统中可能运行着各种各样的事务，一些较短一些较长，如果事务，串行执行，短事务可能得等他前面的长事务完成，导致难以预测的延迟。如果各个事务针对数据库的不同部分进行操作，事务并发执行会更好，各个事务可以共享CPU周期和磁盘存取。

并发操作与数据的不一致性：

例子：

在同一时间中，

航空公司售票点甲读出1航班的机票余数A，设A=50；

航空公司售票点乙读出1航班的机票余数A，设A=50；

甲售出10张，A=A-10=40，写回数据库

乙售出10张，A=A-10=40，写回数据库

结果当前数据库中机票余数剩余40，但实际上卖出了20张票

这个例子反应出了数据库中的不一致性，这种不一致性是由并发操作引起的。在并发操作的情况下，对甲，乙两个事务操作序列的调度是随机的，如果按上述调度序列执行甲事务的修改会被丢失。

并发操作带来的数据不一致性可以分为三类，分别为丢失更新问题，不可重复读问题和读"脏"数据问题。

1. 丢失更新（随着时间，甲乙两个事务交替依次执行Read、减、Write）

过程：Read A（甲）——Read A（乙）——A =A -10（甲）——A=A-10（乙）——Write A（甲）——Write A（乙）

我们会发现，过程执行完后，乙修改完A后，直接将甲修改完的A值覆盖掉了，因此甲修改的值发生了丢失。

1. 不可重复读（甲事务读取数据后，乙事务进行更新，使得甲无法再重复前一次的结果）通常有三种情况：

• 事务甲读取数据对象A并仍在运行，事务乙修改了A的值，如果甲再次读A的值，得到与前一次不同的结果。如图所示：

• 事务甲按照一定条件从数据库中读取了某些记录后，事务乙删除了其中部分记录，当甲再次按相同条件读取数据时，发现某些记录不见了。
• 事务甲按照一定条件从数据库中读取了某些记录后，事务甲插入了一些记录，当甲再次按相同条件读取数据时，发现多了一些记录。

后面两种情况被称为幻影现象。

1. 读“脏”数据（指事务甲修改了一些数据并将其写回到磁盘中，事务乙读取同一数据后，事务甲因为某种原因被撤销，这是事务甲将已经修改过的数据恢复到原值，事务乙读到的数据就与数据库中的数据不一致）

A的初值为。事务甲把A的值修改成40，但没有提交操作，事务乙读取了未提交的A的值40，紧接着，事务甲做了回滚操作，把A的值恢复到50，而事务乙仍在用被撤销前A的值40，则乙读到的A值就为“脏”数据，及不正确的数据。

封锁：

产生数据不一致性的原因主要是因为事务在执行时受到其他事务的干扰，破坏了事务的隔离性。并发控制就是要用正确的方法调度并发操作，从而避免造成数据不一致性。实现并发控制的方法主要有两种：封锁技术和时标技术。

1.封锁类型

所谓封锁就是当一个事务在对某个数据对象进行操作之前。先请求系统对其加锁，成功加锁之后，该事务就对该数据对象有了控制权。只有该事务对其进行解锁之后，其他的事务才能更新它。当前普遍采用的病发控制方法中的封锁类型主要有两种，一种是排它锁，另一种是共享锁。

排他锁：如果是A获得了数据项Q上的排它锁，则事务A即可读Q也可以写Q，其他事务都不能再对Q加上任何类型的锁，直到A释放Q上的锁。

共享锁：如果是A获得数据项Q的共享锁，则A可读，但不能修改Q，其他事务只能再对Q加共享锁不能加排他锁，直到A释放Q上的共享锁。

2.封锁协议

封锁可以有效地控制并发事务之间的相互作用，保证数据的一致性。实际上锁是一个控制块，其中包括被加锁记录的标识符及持有锁的事务的标识符，在封锁时要遵从一定的封锁规则，这些规则规定事务对数据项何时加锁、持有锁时间和何时解锁，称这些为封锁协议。

• 一级封锁协议。更新操作之前，必须先获得排他锁。但数据是不需要加锁的，所以使用一级封锁协议可以解决丢失更新问题，但不能解决不可重复读，读脏数据问题。事物甲在修改数据A之前必须先对其加上排他锁，直到事务结束才释放。事务的结束，包括正常结束和非正常结束。一级封锁协议可防止丢失，修改并保证事务甲也是可以恢复的。

例子：

事务甲进行修改之前先对A进行加排它锁，当事务乙请求对A加排它锁被拒绝后，事务乙只能等待事务甲释放A上的锁后，才能获得对A的排他锁。事务甲提交对A的修改，并释放锁，此时数据库中A的值为修改后的值40。这时事物乙获得对A的排他锁。读取的数据A为甲更新之后的40，再对新值40进行运算，并将结果30写入磁盘。

• 二级封锁协议。在一级封锁协议的基础上，再加上事务在对数据A进行读操作之前，先对A进行加上共享锁，读完后立即释放共享锁。二级协议不仅解决了丢失更新问题，还可以进一步防止读“脏”数据和幻影读，但无法解决“无可重复读”。

例子：A的初始值为50，事务甲在对A修改之前，先对A加排它锁，修改A的值后写回磁盘。这时事务乙请求在A上加共享锁，因为甲已经在A上加了排它锁，所以此时乙不能添加共享锁，乙只能等待，甲撤销了刚才的修改操作，此时A的值恢复为50，甲释放A上的排它锁。这时乙获得A上的共享锁，读取A的值为50。

 

• 三级封锁协议。三级封锁协议不仅解决了丢失更新、不读“脏”数据和幻影读问题，还可以防止不可重复读。

例子：事务甲对A和B都进行了加共享锁，假设A和B的值分别为50和80.事务乙申请对A加排它锁，因为甲已对A加了共享锁，乙不能对A加排它锁，乙处于等待状态，等待甲释放对A的锁。然后事务甲又读取A的数据，进行求和运算提交结果并释放锁。于是乙获得对A的排它锁，然后读取数据进行运算。甲两次读取数据A，得到相同的结果，这就解决了不可重复读的问题。

3.封锁粒度

封锁对象的大小称为封锁粒度。根据对数据的不同处理，封锁的对象可以是这样一些逻辑单元：属性值、属性值的集合、元组、关系、索引项、整个索引值直至真个数据库等。封锁粒度与系统的并发度和并发控制的开销密切相关。封锁粒度越小，系统中能够被封锁的对象就越多，并发度越高，但封锁机构复杂，系统开销就越大。

4.死锁和活锁

封锁的方法可以有效解决并行操作的一致性问题，但也会引发新的问题，即活锁和死锁问题。

1.活锁

系统可能使某个事务永远处于等待状态，得不到封锁的机会，这种现象被称为活锁。

例如：事务甲在对数据R封锁，事务乙又请求封锁R，于是乙会等待。丙也请求封锁R，当甲释放了R上的封锁后首先批准了丙的请求，乙继续等待，然后又有丁请求封锁R，丙释放了R上的封锁后又批准丁的请求，......,乙可能永远处于等待状态，从而发生了活锁。

解决活锁问题的一种简单的方法是采用先来先服务的策略，也就是简单的排队方式。如果运行时，事务有优先级，那么很可能优先级低的事务，即排队也很难轮上封锁的机会。此时可以采用“升级”方法来解决，也就是当一个事务等待若干时间还轮不上封锁时，可以提高其优先级别，这样总能轮上封锁。

2.死锁

系统中有两个和两个以上的事务都处于等待状态，并且每个事务都在等待其中另一个事务解除封锁，它才能继续执行下去，结果会造成任何一个事务都无法继续执行，这种现象称为死锁。

如果事务甲封锁了数据R1，事务乙封锁了数据R2，然后甲又请求R2，因乙已封锁了R2，于是甲等待乙释放R2上的锁；接着乙有申请封锁R1，因甲已封锁了R1，乙也只能等待甲释放R1上的锁。这样就出现甲在等待乙，乙在等待甲，甲乙两个事务永远不能结束，形成了死锁。

3.死锁预防

两种方法：一次加锁法和顺序加锁法

• 一次加锁法是指每个事务必须将所有要使用的数据对象全部依法加锁，并要求加锁成功，只要一个加锁不成功，表示本次加锁失败，则应该立即释放所有加锁成功的数据对象，然后重新开始加锁。使用下图为例。通过一次加锁加以预防，事务甲启动后，立即对数据R1和R2依次加锁，枷锁成功后，执行甲，而乙等待，直到甲执行完成后释放R1和R2上的锁，乙继续执行。

• 顺序加锁法是预先对所有可加锁的数据对象规定一个加锁顺序，每个事务都需要按此顺序加锁，在释放时，按逆序进行。依旧使用下图为例：规定封锁顺序为R1，R2，事务甲、乙都需要按此顺序加锁。甲先封锁R1，在封锁R2，当乙再请求封锁R1时，因为甲已经对R1加锁，乙只能等待，待甲释放R1后，乙再封锁R1，则不会发生死锁。

两种方法的缺点：

一次加锁法的缺点：对某一事务所要使用的全部数据一次性加锁，扩大了封锁范围，从而降低了系统的并发度，其次，数据库中的数据是不断变化的，原来不要求封锁的数据，在执行过程中可能会变成封锁对象，所以很难事先精确地确定每个事务所要封锁的数据对象，这样只能在一开始扩大范围，将可能要封锁的数据全部加锁。

顺序加锁法的缺点：事务的封锁请求锁着事务的执行而动态的执行，所以很难事先确定封锁对象，从而更难确定封锁顺序。即使确定了封锁顺序，随着数据插入、删除等操作的不断变化，维护这些数据的封锁顺序需要很大的系统开销。

4.死锁的诊断和解除

数据库利用数据依赖图的形式来测试系统是否存在死锁。

事务甲需要数据B，但数据B已经背事务乙封锁，从甲到乙画一个箭头；事务乙需要数据A，但数据A已经被事务甲封锁，那么从乙到甲也画一个箭头。如果在事务依赖图中沿着箭头方向存在一个循环，那么死锁的条件就形成了，系统就会出现死锁。

左图表示未进入死锁，右图表示进入死锁

                       

数据库中有一个死锁测试程序，每隔一段时间检查并发的事务之间是否发生死锁。如果发生死锁，那么只能选取某个事务作为牺牲品，把它撤销，做回退操作解除它的所有封锁，恢复到该事务的初始状态。释放出来的资源可以分配给其他事务，使其他事务可能继续运行下去，从而消除死锁现象。在消除死锁过程中，选取牺牲事务的标准是根据系统状态及其应用的实际情况来确定的，通常采用的方法之一是选择一个处理死锁代价最小的事务，将其撤销。不重要的用户，取消操作，释放封锁的数据，恢复对数据库所做的改变。