数据库悲观锁和乐观锁

一、Oracle

Oracle数据库悲观锁与乐观锁是本文我们主要要介绍的内容。有时候为了得到最大的性能，一般数据库都有并发机制，不过带来的问题就是数据访问的冲突。为了解决这个问题，大多数数据库用的方法就是数据的锁定。

数据的锁定分为两种方法，第一种叫做悲观锁，第二种叫做乐观锁。什么叫悲观锁呢，悲观锁顾名思义，就是对数据的冲突采取一种悲观的态度，也就是说假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁定住。而乐观锁就是认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让用户返回错误的信息，让用户决定如何去做。

先从悲观锁开始说。在SqlServer等其余很多数据库中，数据的锁定通常采用页级锁的方式，也就是说对一张表内的数据是一种串行化的更新插入机制，在任何时间同一张表只会插1条数据，别的想插入的数据要等到这一条数据插完以后才能依次插入。带来的后果就是性能的降低，在多用户并发访问的时候，当对一张表进行频繁操作时，会发现响应效率很低，数据库经常处于一种假死状态。而Oracle用的是行级锁，只是对想锁定的数据才进行锁定，其余的数据不相干，所以在对Oracle表中并发插数据的时候，基本上不会有任何影响。

注：对于悲观锁是针对并发的可能性比较大，而一般在我们的应用中用乐观锁足以。

Oracle的悲观锁需要利用一条现有的连接，分成两种方式，从SQL语句的区别来看，就是一种是for update，一种是for update nowait的形式。比如我们看一个例子。首先建立测试用的数据库表。

CREATE TABLE TEST(ID,NAME,LOCATION,VALUE,CONSTRAINT test_pk PRIMARY KEY(ID))AS SELECT deptno, dname, loc, 1 FROM scott.dept

这里我们利用了Oracle的Sample的scott用户的表，把数据copy到我们的test表中。首先我们看一下for update锁定方式。首先我们执行如下的select for update语句。

select * from test where id = 10 for update

通过这条检索语句锁定以后，再开另外一个sql*plus窗口进行操作，再把上面这条sql语句执行一便，你会发现sqlplus好像死在那里了，好像检索不到数据的样子，但是也不返回任何结果，就属于卡在那里的感觉。这个时候是什么原因呢，就是一开始的第一个Session中的select for update语句把数据锁定住了。由于这里锁定的机制是wait的状态(只要不表示nowait那就是wait)，所以第二个Session(也就是卡住的那个sql*plus)中当前这个检索就处于等待状态。当第一个session最后commit或者rollback之后，第二个session中的检索结果就是自动跳出来，并且也把数据锁定住。不过如果你第二个session中你的检索语句如下所示。

select * from test where id = 10

也就是没有for update这种锁定数据的语句的话，就不会造成阻塞了。另外一种情况，就是当数据库数据被锁定的时候，也就是执行刚才for update那条sql以后，我们在另外一个session中执行for update nowait后又是什么样呢。比如如下的sql语句。 由于这条语句中是制定采用nowait方式来进行检索，所以当发现数据被别的session锁定中的时候，就会迅速返回ORA-00054错误，内容是资源正忙, 但指定以 NOWAIT 方式获取资源。所以在程序中我们可以采用nowait方式迅速判断当前数据是否被锁定中，如果锁定中的话，就要采取相应的业务措施进行处理。

select * from test where id = 10 for update nowait

那这里另外一个问题，就是当我们锁定住数据的时候，我们对数据进行更新和删除的话会是什么样呢。比如同样，我们让第一个Session锁定住id=10的那条数据，我们在第二个session中执行如下语句。

update test set value=2 where id = 10

这个时候我们发现update语句就好像select for update语句一样也停住卡在这里，当你第一个session放开锁定以后update才能正常运行。当你update运行后，数据又被你update语句锁定住了，这个时候只要你update后还没有commit，别的session照样不能对数据进行锁定更新等等。

