金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > 不可不知的Metadata,触发器详解

不可不知的Metadata,触发器详解

发布时间:2019-11-06 12:29编辑:数据库浏览(170)

    金沙国际官网, 

    在线上进行DDL操作时,相对于其可能带来的系统负载,其实,我们最担心的还是MDL其可能导致的阻塞问题。

    SQL Server:触发器详解

    Preface

    一旦DDL操作因获取不到MDL被阻塞,后续其它针对该表的其它操作都会被阻塞。典型如下,如阻塞稍久的话,我们会看到Threads_running飙升,CPU告警。

     

     

    mysql> show processlist;
    +----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
    | Id | User            | Host      | db        | Command | Time | State                           | Info                               |
    +----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
    |  4 | event_scheduler | localhost | NULL      | Daemon  |  122 | Waiting on empty queue          | NULL                               |
    |  9 | root            | localhost | NULL      | Sleep   |   57 |                                 | NULL                               |
    | 12 | root            | localhost | employees | Query   |   40 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
    | 13 | root            | localhost | employees | Query   |   35 | Waiting for table metadata lock | select * from slowtech.t1          |
    | 14 | root            | localhost | employees | Query   |   30 | Waiting for table metadata lock | select * from slowtech.t1          |
    | 15 | root            | localhost | employees | Query   |   19 | Waiting for table metadata lock | select * from slowtech.t1          |
    | 16 | root            | localhost | employees | Query   |   10 | Waiting for table metadata lock | select * from slowtech.t1          |
    | 17 | root            | localhost | employees | Query   |    0 | starting                        | show processlist                   |
    +----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
    8 rows in set (0.00 sec)
    
    • 1. 概述
    • 2. 触发器的分类
    • 3. Inserted和Deleted表
    • 4. 触发器的执行过程
    • 5. 创建触发器
    • 6. 修改触发器:
    • 7. 删除触发器:
    • 8. 查看数据库中已有触发器:
    • 9. “Instead of”相关示例:
    • 10. “After”触发器
    • 11. 参考资源

        As we all know,it's a common sense that separate reading and writing operations can immensely increse the performance of MySQL database.Especially the query operations by executing select statement relevant with large tables.Therefore,we usually choose a proxy tool to deal with it.There're a lot of tools can be used nowadays such as mycat(by Apache),dble(based on mycat by Action),atlas,dbproxy(based on atlas of Qihoo360 by MeituanDianping),cetus(by NetEase) and so forth.I'm not going to compare who's the better tool to use.I'm just prefer to having a test on another popular tool which is called "ProxySQL".

    如果发生在线上,无疑会影响到业务。所以,一般建议将DDL操作放到业务低峰期做,其实有两方面的考虑,1. 避免对系统负载产生较大影响。2. 减少DDL被阻塞的概率。

     

     

    1. 概述

    触发器是一种特殊的存储过程,它不能被显式地调用,而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。 所以触发器可以用来实现对表实施复杂的完整性约束。

    Introduce

    MDL引入的背景

    2. 触发器的分类

    SQL Server2000提供了两种触发器:“Instead of” 和“After” 触发器。

    一个表或视图的每一个修改动作(Insert、Update和Delete)都可以有一个“Instead of” 触发器,一个表的每个修改动作都可以有多个“After”触发器。

     

    MDL是MySQL 5.5.3引入的,主要用于解决两个问题,

    2.1 “Instead of”触发器

    • “Instead of”触发器在执行真正“插入”之前被执行。除表之外,“Instead of” 触发器也可以用于视图,用来扩展视图可以支持的更新操作。
    • “Instead of”触发器会替代所要执行的SQL语句,言下之意就是所要执行SQL并不会“真正执行”

    alter trigger trigger_学生_Delete

    on 学生

    instead of Delete

    as

    begin

    ``select 学号, 姓名 ``from deleted

    end

     

    delete from 学生 ``where 学号 = 4

    上例中定义了“trigger学生_Delete”触发器,该触发器从“delete”表中打印出所要删除的学生.在执行“delete”操作后,会发现“学号 = 4”的学生并未被删除, 原因在于“trigger学生Delete”替代了所要执行的“delete from 学生 where 学号 = 4”语句,而在“trigger学生_Delete”中并未真正删除学生。

        ProxySQL is a low-weight proxy tool based on a SQLite database.It provids hight performance espcially in high concurrent environment what we can see below(compared with the MaxScale).

     

    2.2 “After”触发器

    • “After”触发器在Insert、Update或Deleted语句执行之后被触发。“After”触发器只能用于表。
    • “After”触发器主要用于表在修改后(insert、update或delete操作之后),来修改其他表

     

    RR事务隔离级别下不可重复读的问题

    3. Inserted和Deleted表

    SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。

    • 这两个表由系统来维护,它们存在于内存中而不是在数据库中,可以理解为一个虚拟的表。
    • 这两个表的结构总是与被该触发器作用的表的结构相同。
    • 触发器执行完成后,与该触发器相关的这两个表也被删除。
    • Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
    • Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。
    对表的操作 Inserted逻辑表 Deleted逻辑表
    增加记录(insert) 存放增加的记录
    删除记录(delete) 存放被删除的记录
    修改记录(update) 存放更新后的记录 存放更新前的记录

    金沙国际官网 1

    如下所示,演示环境,MySQL 5.5.0。

    4. 触发器的执行过程

    • 如果一个Insert﹑update或者delete语句违反了约束,那么这条SQL语句就没有执行成功,因此“After”触发器也不会被激活。

    • “Instead of” 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立,其它任何操作还没有发生时被执行。因为“Instead of” 触发器在约束之前执行,所以它可以对约束进行一些预处理。

     

    session1> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    session1> select * from t1;
    +------+------+
    | id  | name |
    +------+------+
    |    1 | a    |
    |    2 | b    |
    +------+------+
    2 rows in set (0.00 sec)
    
    session2> alter table t1 add c1 int;
    Query OK, 2 rows affected (0.02 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    session1> select * from t1;
    Empty set (0.00 sec)
    
    session1> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    session1> select * from t1;
    +------+------+------+
    | id  | name | c1  |
    +------+------+------+
    |    1 | a    | NULL |
    |    2 | b    | NULL |
    +------+------+------+
    2 rows in set (0.00 sec)
    

    5. 创建触发器

    create trigger trigger_name

    on  {table_name|view_name}

    {``After``|``Instead of``} {``insert``|``update``|``delete``}

    as 相应T-SQL语句

        

    可以看到,虽然是RR隔离级别,但在开启事务的情况下,第二次查询却没有结果。

    6. 修改触发器:

    alter trigger trigger_name

    on  {table_name|view_name}

    {``After``|``Instead of``} {``insert``|``update``|``delete``}

    as 相应T-SQL语句

    *    The configuration of ProxySQL is a three-layer structure:*

     

    7. 删除触发器:

    drop trigger trigger_name

     

    主从复制问题

    8. 查看数据库中已有触发器:

    金沙国际官网 2

    包括主从数据不一致,主从复制中断等。
    如下面的主从数据不一致。

    8.1 查看数据库中所有触发器

    select * ``from sysobjects ``where xtype=``'TR'

     

    session1> create table t1(id int,name varchar(10)) engine=innodb;
    Query OK, 0 rows affected (0.00 sec)
    
    session1> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    session1> insert into t1 values(1,'a');
    Query OK, 1 row affected (0.00 sec)
    
    session2> truncate table t1;
    Query OK, 0 rows affected (0.46 sec)
    
    session1> commit;
    Query OK, 0 rows affected (0.35 sec)
    
    session1> select * from t1;
    Empty set (0.00 sec)
    

    8.2 查看单个触发器

    exec sp_helptext ``'触发器名'

      *  We usually conifuge the parameter in layer of memory,and then load them into layer of runtime to make it take effect.In the end,we should save them to disk for durability storage.It also provides some simple syntax to transfer configurations between those layers as below:*

     

    9. “Instead of”相关示例:

    两张表:学生(学号 int, 姓名 varchar)、借书记录(学号 int, 图书编号 int)

    实现功能:在删除学生表时,如果该学生仍有借书记录(未还)则不能删除

    alter trigger trigger_学生_Delete

    on 学生

    instead of Delete

    as

    begin

    ``if ``not exists(``select * ``from 借书记录, deleted ``where 借书记录.学号 = deleted.学号)

    ``delete from 学生 ``where 学生.学号 ``in (``select 学号 ``from deleted)

    end

     

    再来看看从库的结果

    10. “After”触发器

    • LOAD MYSQL object FROM MEMORY or LOAD MYSQL object TO RUNTIME 
    • SAVE MYSQL object TO MEMORY or SAVE MYSQL object FROM RUNTIME 
    • LOAD MYSQL object TO MEMORY or LOAD MYSQL object FROM DISK 
    • SAVE MYSQL object FROM MEMORY or SAVE MYSQL object TO DISK 
    • LOAD MYSQL object FROM CONFIG
    session1> select * from slowtech.t1;
    +------+------+------+
    | id   | name | c1   |
    +------+------+------+
    |    1 | a    | NULL |
    +------+------+------+
    1 row in set (0.00 sec)
    

    10.1 在“订单”表中建立触发器,当向“订单”表中插入一条订单记录时,检查“商品”表的货品状态“状态”是否为1(正在整理),则不能往“订单”表加入该订单。

    create trigger trigger_订单_insert

    on 订单

    after insert

    as

    ``if (``select 状态 ``from 商品, inserted ``where 商品.pid = inserted.pid)=1

    ``begin

    ``print ``'the goods is being processed'

    ``print ``'the order cannot be committed'

    ``rollback transaction --回滚,避免加入

    ``end

    • 该示例中“pid”为商品编码
    • 该示例的if判断严格来讲是不准确的,因为“订单”表如果每次插入一条记录,该判断没有问题;如果一次插入多条记录,则“select 状态”返回的是多行。

     

     

    10.2 在“订单”表建立一个插入触发器,在添加一条订单时,减少“商品”表相应的货品记录中的库存。

    create trigger trigger_订单_insert2

    on 订单

    after insert

    as

    ``update 商品 ``set 数量 = 数量 - inserted.数量

    ``from 商品, inserted

    ``where 商品.pid = inserted.pid

    The comparison with other popular middleware tools.

    看看binlog的内容,可以看到,truncate操作记录在前,insert操作记录在后。

    10.3 在“商品”表建立删除触发器,实现“商品”表和“订单”表的级联删除。

    create trigger goodsdelete trigger_商品_delete

    on 商品

    after delete

    as

    ``delete from 订单 ``where 订单.pid ``in (``select pid ``from deleted)

    # at 7140
    #180714 19:32:14 server id 1  end_log_pos 7261    Query    thread_id=31    exec_time=0    error_code=0
    SET TIMESTAMP=1531567934/*!*/;
    create table t1(id int,name varchar(10)) engine=innodb
    /*!*/;
    
    # at 7261
    #180714 19:32:30 server id 1  end_log_pos 7333    Query    thread_id=32    exec_time=0    error_code=0
    SET TIMESTAMP=1531567950/*!*/;
    BEGIN
    /*!*/;
    # at 7333
    #180714 19:32:30 server id 1  end_log_pos 7417    Query    thread_id=32    exec_time=0    error_code=0
    SET TIMESTAMP=1531567950/*!*/;
    truncate table t1
    /*!*/;
    # at 7417
    #180714 19:32:30 server id 1  end_log_pos 7444    Xid = 422
    COMMIT/*!*/;
    
    # at 7444
    #180714 19:32:34 server id 1  end_log_pos 7516    Query    thread_id=31    exec_time=0    error_code=0
    SET TIMESTAMP=1531567954/*!*/;
    BEGIN
    /*!*/;
    # at 7516
    #180714 19:32:24 server id 1  end_log_pos 7611    Query    thread_id=31    exec_time=0    error_code=0
    SET TIMESTAMP=1531567944/*!*/;
    insert into t1 values(1,'a')
    /*!*/;
    # at 7611
    #180714 19:32:34 server id 1  end_log_pos 7638    Xid = 421
    COMMIT/*!*/;
    

    10.4 在“订单”表建立一个更新触发器,监视“订单”表的“订单日期”列,使其不能被“update”.

    create trigger trigger_订单_update

    on 订单

    after update

    as

    ``if ``update``(订单日期)

    ``begin

    ``raiserror(``'订单日期不能手动修改'``,10,1)

    ``rollback transaction

    ``end

    本文由金沙国际官网发布于数据库,转载请注明出处:不可不知的Metadata,触发器详解

    关键词: