mysql学习--入门（三）

Mysql的锁

读写锁

在msyql中，处理并发读或者并发写的时候，采用了两种锁来解决并发问题。这两种锁就是读锁和写锁，也分别称为共享锁和排他锁。

• 读锁：多个用户在同一时刻访问同一个资源，彼此之间互不干扰
• 写锁：一个写锁会阻塞其它的写锁和读锁，即阻塞别人的访问。一个用户在写入的时候，防止其它用户读取或者写入。

锁粒度

给不同大小的资源上锁，就是控制锁的粒度。锁的资源越少或越小，则并发程度越高，需要的锁也越多。但同时系统开销也会增大。
大多数商业数据库提供的基本都是行级锁。
mysql提供了多种选择：

• 表锁：表锁是mysql当中最基本的锁策略、并且是开销最小的策略。正如其名字所说，当一个用户对一张表里的内容进行操作时，那么就会锁定整个表，其它用户想要对这个表进行读或者写操作时，都会遭到阻塞。
• 行锁：一个表是由许多行数据组成的。行锁则是加在某一行数据上的。即锁的粒度更细了。一张表的锁的数量也增多了，所以行锁可以最大限度的支持并发控制，但却也带来了最大的锁开销

mysql的事务

事务是一组sql语句，这组sql语句要么全部成功执行，任何地方的失败都会导致整个事务回滚，即撤销已成功的sql语句。

事务例子

• 小明现在要将卡里的200元存到微信钱包里。步骤如下：

    检查卡里余额 是否高于200元
    将卡里数值减去200
    微信钱包数值增加200
  

  上述三个操作，必须全部执行，或者全部不执行，不然都会导致小明平白无故的少钱，或者多钱。

• 通常我们可以将sql语句放进

   start transaction;
   ...sql...
   ...sql...
   commit;
  

ACID

• 原子性
• 一致性
• 隔离性
• 持久性

事务的隔离级别

• 读未提交
  事务中的修改，即时还没有提交，对其它事务也都是可见的。事务可以提交未提交的事务，这就是所谓的 “脏读”。一般使用的比较少。
• 读已提交
  事务开始时，只能看见已经提交的事务所做的修改。即两次查询会读到不同的结果。即 “不可重复读问题”
• 可重复读
  可重复读是Mysql的默认隔离界别。该级别保证了同一个事务中，多次读取同样记录的结果是一致的。但可重复读并不能解决“幻读”的问题。
  幻读指的就是当某个事务在读取某一个范围的记录时，另外一个事务在该范围内插入了新的记录，则当前事务再去读取的时候，可能就读的不是之前的行。InnoDB通过 多版本并发控制(MVCC) 解决了幻读的问题。
• 可串行化
  serializable是最高的隔离级别。通过强制事务串行，避免了前面的幻读问题。它的解决方法，就是 加行级锁，所以可能会导致大量的超时和锁争用的问题。

Mysql的死锁

死锁指的是，两个或两个以上的事务，相互占用资源，并请求互相占用的资源，就产生了一个循环等待。

解决死锁

• InnoDB能够检测到死锁的循环依赖，并立即返回一个错误。
• 当查询出现锁等待超时的时候，放弃该锁请求。
• InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务回滚。（大多数情况下都采用这种，简单的方式）

事务日志

事务日志的作用就是，预写数据。将数据的内存拷贝，先记录到事务日志中，等到后面再持久化到磁盘上。

事务日志采用的是追加的方式，写日志的操作是磁盘一小块区域上的顺序I/O，而不是持久化数据到磁盘上的随机I/O，所以先记录到日志上更快。

如果数据的修改已经记录到日志上，但数据还没有持久化到磁盘，那么此时系统即时崩溃，也可以在重启时通过事务日志恢复这一部分的数据。

mysql的事务

mysql提供了两种事务型的存储引擎，分别是InnoDB和NDB Cluster

