数据库进阶路线图(二)

78 阅读13分钟

4.数据库事务

数据库事务概念:

    mysql数据库一切操作的合集,该合集中的所有操作,要么全部成功要么全部失败,不可能存在部分成功或者失败.
    基于数据库事务的概念,不难猜出他有其他四个特点:
         1. 原子性(Atomicity)
         2. 一致性(Consistency)
         3. 隔离性(Isolation)
         4. 持久性(Durability)
    这就是我们通常说的ACID
 什么是原子性:将事务看作是一个完整的操作。事务的各元素是不可分的(原子的)。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败,则整个事务将失败;
 

事务隔离级别:

为了达到事务的四大特性,数据库定义了4种不同的事务隔离级别,由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable,这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

级别名字隔离级别脏读不可重复读幻读数据库默认隔离级别
1读未提交read uncommitted
2读已提交read committedOracle
3可重复读repeatable readMySQL
4串行化serializable

注意: 这里需要注意的是:Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别
事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC(多版本并发控制),通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
因为隔离级别越低,事务请求的锁越少,所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容),但是你要知道的是InnoDB 存储引擎默认使用REPEATABLE-READ(可重读) 并不会有任何性能损失。

设置事务隔离级别:

--- 设置事务隔离级别
--- 语法: set session transaction isolation level  隔离级别;
--- 实例: 设置事务隔离级别为:read uncommitted,read committed,repeatable read,serializable
--- 设置读未提交
set session transaction isolation level read uncommitted;

---查询事务隔离级别
select @@tx_isolation;

事务开启

方式一: start transaction;开启事务 > commit;提交 / rollback;回滚

-- 没有异常
start transaction; -- 开启事务
update accounts set money = money - 100 where name = 'zs'; -- zs  money-100
-- 没有异常
update accounts set money = money + 100 where name = 'ls'; -- ls  money+100
commit; -- 提交事务
 
-- 有异常
start transaction; -- 开启事务
update accounts set money = money - 100 where name = 'zs'; -- zs  money-100
-- 有异常
update accounts set money = money + 100 where name = 'ls'; -- ls  money+100
rollback; -- 回滚事务

方式二: 设置MYSQL中的自动提交的参数

---查看MYSQL中事务是否自动提交
show variables like '%commit%';

---设置自动提交的参数为OFF (0:OFF 1:ON)
set autocommit = 0;

回滚点

开启事务后 可以设置回滚点 指定回滚到回滚点操作之前;

start transaction;
update accounts set money = money - 100 where name = 'zs'; -- zs money-100
update accounts set money = money - 100 where name = 'zs'; -- zs money-100
update accounts set money = money - 100 where name = 'zs'; -- zs money-100
update accounts set money = money - 100 where name = 'zs'; -- zs money-100
update accounts set money = money - 100 where name = 'zs'; -- zs money-100
-- 以上sql语句没有问题
savepoint rollpoint;
update accounts set money = money - 100 where name = 'zs'; -- zs money-100
update accounts set money = money - 100 where name = 'zs'; -- zs money-100
-- 出现异常,回滚到rollpoint位置
rollback to rollpoint;
update account set money = money - 100 where name = 'zs'; -- zs money-100
update account set money = money - 100 where name = 'zs'; -- zs money-100
update account set money = money - 100 where name = 'zs'; -- zs money-100
commit;

5.数据库锁

数据库锁的种类一般分为两种:一种是悲观锁,一种乐观锁。

悲观锁

悲观锁(Pessimistic Lock)具有强烈的独占和排他特性,它指的是对数据被外界修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制。传统的关系数据库里用到了很多这种锁机制,比如行锁、表锁、读锁、写锁等,都是在操作之前先上锁。

悲观锁的隔离级别可以看做可重复读。

悲观锁按使用性质分类
共享锁(读锁、S锁)
事务A对对象T加共享锁,其他事务也只能对T加共享锁,多个事务可以同时读,但不能有写操作,直到A释放共享锁。
特点: 多个事务可封锁同一个共享页;任何事务都不能修改该页;该页被读取完毕,共享锁立即被释放。

