在調(diào)用存儲(chǔ)過程中，就會(huì)涉及到表鎖，行鎖這一概念：所謂區(qū)別：有索引的時(shí)候就是行鎖，沒有索引的時(shí)候就是表索。

innodb 的行鎖是在有索引的情況下,沒有索引的表是鎖定全表的.

表鎖演示（無索引）

Session1:

mysql> set autocommit=0;

mysql> select * from innodb_test;
+------+-------------+
| id?? | name??????? |
+------+-------------+
|??? 1 | woshiceshi? |?
|??? 2 | woshiceshi2 |?
|??? 3 | woshiceshi3 |?
+------+-------------+

mysql> select * from innodb_test where id = 2 for update;
+------+------------+
| id?? | name?????? |
+------+------------+
|??? 2 | woshiceshi2 |?
+------+------------+

Session2:

mysql> update innodb_test set name='sjis' where id = 1 ;
處于等待狀態(tài)....

再回到session1 commit以后，session2就出來結(jié)果了（鎖定了8秒，過了8秒左右才去session1提交）。

mysql> update innodb_test set name='sjis' where id = 1 ;
Query OK, 1 row affected (8.11 sec)
Rows matched: 1? Changed: 1? Warnings: 0

實(shí)驗(yàn)結(jié)果是：我在session1的for update 操作看似只鎖定ID為2的行其實(shí)鎖定了全表，以至于后面session2的對(duì)ID為1的行update 需要等待Session1鎖的釋放。

行鎖演示（索引為ID）

Session1:
mysql> alter table innodb_test add index idx_id(id);
Query OK, 4 rows affected (0.01 sec)
Records: 4? Duplicates: 0? Warnings: 0

mysql> select * from innodb_test where id = 2 for update;
+------+------------+
| id?? | name?????? |
+------+------------+
|??? 2 | woshiceshi2 |?
+------+------------+

Session2:

mysql> update innodb_test set name='wohaishiceshi' where id = 1 ;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1? Changed: 1? Warnings: 0
mysql> select * from innodb_test where id = 1;???????????
+------+---------------+
| id?? | name????????? |
+------+---------------+
|??? 1 | wohaishiceshi |?
+------+---------------+
1 row in set (0.00 sec)

實(shí)驗(yàn)結(jié)果:這次的鎖定是鎖定的行，所以沒有被鎖定的行（ID不為2的行）可以進(jìn)行update..

http://keshion.iteye.com/category/125242