总之，Oracle中的悲观锁就是利用Oracle的Connection对数据进行锁定。在Oracle中，用这种行级锁带来的性能损失是很小的，只是要注意程序逻辑，不要给你一不小心搞成死锁了就好。而且由于数据的及时锁定，在数据提交时候就不呼出现冲突，可以省去很多恼人的数据冲突处理。缺点就是你必须要始终有一条数据库连接，就是说在整个锁定到最后放开锁的过程中，你的数据库联接要始终保持住。与悲观锁相对的，我们有了乐观锁。乐观锁一开始也说了，就是一开始假设不会造成数据冲突，在最后提交的时候再进行数据冲突检测。

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

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

[2]第二种乐观锁的做法就是采用版本戳，这个在Hibernate中得到了使用。采用版本戳的话，首先需要在你有乐观锁的数据库table上建立一个新的column，比如为number型，当你数据每更新一次的时候，版本数就会往上增加1。比如同样有2个session同样对某条数据进行操作。两者都取到当前的数据的版本号为1，当第一个session进行数据更新后，在提交的时候查看到当前数据的版本还为1，和自己一开始取到的版本相同。就正式提交，然后把版本号增加1，这个时候当前数据的版本为2。

当第二个session也更新了数据提交的时候，发现数据库中版本为2，和一开始这个session取到的版本号不一致，就知道别人更新过此条数据，这个时候再进行业务处理，比如整个Transaction都Rollback等等操作。在用版本戳的时候，可以在应用程序侧使用版本戳的验证，也可以在数据库侧采用Trigger(触发器)来进行验证。不过数据库的Trigger的性能开销还是比较的大，所以能在应用侧进行验证的话还是推荐不用Trigger。

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

二、Mysql

悲观锁介绍（百科）：

悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

 

使用场景举例：以MySQL InnoDB为例

商品goods表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单，那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。

 

1如果不采用锁，那么操作方法如下：

//1.查询出商品信息

select status from t_goods where id=1;

//2.根据商品信息生成订单

insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2

update t_goods set status=2;

 

上面这种场景在高并发访问的情况下很可能会出现问题。

前面已经提到，只有当goods status为1时才能对该商品下单，上面第一步操作中，查询出来的商品status为1。但是当我们执行第三步Update操作的时候，有可能出现其他人先一步对商品下单把goods status修改为2了，但是我们并不知道数据已经被修改了，这样就可能造成同一个商品被下单2次，使得数据不一致。所以说这种方式是不安全的。

 

2使用悲观锁来实现：

在上面的场景中，商品信息从查询出来到修改，中间有一个处理订单的过程，使用悲观锁的原理就是，当我们在查询出goods信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为goods被锁定了，就不会出现有第三者来对其进行修改了。

 

注：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

 

我们可以使用命令设置MySQL为非autocommit模式：

set autocommit=0;

 

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

//0.开始事务

begin;/begin work;/start transaction; (三者选一就可以)

//1.查询出商品信息

select status from t_goods where id=1 for update;

//2.根据商品信息生成订单

insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2

update t_goods set status=2;

//4.提交事务

commit;/commit work;

 

注：上面的begin/commit为事务的开始和结束，因为在前一步我们关闭了mysql的autocommit，所以需要手动控制事务的提交，在这里就不细表了。

 

上面的第一步我们执行了一次查询操作：select status from t_goods where id=1 for update;

与普通查询不一样的是，我们使用了select…for update的方式，这样就通过数据库实现了悲观锁。此时在t_goods表中，id为1的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

 

注：需要注意的是，在事务中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 则不受此影响。拿上面的实例来说，当我执行select status from t_goods where id=1 for update;后。我在另外的事务中如果再次执行select status from t_goods where id=1 for update;则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行select status from t_goods where id=1;则能正常查询出数据，不会受第一个事务的影响。

 

补充：MySQL select…for update的Row Lock与Table Lock

上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

 

举例说明：

数据库表t_goods，包括id,status,name三个字段，id为主键，数据库中记录如下;

Sql代码 

1. mysql> select * from t_goods;   
2. +----+--------+------+   
3. | id | status | name |   
4. +----+--------+------+   
5. |  1 |      1 | 道具 |   
6. |  2 |      1 | 装备 |   
7. +----+--------+------+   
8. 2 rows in set  
9.   
10. mysql>  

