MySQL的事务、锁机制和日志

目录

一：MySQL的事务

1.概述

2.基本操作

3.特性

二：MySQL的锁机制

1.概述

2.MyISAM引擎--表锁

3.InnoDB引擎--行锁

三：MySQL的日志

1.错误日志

2.binlog日志（二进制日志）

3.查询日志

4.慢日志查询

---

一：MySQL的事务

1.概述

*  在MySQL中的事务（Transaction）是由存储引擎实现的，在MySQL中，只有innodb存储引擎支持事务。

*  事务处理可以用来维护数据库的完整性，保证成批的sql语句要么全部执行，要么都不执行。

*  事务用来管理DDL、DML和DCL操作，比如insert，update，delete语句，默认是自动提交的。

举个例子：

在银行转账时，必须保证转账绝对安全，这时需要事务参与：

update account set money = money - 200 where id = 1;
update account set money = money + 200 where id = 2;

假如在第一次update之后，出现了意外，异常，没有执行第二次update,这时转账是否会出现异常？

2.基本操作

MySQL的事务操作主要有以下三种：

*  开启事务：Start Transaction 

任何一条DML语句（insert,update,delete）执行，标志事务的开启

命令：begin或start transaction

*  提交事务：Commit Transaction

成功的结束，将所有DML语句操作历史记录和底层硬盘数据来一次同步

命令：commit

*  回滚事务：Rollback Transaction

失败的结束，将所有DML语句操作记录全部清空

命令：rollback

        注意：之前所有的sql操作其实也有事务，只是MySQL自动帮我们完成，每执行一条SQL时MySQL就会帮助我们自动提交事务，因此如果想手动控制事务，则必须关闭MySQL的事务自动提交。在MySQL中直接用set 来改变MySQL的自动提交模式。

set autocommit = 0;  -- 禁止自动提交
set autocommit = 1;  -- 开启自动提交 

create database if not  exists mydb12_transcation;
use mydb12_transcation;
-- 创建账户表
create table account(
id int primary key, --账户id
name varchar(20),   -- 账户名name
money double        -- 金额
);

-- 插入数据
insert into account values(1,'zhangsan',1000);
insert into account values(2,'lisi',1000);

-- 设置MySQL的事务为手动提交（关闭自动提交）
select @@autocommit;
set autocommit=0;

-- 模拟账户转账
-- 开启事务
begin;
-- id为1的转账给id为2的200
update account set money = money - 200 where id = 1;
update account set money = money + 200 where id = 2;
-- 提交事务
commmit;

-- 回滚事务
rollback;

3.特性

原子性：事务是一个不可分割的整体，事务开始后的所有操作，要么全部完成，要么全部不做

一致性：系统从一个正确的状态，迁移到另一个正确的状态

隔离性：每个事务的对象对其他事务的操作对象互相分离，事务提交前对其他事务不可见 

持久性：事务一旦提交，则其结果是永久性的

二：MySQL的锁机制

1.概述

        从上述特点可见，很难笼统的说那种锁更好，只能就具体应用的特点来说哪种锁合适！仅从锁的角度来说：表锁级更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用。而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

2.MyISAM引擎--表锁

        MyISAM在执行查询语句（select）前，会自动的给涉及的所有表加读锁，在执行更新操作（update,delete,insert等）前，会自动的给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用lock table命令给MyISAM表显示加锁。

加读锁  lock table table_name read;
加写锁  lock table table_name write;

3.InnoDB引擎--行锁

InnoDB实现了以下两种类型的行锁

共享锁（s）：又被称为读锁，简称s锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁（x）：又被称为写锁，简称x锁，排他锁就是不能与其他锁共存，如一个事务获取了一个数据行的排他锁，其他事务就不能在获取该行的其他锁，但是获得排他锁的事务是可以对数据就行读取和修改。

可以通过以下语句显示给记录集加共享锁或排他锁

共享锁  select * from table_name where ... lock in share mode
排他锁  select * from table_name where ... for update

三：MySQL的日志

1.错误日志

        错误日志是MySQL中最重要的日志之一，它记录了当mysql启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现出现任何故障导致无法使用时，可以首先查看此日志。

该日志时默认开启的，默认存放目录为mysql的数据目录，默认的日志文件名为hostname.err(hostname是主机名)

查看日志位置指令：

show variables like 'log_error%';

2.binlog日志（二进制日志）

相关操作：

-- 查看MySQL是否开启了binlog日志
show variables like 'log_bin'; 

-- 查看bnlog日志的格式
show variables like 'binlog_format';

-- 查看所有日志
show binlog events;

-- 查看最新日志
show master status;

-- 查看指定的binlog日志
show binlog events in 'binlog.000010'; 

-- 清空所有的binlog日志文件
reset master;

3.查询日志

查询日志记录了客户端所有的操作语句，而二进制日志不包含查询语句的sql语句

默认情况下，查询日志是未开启的

相关操作：

-- 查看MySQL是否开启了查询日志
show variables like 'general_log';

-- 开启日志查询
set global general_log=1;

4.慢日志查询

相关操作：

-- 查看慢查询日志是否开启
show variables like 'slow_query_log%';

-- 开启慢查询日志
set global slow_query_log=1;

-- 查看慢查询的超时时间
show variables like 'long_query_time%';