九、MySql锁和事务管理

并发控制

• 锁粒度：
    表级锁
    行级锁
• 锁：
    读锁：共享锁，只读不可写，多个读互不阻塞
    写锁：独占锁,排它锁，一个写锁会阻塞其它读和它锁
• 实现
    存储引擎：自行实现其锁策略和锁粒度
    服务器级：实现了锁，表级锁；用户可显式请求
• 分类：
    隐式锁：由存储引擎自动施加锁
    显式锁：用户手动请求

示例：

lock tables teachers read;   对teachers表加读锁
加读锁后，其他终端只能读该表，不能写
lock tables students write;   加写锁
加写锁后，自己可读可写，别人不可读不可写
注意：加锁时，要重启服务或清除缓存，否则加锁可能无效

启用缓存的情况下，可以通过修改变量，使缓存不生效
query_cache_wlock_invalidate  启用该项表示不允许从缓存中返回数据
此变量为会话级，要想全局生效则需要更改配置文件
set query_cache_wlock_invalidate=on
更改配置文件
vim /etc/my.cnf
[mysqld]
query_cache_wlock_invalidate=on

• 锁策略：在锁粒度及数据安全性寻求的平衡机制
• 显示使用锁
    LOCK TABLES
      tbl_name [[AS] alias] lock_type
      [, tbl_name [[AS] alias] lock_type] ...
      lock_type: READ ， WRITE
    UNLOCK TABLES 解锁
    FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
     关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁

  对服务器全部数据库加锁，即不仅对当前数据库生效，对其他数据库也生效
  还可以清除查询缓存信息,即flush tables
  其他表全部关闭后，才能使用此命令加锁
  flush tables with read lock;  对所有数据库的所有表加读锁(只能读不能写)，无需写表名
  unlock tabls  解锁

    SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
     查询时加写或读锁

事务

• 事务Transactions：一组原子性的SQL语句，或一个独立工作单元
• 事务日志：记录事务信息，实现undo,redo等故障恢复功能

  存放完整的事务操作和不完整的事务操作
  系统根据事务日志决定是否继续执行事务操作(事务动作已经全部写入事务日志)和撤销事务操作(事务动作没有完全写入事务日志)
  myisam存储引擎不支持事务日志，因此该存储引擎恢复能力差

• ACID特性：
    A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
    C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
  如：账户转账，转账人转走1万元，收账人收到1万元，两个动作要么都做，要么都不做，即两个动作要保持一致，确保数据保持一致
    I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
    D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
• 启动事务：
    BEGIN
    BEGIN WORK
    START TRANSACTION
• 结束事务：
    COMMIT：提交
    ROLLBACK: 回滚
    注意：只有事务型存储引擎中的DML语句方能支持此类操作
• 自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交
    建议：显式请求和提交事务，而不要使用“自动提交”功能
• 事务支持保存点：savepoint
    SAVEPOINT identifier
    ROLLBACK [WORK] TO [SAVEPOINT] identifier
    RELEASE SAVEPOINT identifier

示例：

update  students set  classid=5 where stuid=23;  对学生表进行更改
savepoint sp23   创建保存点
update  students set  classid=10 where stuid=24;  对学生表进行更改
savepoint sp24   创建保存点
update  students set  classid=30 where stuid=25;  对学生表进行更改

rollback to savepoint sp24;  回滚到sp24保存点，即stuid=25的操作会被撤销
rollback to savepoint sp23;  回滚到sp23保存点，即stuid=24的操作会被撤销

事务隔离级别

• 事务隔离级别：从上至下更加严格
    READ UNCOMMITTED 可读取到未提交数据，产生脏读

  两个会话：会话1和会话2
  会话1开启事务写入数据，会话2同样开启事务，在该事务内可以读取到会话1未提交的数据

    READ COMMITTED 可读取到提交数据，读取不到未提交数据。即在数据提交之前读取到的数据为写数据之前的旧数据，在数据提交之后读取到的数据为提交后更改的新数据，这样就读取到多个提交数据，导致每次读取数据不一致，也就是不可重复读

  两个会话：会话1和会话2
  会话1开启事务写入数据，会话2同样开启事务，在该事务内可以读取到会话1未提交前的旧数据
  会话1提交数据完成事务，会话2开启事务，在该事务内可以读取到会话1提交后的新数据

    REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，也只能读取到未修改前的旧数据，等其他事务提交完毕以后读取的数据也是提交前的旧数据。该选项为MySQL默认设置。

  两个会话：会话1和会话2
  会话1开启事务写入数据，会话2同样开启事务，在该事务内容读取到会话1未提交前的旧数据
  会话1提交数据完成事务，会话2开启事务，在该事务内读取到的还是会话1提交前的旧数据，当会话2提交数据完成事务后，才能看到会话1提交的新数据

    SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差

  两个会话：会话1和会话2
  会话1开启事务读取数据，会话2开启事务，在该事务内可以读取数据，但无法写入数据，只有等会话1完成事务，会话2才可以写入数据
  会话1开启事务写入数据，会话2开启事务，在该事务内无法读取数据，也无法写入数据，只有等会话1完成事务，会话2才可以读或写数据
  会话2会一直等待，直至会话超时

    会话超时的相关变量:

  MariaDB [hellodb]> show variables like '%timeout%';
  其中，innodb_lock_wait_timeout是指等待超时时间，默认为50秒，该变量可以手动更改超时时间

• MVCC: 多版本并发控制，和事务级别相关

• 指定事务隔离级别：
    服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置
    SET tx_isolation=''
      READ-UNCOMMITTED
      READ-COMMITTED
      REPEATABLE-READ
      SERIALIZABLE
    服务器选项中指定
      vim /etc/my.cnf
      [mysqld]
      transaction-isolation=SERIALIZABLE
    注意：服务器变量和服务器选项(即写入配置文件中的选项)并不一样，书写时要注意

并发控制

• 死锁：
    两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

  如果会话1开启事务对某行进行操作，会话2页对该行进行操作，那么操作会处于等待状态，直至等待超时退出操作
  如何强制会话2退出等待状态
  show processlist   查看事务列表，显示操作id号
  kill 2  关闭正在执行的事务，2为事务id号，用show processlist可以查看

• 事务日志：
    事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging
    日志文件： ib_logfile0， ib_logfile1
    建议：将事务日志存放在一个干净的，独立的分区中