10-事务、事件、锁

1、事务

• 特性：

  • 原子性。

    事务是一个整体，不可分割，一步没完成，则整体不算完成。
    

  • 一致性。

    银行转账：如果扣了张三的钱，必须给李四加钱；同时，扣张三1w，不能给李四加5k。
    

  • 隔离性。

    在事务最终完成之前，谁也看不到之间的状态变化，直到事务完成为止。
    

  • 持久性。

    事务完成之后不可撤销。
    

• 不在同一台服务器上的事务，称之为分布式事务，简称XA。

• innodb支持事务，myisam不支持事务。事务必须提交才算完成，在innodb中默认开启自动提交。每执行一个命令就是一个完整的事务，命令执行完毕意味着事务执行完毕。

  • 查看自动提交参数：

  mysql> show variables like '%autocommit%';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | autocommit    | ON    |
  +---------------+-------+
  1 row in set, 1 warning (0.14 sec)
  

  • 如果不设置自动提交{set autocommit=0}，开启事务操作，具体如下：

  mysql> begin;      //开启事务。或者使用{start transaction;}
  mysql> sql语句；
  mysql> commit;
  

  • commit之前，回滚操作：

  mysql> begin;
  mysql> savepoint x1;    //设置保存点x1
  mysql> sql语句1;
  mysql> savepoint x2;    //设置保存点x2
  mysql> sql语句2;
  mysql> rollback to x1;  //回滚到X1
  注：回滚到x1后不能回滚到x2
  

2、事件

• 理解：mysql的计划任务

• 查看状态：

  mysql> show variables like '%event_scheduler%';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | event_scheduler | ON    |
  +-----------------+-------+
  1 row in set, 1 warning (0.00 sec)
  

• 创建事件语法：

  create event 事件名 on schedule
  时间间隔
  starts '时间'
  ends '时间'
  do
  sql语句;
  

• 查看event

  # 查看有哪些event
  mysql> show events from 数据库名\G
  
  # 查看创建event的具体信息
  mysql> show create event 事件名\G
  
  # 删除event
  mysql> drop event 事件名;
  
  # 禁用/启用event
  mysql> alter event 事件名 enable|disable;
  

• 示例：

  # 需求1：每隔10分钟进行1次对aa表进行表分析
  mysql> create event x1 on schedule
  mysql> every 10 minute
  mysql> do
  mysql> analyze table aa;
  
  # 需求2：从2013年7月2日12点开始执行，一直执行到2013年12月31日，对aa表进行表分析
  mysql> drop event if exists x2;
  mysql> create event x2 on schedule
  mysql> every 1 day
  mysql> starts '2013-07-02 12:00:00'
  mysql> ends '2013-12-21 00:00:00'
  mysql> do
  mysql> analyze table aa;
  
  # 需求3：10分钟之后，每隔10秒执行一次
  mysql> drop event if exists x3;
  mysql> create event x3 on schedule
  mysql> every 10 second
  mysql> starts current_timestamp +interval 10 minute
  mysql> do
  mysql> analyze table aa;
  
  # 需求4：每隔10分钟执行一次，10天之后结束
  mysql> drop event if exists x4;
  mysql> create event x4 on schedule
  mysql> every 10 minute
  mysql> ends now() +interval 10 day
  mysql> do
  mysql> analyze table aa;
  

3、锁

• myisam仅支持表级锁，innodb支持表级锁和行级锁，表级锁解锁使用{unlock tables;}，行级锁提交事务自动解锁。

• myisam锁

  • 共享读锁：
    • 允许事务和其他事务对表的读请求，阻止事务和其他事务对表的写请求；用户中其他表，阻止事务读请求，允许其他事务读请求
  • 共享写锁：
    • 阻止其他事务对同一表的读写

• innodb锁

  • 表若无索引，使用表级锁。
    • 示例：无索引情况下，给一行添加排他锁，其他行均不能加排他锁
  • 共享锁：
    • 允许事务去读一行，阻止其他事务获取该行的排他锁
    • 一般理解：能读，不能写
  • 排他锁：
    • 允许持有排他锁的事务读写数据，阻止其他事务获取该资源的任何锁。
    • 不能获取任何锁，不代表不能读

• 相关sql：

  # 查看哪些表加了锁
  show open tables where in_use>0;
  # 表级锁加共享读锁
  lock table 表名 read;
  # 表级锁加共享写锁
  lock table 表名 write;
  #### 3.2 innodb(行级锁)
  # 表级锁解锁
  unlock tables;
  # innodb共享锁
  select ... where ... lock in share mode;
  select ... where ... for share;   //mysql8.0以上
  # innodb排他锁
  update ...
  delete ...
  insert ...
  select ... where ... for update;