mysql> select * from t_goods;+----+--------+------+| id | status | name |+----+--------+------+|  1 |      1 | 道具 ||  2 |      1 | 装备 |+----+--------+------+2 rows in setmysql>

注：为了测试数据库锁，我使用两个console来模拟不同的事务操作，分别用console1、console2来表示。 

 

例1: (明确指定主键，并且有此数据，row lock)

console1：查询出结果，但是把该条数据锁定了

Sql代码 

1. mysql> select * from t_goods where id=1 for update;   
2. +----+--------+------+   
3. | id | status | name |   
4. +----+--------+------+   
5. |  1 |      1 | 道具 |   
6. +----+--------+------+   
7. 1 row in set  
8.   
9. mysql>  

mysql> select * from t_goods where id=1 for update;+----+--------+------+| id | status | name |+----+--------+------+|  1 |      1 | 道具 |+----+--------+------+1 row in setmysql>

console2：查询被阻塞

Sql代码 

1. mysql> select * from t_goods where id=1 for update;  

mysql> select * from t_goods where id=1 for update;

console2：如果console1长时间未提交，则会报错

Sql代码 

1. mysql> select * from t_goods where id=1 for update;   
2. ERROR 1205 : Lock wait timeout exceeded; try restarting transaction  

mysql> select * from t_goods where id=1 for update;ERROR 1205 : Lock wait timeout exceeded; try restarting transaction

 

例2: (明确指定主键，若查无此数据，无lock)

console1：查询结果为空

Sql代码 

1. mysql> select * from t_goods where id=3 for update;   
2. Empty set  

mysql> select * from t_goods where id=3 for update;Empty set

console2：查询结果为空，查询无阻塞，说明console1没有对数据执行锁定

Sql代码 

1. mysql> select * from t_goods where id=3 for update;   
2. Empty set  

mysql> select * from t_goods where id=3 for update;Empty set

 

例3: (无主键，table lock)

console1：查询name=道具 的数据，查询正常

Sql代码 

1. mysql> select * from t_goods where name='道具' for update;   
2. +----+--------+------+   
3. | id | status | name |   
4. +----+--------+------+   
5. |  1 |      1 | 道具 |   
6. +----+--------+------+   
7. 1 row in set  
8.   
9. mysql>  

mysql> select * from t_goods where name='道具' for update;+----+--------+------+| id | status | name |+----+--------+------+|  1 |      1 | 道具 |+----+--------+------+1 row in setmysql>

console2：查询name=装备 的数据，查询阻塞，说明console1把表给锁住了

Sql代码 

1. mysql> select * from t_goods where name='装备' for update;  

mysql> select * from t_goods where name='装备' for update;

console2：若console1长时间未提交，则查询返回为空

Sql代码 

1. mysql> select * from t_goods where name='装备' for update;   
2. Query OK, -1 rows affected  

mysql> select * from t_goods where name='装备' for update;Query OK, -1 rows affected

 

例4: (主键不明确，table lock)

console1：查询正常

Sql代码 

1. mysql> begin;   
2. Query OK, 0 rows affected   
3.   
4. mysql> select * from t_goods where id>0 for update;   
5. +----+--------+------+   
6. | id | status | name |   
7. +----+--------+------+   
8. |  1 |      1 | 道具 |   
9. |  2 |      1 | 装备 |   
10. +----+--------+------+   
11. 2 rows in set  
12.   
13. mysql>  

mysql> begin;Query OK, 0 rows affectedmysql> select * from t_goods where id>0 for update;+----+--------+------+| id | status | name |+----+--------+------+|  1 |      1 | 道具 ||  2 |      1 | 装备 |+----+--------+------+2 rows in setmysql>

console2：查询被阻塞，说明console1把表给锁住了

Sql代码 

1. mysql> select * from t_goods where id>1 for update;  

mysql> select * from t_goods where id>1 for update;

 

例5: (主键不明确，table lock)

console1：

Sql代码 

