数据库锁整理

B+树

在了解数据库索引之前，首先了解一下数据库索引的数据结构基础，B+tree

B＋tree 是一个n叉树，每个节点有多个叶子节点，一颗B+树包含根节点，内部节点，叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上叶子节点的节点。

B＋tree的性质：

1. n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
2. 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3. 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

B＋tree结构原型图大概如下（引用）：

由于B+tree的性质， 它通常被用于数据库和操作系统的文件系统中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引，因为B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度（B+ 树元素自底向上插入）。

索引

索引的原理：

对要查询的字段建立索引其实就是把该字段按照一定的方式排序；建立的索引只对该字段有用，如果查询的字段改变，那么这个索引也就无效了，比如图书馆的书是按照书名的第一个字母排序的，那么你想要找作者叫张三的就不能用改索引了；还有就是如果索引太多会降低查询的速度。

索引是优缺点：   

首先明白为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。那么在任何时候都应该加索引么？

这里有几个反例：

1. 如果每次都需要取到所有表记录，无论如何都必须进行全表扫描了，那么是否加索引也没有意义了。
2. 对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义。
3. 对于记录比较少的表，增加索引不会带来速度的优化反而浪费了存储空间，因为索引是需要存储空间的，而且有个致命缺点是对于update/insert/delete的每次执行，字段的索引都必须重新计算更新。所以并不是任何情况下都改建立索引的

唯一索引

唯一性索引，可以保证数据库表中每一行数据的唯一性。

INSERT 中ON DUPLICATE KEY UPDATE的使用

如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
mysql>UPDATE table SET c=c+1 WHERE a=1;

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。

注释：如果列b也是唯一列，则INSERT与此UPDATE语句相当：

mysql>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2与多个行向匹配，则只有一个行被更新。通常，您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT...UPDATE语句的INSERT部分引用列值。换句话说，如果没有发生重复关键字冲突，则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT...UPDATE语句中有意义，其它时候会返回NULL。

示例：

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
          ->ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

本语句与以下两个语句作用相同：

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)
          ->ON DUPLICATE KEY UPDATE c=3;
mysql>INSERT INTO table (a,b,c) VALUES (4,5,6)
          ->ON DUPLICATE KEY UPDATE c=9;

当您使用ON DUPLICATE KEY UPDATE时，DELAYED选项被忽略。

锁的作用域

根据不同的锁的作用域我们可以把数据库的锁分为三种，分别为：

• 行锁：对表中的某一行进行加锁。
• 页锁：对表中一组连续的行进行加锁。
• 表锁：对整张表进行加锁

不同的作用域对并发性能是有很大影响的，比如说如果数据库的插入都是使用表锁，那在大量用户对某张表进行插入读取操作的话，同时只能有一个用户可以访问该表，那并发量肯定就是惨不忍睹了。

乐观锁

在乐观锁中，我们有3种常用的做法来实现：

第一种就是在数据取得的时候把整个数据都copy到应用中，在进行提交的时候比对当前数据库中的数据和开始的时候更新前取得的数据。当发现两个数据一模一样以后，就表示没有冲突可以提交，否则则是并发冲突，需要去用业务逻辑进行解决。

第二种乐观锁的做法就是采用版本戳，这个在Hibernate中得到了使用。采用版本戳的话，首先需要在你有乐观锁的数据库table上建立一个新的column，比如为number型，当你数据每更新一次的时候，版本数就会往上增加1。比如同样有2个session同样对某条数据进行操作。两者都取到当前的数据的版本号为1，当第一个session进行数据更新后，在提交的时候查看到当前数据的版本还为1，和自己一开始取到的版本相同。就正式提交，然后把版本号增加1，这个时候当前数据的版本为2。当第二个session也更新了数据提交的时候，发现数据库中版本为2，和一开始这个session取到的版本号不一致，就知道别人更新过此条数据，这个时候再进行业务处理，比如整个Transaction都Rollback等等操作。在用版本戳的时候，可以在应用程序侧使用版本戳的验证，也可以在数据库侧采用Trigger(触发器)来进行验证。不过数据库的Trigger的性能开销还是比较的大，所以能在应用侧进行验证的话还是推荐不用Trigger。

第三种做法和第二种做法有点类似，就是也新增一个Table的Column，不过这次这个column是采用timestamp型，存储数据最后更新的时间。在Oracle9i以后可以采用新的数据类型，也就是timestamp with time zone类型来做时间戳。这种Timestamp的数据精度在Oracle的时间类型中是最高的，精确到微秒(还没与到纳秒的级别)，一般来说，加上数据库处理时间和人的思考动作时间，微秒级别是非常非常够了，其实只要精确到毫秒甚至秒都应该没有什么问题。和刚才的版本戳类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。如果不想把代码写在程序中或者由于别的原因无法把代码写在现有的程序中，也可以把这个时间戳乐观锁逻辑写在Trigger或者存储过程中

悲观锁（排他锁）

悲观锁也称之为互斥锁，可以写为X锁，指的是同时只能有一个事务可以对某个资源进行访问操作。如果有两个事务同时要操作某张表，我们称之为事务A和事务B，如果事务A获得了这张表的表锁，那事务B只能等待事务A释放了这个锁之后才能对该表进行操作。

数据库的insert，update操作默认是采用互斥锁进行加锁，读取select则不是，如果要对select操作使用互斥锁，可以这样写

select * from table where id = 1 for update

共享锁

共享锁是一种乐观锁，可以写为S锁，在数据库中共享锁的作用主要是针对读取操作的。如果读取操作使用X锁的话，并发量会非常低，所以数据库提供了共享锁S锁，提高读取操作的并发性能，多个事务可以同时持有一个资源的S锁，不像X锁，同时只能有一个事务持有。

举个例子：

事务A和事务B对表TABLE进行访问，事务A想查看id = 1的行信息

select * from TABLE where id = 1 lock in share mode

如果当前id = 1的行对应的X锁没有被其他事务获取，那事务A就顺利的获得了该行的S锁。

现在事务B也想查看id = 1 的行信息，会怎么样？

select * from TABLE where id = 1 lock in share mode

现在持有该行锁的只有事务A，持有的是S锁，所以事务B也可以获取该行的S锁，两个事务可以并发的读取id = 1的行。

这个和之前所说的乐观锁实现是有区别的，最大的不同就是读取的时候共享锁是要真的去持有锁，但是乐观锁只是实现了一种CAS模式，但是并读取的时候没有真的持有锁。