MySQL学习（事务、锁机制、日志）

事务（Transaction）

• 可以用来维护数据库的完整性，要么全部执行，要么全部不执行。
• 事务管理DDL、DML、DCL语句，增删改，默认自动提交。

1. 开启事务：Start Transaction
   insert、update、delete）执行时，标志事务开启
   语句：begin或者start transaction;
2. 提交事务：Commit Transaction
   成功的结束，将DML语句操作历史记录和底层硬盘数据同步
   语句：commit;
3. 回滚事务：Rollback Transaction
   失败的结束，将DML语句操作历史记录清空
   语句：rollback;

• 准备数据

        create database if not exists mydb_transaction;
        use mydb_transaction;
        create table if not exists account(
            id int primary key,
            name varchar(20),
            money double
        );
        insert into account values(1, '张三', 1000);
        insert into account values(2, '李四', 1000);

• 查看事务是否自动提交

select @@autocommit;

• 关闭自动提交事务

set autocommit=0;

• 开启事务

 start transaction;  -- 或者 begin;
 update account set money=money - 200 where id=1;
 update account set money=money + 200 where id=2;

• 提交事务

commit;

• 回滚事务

rollback;

事务的特性

• 原子性：事务不可分割，要么全部成功，要么全部失败。
• 一致性：事务前后的数据完整性保持一致。
• 隔离性：事务与事务之间是相互独立的，互不干扰。
  隔离级别（越向下关联性越小）：
  1. read uncommitted：未提交读，最低隔离级别，一个事务可以读取另一个未提交的事务，会造成脏读。
  2. read committed：已提交读，Oracle默认隔离级别，一个事务要等另一个事务提交后才能读取数据，会造成不可重复读。
  3. repeatable read：可重复读，MySQL默认隔离级别，开始读取数据（事务开启）时，禁止其他事务对数据更新，会造成幻读。
  4. serializable：序列化，最高隔离级别，事务串行执行，禁止其他事务并发操作数据，会造成长时间锁表。
• 持久性：事务一旦提交，数据将永久保存。

1. 查看隔离级别

show variables like 'transaction_isolation';

2. 设置隔离级别

 --  设置read uncommitted
 set session transaction isolation level read uncommitted;
     -- 脏读：a事务读到b事务未提交的数据
 -- 设置read committed
 set session transaction isolation level read committed;
     -- 不可重复读：a事务提交之前读到b事务的数据是不同的
 -- 设置repeatable read
 set session transaction isolation level repeatable read;
     -- 幻读：a事务提交前后读取数据不同
 -- 设置serializable
 set session transaction isolation level serializable;
     -- 较安全但效率低，a事务操作表时，其他事务不能操作该表

锁机制

• 计算机协调多个进程或线程并发访问某一资源的机制。
• 按锁的粒度分为：表锁和行锁。
• 按锁的类别分为：共享锁和排他锁。
• 共享锁：读锁，多个事务可同时读取同一资源不会相互影响，但只能读不能写。
• 排它锁：写锁，一个事务对资源进行更新操作时，其他事务不能对该资源进行更新操作。

表锁

• 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。MyISAM、InnoDB都支持表锁。
• 在MySQL的查询语句select前，会自动给涉及的所有表加读锁，在MySQL的更新语句update、delete、insert前，会自动给涉及的所有表加写锁，在事务提交前，自动释放事务占用的所有锁。
• 表加读锁

lock tables emp read;    -- 给emp表加读锁
    select * from emp;  -- 所有用户都可以读取加读锁的表
    select * from dept; -- 当给一张表加读锁后，不能读取其他表
    update emp set ename='张三' where empno=7369;  -- 不能更新加读锁的表
unlock tables;  -- 解锁

• 表加写锁

lock tables emp write;  -- 给emp表加写锁，其他用户不能读取也不能更新，解锁之前其他用户也不能再加写锁
    select * from emp;  -- 只有当前用户可以读取加写锁的表
    update emp set ename='张三' where empno=7369;  -- 只有当前用户可以更新加写锁的表
unlock tables;  -- 解锁

行锁(InnoDB)

• 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。只有InnoDB支持行锁。
• 对于update、delete、insert语句，InnoDB会自动给涉及数据加行排他锁；对于select语句，InnoDB不会加任何锁。

set autocommit=0;  -- 关闭自动提交事务
start transaction;  -- 开启事务
update emp set ename='张三' where empno=7369;  -- 更新数据,自动给该行加行锁
-- 在提交事务之前，其他用户不能更新该行数据
commit;  -- 提交事务

日志

• 记录MySQL运行期间的一些状态和错误信息

错误日志

• 记录启动、停止MySQL时出现的问题，重启MySQL时清空。

-- 查看错误日志路径
    show variables like 'log_error%';
-- 开启错误日志
    set global log_error=on;
-- 关闭错误日志
    set global log_error=off;

二进制日志

• 记录所有更改数据的语句，不包括查询语句。

-- 日志格式
    -- statement：基于SQL语句的日志，记录的是SQL语句，每一条对数据进行修改的SQL语句都会记录在日志中，如insert、update、delete等。
    -- row：基于行的日志，记录的是每一条数据的修改内容，不记录SQL语句。
    -- mixed：混合模式，statement和row的结合。

  -- 查看二进制日志是否开启
      show variables like 'log_bin%';
  -- 查看二进制日志格式
      show variables like 'binlog_format';
  -- 开启二进制日志
      set global log_bin=on;
  -- 关闭二进制日志
      set global log_bin=off;
  -- 查看所有二进制日志
      show binlog events;
  -- 查询最新二进制日志
      show master status;
  -- 查询二进制日志文件列表
      show master logs;
  -- 查看二进制日志文件内容
      show binlog events in 'DESKTOP-I4G6UVE-bin.000001';

查询日志

• 记录客户端的所有操作语句。

 -- 查看查询日志是否开启
     show variables like 'general_log';
 -- 开启查询日志
     set global general_log=on;
 -- 关闭查询日志
     set global general_log=off;
 -- 查看查询日志路径
     show variables like 'general_log_file';

慢查询日志

• 记录所有执行时间超过指定时间long_query_time值，并且扫描记录不小于min_examined_row_limit的SQL语句。

  -- 查看慢查询日志是否开启
      show variables like 'slow_query_log%';
  -- 开启慢查询日志
      set global slow_query_log=on;
  -- 查看慢查询的超时时间
      show variables like 'long_query_time%';