互斥锁(排它锁、独占锁、写锁、X锁) 事务A对对象T加互斥锁以后,其他事务不能对T加任何锁(即其他事物进入阻塞状态),只有事务A可以读写对象T直到A释放互斥锁。
特点: 仅允许一个事务封锁此页;其他任何事务必须等到互斥锁被释放才能对该页进行访问;互斥锁一直到事务结束才能被释放。

更新锁(U锁)
更新锁用来预定要对此对象施加互斥锁,它允许其他事务读,但不允许再施加更新锁或互斥锁,当被读取的对象将要被更新时,则升级为互斥锁。
更新锁主要是用来防止死锁的,因为使用共享锁时,修改数据的操作分为两步,首先获得一个共享锁,读取数据,然后将共享锁升级为互斥锁,然后再执行修改操作。这样如果同时有两个或多个事务同时对一个对象申请了共享锁,在修改数据的时候,这些事务都要将共享锁升级为互斥锁。这些事务都不会释放共享锁而是一直等待对方释放,这样就造成了死锁。如果一个数据在修改前直接申请为更新锁,在数据修改的时候再升级为互斥锁,就可以避免死锁。
特点: 用来预定要对此页施加互斥锁,它允许其他事务读,但不允许再施加更新锁或互斥锁;当被读取的页要被更新时,则升级为互斥锁。更新锁一直到事务结束时才能被释放。

自旋锁 自旋锁和互斥锁很像,一次只能有一个进程进入临界区,唯一不同的是自旋锁访问加锁资源时,会一直循环的查看是否释放锁。这样要比互斥锁效率高很多,但是仍然需要占用CPU。

特点:自旋锁需要设定一个自旋等待的最大时间,如果持有锁的线程执行的时间超过自旋等待的最大时间扔没有释放锁,就会导致其它争用锁的线程在最大等待时间内还是获取不到锁,这时争用线程会停止自旋进入阻塞状态。除此之外,当自旋锁递归调用的时候会造成死锁现象,所以慎重使用自旋锁。

悲观锁按作用范围分类(按锁的粒度分类) 数据库能够确定那些行需要锁的情况下使用行锁,如果不知道会影响哪些行的时候就会使用表锁。

举个例子,一个用户表user,有主键id和用户生日birthday。当你使用update … where id=?这样的语句时,数据库明确知道会影响哪一行,它就会使用行锁;当你使用update … where birthday=?这样的的语句时,因为事先不知道会影响哪些行就可能会使用表锁。

行锁 行锁的作用范围是行级别,数据库能够确定那些行需要锁的情况下使用行锁,如果不知道会影响哪些行的时候就会使用表锁。举个例子,一个用户表user,有主键id和用户生日birthday当你使用update … where id=?这样的语句数据库明确知道会影响哪一行,它就会使用行锁,当你使用update … where birthday=?这样的的语句的时候因为事先不知道会影响哪些行就可能会使用表锁。

表锁 表锁的作用范围是整张表。

乐观锁

相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制,大多是基于版本号( Version )记录机制实现,而不需要借助数据库的锁机制。

乐观锁的实现
乐观锁的本质不是锁,其隔离级别可以看作为读未提交。乐观锁可以通过以下几种方式实现:

版本号
给数据增加一个版本标识,在数据库上就是表中增加一个version字段,每次更新把这个字段加1。读取数据的时候把version读出来,更新的时候比较一开始读取的version和现在的version,如果还是开始读取的version就可以更新了,如果现在的version比老的version大,说明有其他事务更新了该数据,并增加了版本号,这时候得到一个无法更新的通知,用户自行根据这个通知来决定怎么处理,比如重新开始一遍。

基于版本号实现乐观锁的关键是判断version和更新两个动作需要作为一个原子单元执行,否则在你判断可以更新以后正式更新之前有别的事务修改了version,这个时候你再去更新就可能会覆盖前一个事务做的更新。所以你可以使用update … where … and version=”old version”这样的语句,根据返回结果是0还是非0来得到通知,如果是0说明更新没有成功,因为version被改了,如果返回非0说明更新成功。

待更新字段
基于待更新字段实现乐观锁的方式和版本号方式相似,只是不增加额外字段,直接使用有效数据字段做版本控制信息,因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值(即开始读的值),如果是就把我修改的count的值更新到该字段,否则更新失败。

