mysql 中select...lock in share mode与select...for update

select...lock in share mode和select...for update一直模模糊糊的..
 这次刚好碰到..就测了一把...
 
首先看官方的说明:
 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.
 select...lock in share在select的结果集上加一个共享锁.允许其他会话读取这些数据,但不允许修改.
 读取的行数是最新的行,如果在读取之前有另外的事物没有提交,共享锁会被堵塞,直到事物结束.
 

SELECT ... FOR UPDATE sets an exclusive lock on the rows read. An exclusive lock prevents other sessions from accessing the rows for reading or writing.
 select...for update 将读取的结果集加一个排他锁. 阻止其他会话读或写该结果集.
 
由此可见,这两种加锁的方式不一样.虽然for update会加排他锁,但个人还推荐使用该方法.因为lock inshare mode容易造成死循环.
 如下所测试:

---

 |                                                         |                                                                    
| session 1                                               |   session 2                                                        
|                                                         |                                                                    
|                                                         |                                                                    
| mysql> begin;                                           |                                                                    
|Query OK, 0 rows affected (0.00 sec)                     |                                                                    
|                                                         |                                                                    
|mysql> select * from info where id <4 lock in share mode;|                                                                    
|+----+-------------+----------+------+                   |                                                                    
|| id | temperature | humidity | type |                   |                                                                    
|+----+-------------+----------+------+                   |                                                                    
||  1 |           1 |        2 | 33   |                   |                                                                    
||  2 |           2 |        3 | 33   |                   |                                                                    
||  3 |           1 |        4 | 33   |                   |                                                                    
|+----+-------------+----------+------+                   |                                                                    
| 3 rows in set (0.00 sec)                                |                                                                    
|                                                         |                                                                    
| mysql> update info set type ='44' where id =2;          |                                                                    

---

|                                                         |                                                                    
|                                                         |mysql> use test;                                                    
|                                                         |Reading table information for completion of table and column names  
|                                                         |You can turn off this feature to get a quicker startup with -A      
|                                                         |                                                                    
|                                                         |Database changed                                                    
|                                                         |mysql> begin;                                                       
|                                                         |Query OK, 0 rows affected (0.00 sec)                                
|                                                         |                                                                    
|                                                         |mysql> select * from info where id <4 lock in share mode;           
|                                                         |+----+-------------+----------+------+                              
|                                                         || id | temperature | humidity | type |                              
|                                                         |+----+-------------+----------+------+                              
|                                                         ||  1 |           1 |        2 | 33   |                              
|                                                         ||  2 |           2 |        3 | 33   |                              
|                                                         ||  3 |           1 |        4 | 33   |                              
|                                                         |+----+-------------+----------+------+                              
|                                                         |3 rows in set (0.00 sec)                                            
|                                                         |                                                                    
|                                                         |mysql> update info set type ='55' where id =1;                       
 
 此时看锁情况:

mysql> select * from innodb_trx;
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 23840  | RUNNING   | 2015-05-27 15:03:57 | NULL                  | NULL             |          2 |                   3 | NULL      | NULL                |                 0 |                 0 |                2 |                   360 |               3 |                 0 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                     10000 |                0 |                          0 |
| 23839  | RUNNING   | 2015-05-27 15:03:34 | NULL                  | NULL             |          2 |                   2 | NULL      | NULL                |                 0 |                 0 |                2 |                   360 |               3 |                 0 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                     10000 |                0 |                          0 |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.00 sec)

mysql> select * from innodb_locks;
+--------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 23839:57:3:3 | 23839       | X         | RECORD    | `test`.`info` | PRIMARY    |         57 |         3 |        3 | 2         |
| 23840:57:3:3 | 23840       | S         | RECORD    | `test`.`info` | PRIMARY    |         57 |         3 |        3 | 2         |
+--------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT concat(e.id,' is block ', b.id) 'block_session_id',
    ->        f.trx_state 'block_tx_st',
    ->        c.trx_state 'blocked_tx_st',
    ->        concat(e.DB,' ', e.TIME,' ',e.STATE) 'block_sess_st' ,
    ->        concat(b.DB,' ',b.TIME,' ',b.STATE) 'blocked_sess_st',
    ->        e.INFO 'block_sql',
    ->        b.INFO 'blocked_sql'
    ->   FROM INNODB_LOCK_WAITS a,
    ->        PROCESSLIST       b,
    ->        INNODB_TRX        c,
    ->        INNODB_LOCK_WAITS d,
    ->        PROCESSLIST       e,
    ->        INNODB_TRX        f
    ->  where a.requesting_trx_id = c.trx_id
    ->    and c.trx_mysql_thread_id = b.id
    ->    and d.blocking_trx_id = f.trx_id
    ->    and f.trx_mysql_thread_id = e.id
    ->    and a.requesting_trx_id = d.requesting_trx_id
    ->    and a.blocking_trx_id = d.blocking_trx_id;
+------------------+-------------+---------------+---------------+------------------+-----------+----------------------------------------+
| block_session_id | block_tx_st | blocked_tx_st | block_sess_st | blocked_sess_st  | block_sql | blocked_sql                            |
+------------------+-------------+---------------+---------------+------------------+-----------+----------------------------------------+
| 3 is block 2     | RUNNING     | LOCK WAIT     | test 125      | test 36 updating | NULL      | update info set type ='44' where id =2 |
+------------------+-------------+---------------+---------------+------------------+-----------+----------------------------------------+
1 row in set (0.01 sec)

都在相互等锁释放. 如此并形成了死循环了...跟死锁差不多...所以...推荐使用for update;