• 自动提交 ：mysql默认采用自动提交的模式。也就是我们平时写的sql语句，其实默认是处于事务当中的。我们可以通过变量 autocommit来启动或者禁用。1或者ON代表启动，0或者OFF代表禁用。
• 混合使用存储引擎：在实际设计表的过程中，我们要考虑事务回滚，针对不同的表采用不同的存储引擎。（例如InnoDB和MyISAM）。非事务型的存储引擎如MyISAM，如果想要回滚（即撤销变更），那么会导致数据库处于不一致的状态，难以修复。
• 隐式和显式的锁定：一般执行事务时，例如之前的commit和rollback，都是一种隐式锁定。通过特定语句Locks，Tables这种的锁定称为显式锁定。一般不要使用显式锁定。

多版本的并发控制

mysql的大多数事务型存储引擎实现的都不是简单的行级锁，一般都同时实现了多版本并发控制MVCC。MVCC是一种设计模式，或者说是思想。所以不同的数据库系统可能对于MVCC实现的方式不一样。

• 作用： 其在很多情况下避免了加锁操作，实现了非阻塞的读，只锁定必要的行。

• 实现： InnoDB的MVCC，是通过让每行记录都多了两列，一列是该行创建时间，一列该行删除时间。这里的时间指的其实是系统版本号，每开启一个事务，系统版本号就会递增。事务开始时刻的系统版本号，用来和每行记录的版本号进行比较。

    select：行的查找系统版本号要比事务的的系统版本号小，这样才能确保读取行的时候，该行是已经存在，准确的。
    		  或者行的删除版本号要比事务的系统版本号要大，这样才能确保在事务要删除的行，在事务执行完前，还是存在的。
    Insert：为新插入的每一行保存当前系统版本号，作为行版本号
    Delete：为删除的每一行保存当前系统版本号，作为删除版本号
    Update：为更新的行，将系统版本号更新为当前的行版本号
  

  保存这两个额外的系统版本号，可以使大多数读操作不用加锁，而且可以保证只会读到符合条件的行。但相应的，增大了每行的额外存储。

• 位置： MVCC只能用在不可重复读和读已提交两个隔离级别中使用。

Mysql为什么默认使用可重复读这个隔离级别

这个是有历史原因的。在mysql5.0版本之前，做mysql的主从集群的话，mysql有一个binlog文件，一个记录数据库修改的文件。当时这个binlog文件只有一个statement格式。对于这个文件，在当时的mysql如果使用读已提交这个隔离级别的话，就会产生一个问题，下面举一个例子来说明。

	有两个隔离级别都是RC的事务，正在执行值的插入和删除操作
     A事务             B事务
  删除值Value            /
	   /			插入值Value
	   / 			提交事务
	回滚事务             /

对于上面的操作，我们的mysql数据库存在主从机的情况下，会存在一个问题。对于master机来说，它对于上面事务的理解先删除再插入，所以数据库中会存在这个值。而对于slave机来说，它理解的binlog文件中，会理解成先插入，再删除，导致数据库中不存在这个值。

所以mysql当时选择采用了可重复读这个隔离级别。在可重复读这个隔离级别中，有加入一个间隙锁，即在执行删除操作时，会将其锁住，直至其完成。才会去执行后面的插入方法。

在mysql5.1之后，binlog推出了row的格式，基于行的sql复制，使得其不会出现上面的顺序问题。

所以对于一般的互联网项目，仅仅需要采用读已提交这个隔离就够了，因为这个隔离级别的并发读更低。

数据库的范式

• 第一范式，属性不可拆分，即同一列不能有多个值（或者可以理解为多个子列）。
• 第二范式，在满足第一范式的前提下，属性需要完全依赖于主属性。比如：现在主键是（A,B），那非主属性必须要完全依赖于(A,B），不能单单依赖于A或者B
• 第三范式，在满足第二范式的前提下，必须消除传递函数依赖。这是为了减少冗余的。比如student表，course表和grade表，你在course表里出现的课程名，就没必要在grade表里出现了。因为通过grade表里的课程号，就可以找到课程名了。
• BCNF，是3NF的修正版，满足　
  　1.所有非主属性对每一个码都是完全函数依赖。
  　2.所有的主属性对每一个不包含它的码,也是完全函数依赖。
  　3.没有任何属性完全函数依赖于非码的任何一组属性。
  主属性不可以通过传递函数，决定另一个主属性！