悲观锁的优缺点
悲观锁的优点是能避免冲突的发生。

悲观锁的缺点是开销较大,而且加锁时间较长,对于并发的访问性支持不好。

乐观锁的优缺点
乐观锁的优点是避免了长事务中的数据库加锁解锁开销,大大提升了大并发量下的系统整体性能表现。

乐观锁的缺点是只能在提交数据时才发现业务事务将要失败,如果系统的冲突非常的多,而且一旦冲突就要因为重新计算提交而造成较大的代价的话,乐观锁也会带来很大的问题。

乐观锁与悲观锁的选择
乐观锁适用于写比较少的情况下(多读场景),即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。

悲观锁适用于多写的场景,这种情况下一般会经常产生冲突。

行锁的实现方式
InnoDB 行锁是通过给索引上的索引项加锁来实现的。这种行锁意味着只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。

6.数据库日志

MySQL的日志默认保存在数据库文件的存储目录(一般为/usr/local/mysql/data/)。也可以修改配置文件,自定义日志文件的保存位置。

一、四种日志介绍

1.1 错误日志 错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启。

vim /etc/my.cnf
​ [mysqld]
log-error=/usr/local/mysql/data/mysql/mysql_error.log #指定日志的保存位置

1.2 通用查询日志 通用查询日志,用来记录MySQL的所有连接和语句,默认是关闭的。

vim /etc/my.cnf
 ​ [mysqld]
 general_log=ON
 general_log_file=/usr/local/mysql/data/mysql/mysql_general.log

1.3 二进制日志 二进制日志(binlog),用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启。


vim /etc/my.cnf
  ​ [mysqld]
 log-bin=mysql-bin      #也可以log_bin=mysql-bin
 #使用相对路径,则文件存储在默认目录/usr/local/mysql/data/中

1.4 慢查询日志 慢查询日志,用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便于优化,默认是关闭的。

 vim /etc/my.cnf
 ​ [mysqld]
 slow_query_log=ON
 slow_query_log_file=/usr/local/mysql/data/mysql/mysql_slow_query.log 
 long_query_time=5    #慢查询时间,设置超过5秒执行的语句被记录,缺省时为10秒

二、配置四种日志:

步骤一:修改配置文件/etc/my.cnf

vim /etc/my.cnf

 [mysqld]
 ##错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启
 log-error=/usr/local/mysql/data/mysql/mysql_error.log

 ##通用查询日志,用来记录MySQL的所有连接和语句,默认是关闭的
 general_log=ON
 general_log_file=/usr/local/mysql/data/mysql/mysql_general.log

 ##二进制日志(binlog),用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启
 log-bin=mysql-bin      #也可以log_bin=mysql-bin
 #使用相对路径,则文件存储在默认目录/usr/local/mysql/data/mysql/中

 ##慢查询日志,用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便于优化,默认是关闭的
 slow_query_log=ON
 slow_query_log_file=/usr/local/mysql/data/mysql/mysql_slow_query.log 
 long_query_time=5    #慢查询时间,设置超过5秒执行的语句被记录,缺省时为10秒

重启mysql

systemctl stop mysqld  #停止mysql服务
systemctl start mysqld #启动mysql服务
service mysqld restart #重启mysql服务

三、查询日志是否已开启


mysql -u root -p[密码]

 #查看错误日志存放位置
 show variables like 'log_error';    

 #查看通用查询日志是否开启
 show variables like 'general%'; 

 #查看二进制日志是否开启
 show variables like 'log_bin%';     

 #查看慢查询日功能是否开启
 show variables like '%slow%';   

 #查看慢查询时间设置
 show variables like 'long_query_time';                      

 #在数据库中设置开启慢查询的方法,即以修改变量值的方式开启。但重启服务后会失效。
 set global slow_query_log=ON;

四、分割二进制日志

mysql-bin.index 文件会对二进制日志进行分割。
每次重启 mysql 或 在数据库中 "flush logs"刷新日志之后,会生成一个新的二进制日志。

mysql> flush logs #手动分割二进制文件
 service mysqld restart #重启mysql服务 可自动分割二进制文件