1. mysql> begin;   
2. Query OK, 0 rows affected   
3.   
4. mysql> select * from t_goods where id<>1 for update;   
5. +----+--------+------+   
6. | id | status | name |   
7. +----+--------+------+   
8. |  2 |      1 | 装备 |   
9. +----+--------+------+   
10. 1 row in set  
11.   
12. mysql>  

mysql> begin;Query OK, 0 rows affectedmysql> select * from t_goods where id<>1 for update;+----+--------+------+| id | status | name |+----+--------+------+|  2 |      1 | 装备 |+----+--------+------+1 row in setmysql>

console2：查询被阻塞，说明console1把表给锁住了

Sql代码 

1. mysql> select * from t_goods where id<>2 for update;  

mysql> select * from t_goods where id<>2 for update;

console1：提交事务

Sql代码 

1. mysql> commit;   
2. Query OK, 0 rows affected  

mysql> commit;Query OK, 0 rows affected

console2：console1事务提交后，console2查询结果正常

Sql代码 

1. mysql> select * from t_goods where id<>2 for update;   
2. +----+--------+------+   
3. | id | status | name |   
4. +----+--------+------+   
5. |  1 |      1 | 道具 |   
6. +----+--------+------+   
7. 1 row in set  
8.   
9. mysql>  

mysql> select * from t_goods where id<>2 for update;+----+--------+------+| id | status | name |+----+--------+------+|  1 |      1 | 道具 |+----+--------+------+1 row in setmysql>

 

以上就是关于数据库主键对MySQL锁级别的影响实例，需要注意的是，除了主键外，使用索引也会影响数据库的锁定级别

 

举例：

我们修改t_goods表，给status字段创建一个索引

修改id为2的数据的status为2，此时表中数据为：

Sql代码 

1. mysql> select * from t_goods;   
2. +----+--------+------+   
3. | id | status | name |   
4. +----+--------+------+   
5. |  1 |      1 | 道具 |   
6. |  2 |      2 | 装备 |   
7. +----+--------+------+   
8. 2 rows in set  
9.   
10. mysql>  

mysql> select * from t_goods;+----+--------+------+| id | status | name |+----+--------+------+|  1 |      1 | 道具 ||  2 |      2 | 装备 |+----+--------+------+2 rows in setmysql>

 

例6: (明确指定索引，并且有此数据，row lock)

console1：

Sql代码 

1. mysql> select * from t_goods where status=1 for update;   
2. +----+--------+------+   
3. | id | status | name |   
4. +----+--------+------+   
5. |  1 |      1 | 道具 |   
6. +----+--------+------+   
7. 1 row in set  
8.   
9. mysql>  

mysql> select * from t_goods where status=1 for update;+----+--------+------+| id | status | name |+----+--------+------+|  1 |      1 | 道具 |+----+--------+------+1 row in setmysql>

console2：查询status=1的数据时阻塞，超时后返回为空，说明数据被console1锁定了

Sql代码 

1. mysql> select * from t_goods where status=1 for update;   
2. Query OK, -1 rows affected  

mysql> select * from t_goods where status=1 for update;Query OK, -1 rows affected

console2：查询status=2的数据，能正常查询，说明console1只锁住了行，未锁表

Sql代码 

1. mysql> select * from t_goods where status=2 for update;   
2. +----+--------+------+   
3. | id | status | name |   
4. +----+--------+------+   
5. |  2 |      2 | 装备 |   
6. +----+--------+------+   
7. 1 row in set  
8.   
9. mysql>  

mysql> select * from t_goods where status=2 for update;+----+--------+------+| id | status | name |+----+--------+------+|  2 |      2 | 装备 |+----+--------+------+1 row in setmysql>

 

例7: (明确指定索引，若查无此数据，无lock)

console1：查询status=3的数据，返回空数据

Sql代码 

1. mysql> select * from t_goods where status=3 for update;   
2. Empty set  

mysql> select * from t_goods where status=3 for update;Empty set

console2：查询status=3的数据，返回空数据

Sql代码 

1. mysql> select * from t_goods where status=3 for update;   
2. Empty set  

mysql> select * from t_goods where status=3 for update;Empty set