mysql锁机制

转自：http://thebye85.iteye.com/blog/1463546

MySQL有三种锁的级别：页级、表级、行级。
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-levellocking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。以下讲的都是在innodb引擎的前提下。

 

1、共享锁（Share Locks，即S锁），使用方式select ... LOCK IN SHARE MODE
SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends.
如：事务A中有select ... lock in share mode未提交前，其它事务的可以读本次事务前的最新值，写操作会阻塞直到事务A提交后才执行。

 

例子：

Sql代码  

1. SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;  

A read performed with LOCK IN SHARE MODE reads the latest available data and sets a shared mode lock on the rows read. 
A shared mode lock prevents others from updating or deleting the row read. 
Also, if the latest data belongs to a yet uncommitted transaction of another session, we wait until that transaction ends. 
After we see that the LOCK IN SHARE MODE query returns the parent 'Jones', we can safely add the child record to the child table and commit our transaction.
执行select ... LOCK IN SHARE MODE能读取最新的数据，select结果的全部行都会加行锁（如果这些行已被其它事务先加锁，如for update，则等待锁释放后再加锁），其它事务不能select .... for update、update和delete操作这些行记录，直到锁释放。select ... FOR UPDATE同时只能有一个在语句执行，另一个会阻塞；select ... LOCK IN SHARE MODE可以多个同时执行（这也是和for update最大的区别）

 

2、排它锁（Exclusive Locks，即X锁），使用方式：select ... FOR UPDATE 
For index records the search encounters, SELECT ... FOR UPDATE blocks other sessions from doing SELECT ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads will ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they will be reconstructed by applying undo logs on an in-memory copy of the record.)
如：事务A中有select ... for update未提交前，则其它事务的读、写操作会阻塞到事务A提交后才执行。

例子1： 
如一个事务中有select ... for update且事务还未提交，其它事务执行select ... for update会等待锁释放，执行select ... lock in share mode会等待锁释放，执行update或delete会等待锁释放，但可以执行普通select ...语句，因为普通的select查询的是数据快照。

 

    console1:

Sql代码  

1. mysql> start transaction;  
2. mysql> select * from test where id = 1 for update;  

 

    console2:

Sql代码  

1. mysql> start transaction;  
2. mysql> select * from test where id = 1; -- 查询的是快照数据  
3. +----+------------+-------+---------------------+---------------------+  
4. | id   | code        | valid | gmt_create        | gmt_modified     |  
5. +----+------------+-------+---------------------+---------------------+  
6. |  1   | 8967747 |     1  | 2011-11-16 16:44:52 | 2011-12-15 19:16:36 |  
7. +----+------------+-------+---------------------+---------------------+  
8. mysql> update test set valid = 0 where id = 1;  
9. 1205 - Lock wait timeout exceeded; try restarting transaction  

select ... for update锁住的行记录，其它事务不可修改，如果有修改会wait直到锁释放（事务commit）或超时

 

例子2：

利用select ... for update来实现更新原子性（排它锁）

思路是先锁住行记录（select ... for update），再更新。

 

Sql代码  

1. mysql> start transaction;  
2. mysql> select * from region   
3.             where id = 1  
4.             and status = 1   
5.             and remain_prize_quantity > 0  
6.             for update;  
7. mysql> update region  
8.             set remain_prize_quantity = remain_prize_quantity - 1,  
9.             gmt_modified = now()  
10.             where id = 1  
11.             and status = 1   
12.             and remain_prize_quantity > 0;  
13. mysql> commit;  

  

参考：

http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html