5，SELECT ... FOR UPDATE && SELECT ... LOCK IN SHARE MODE
默认情况下，innodb存储引擎的select操作使用一致性非锁定读，但是在某些情况下，需要对读操作进行加锁。Innodb存储引擎对select语句支持2种添加锁操作;
? SELECT ... FOR UPDATE 对于读取的行记录加一个X排它锁，其他事务如果要对这些行进行dml或者select操作都会被阻塞。
? SELECT ... LOCK IN SHARE MODE 对于读取的行记录添加一个S共享锁。其它事务可以向被锁定的行加S锁，但是不允许添加X锁，否则会被阻塞。
对于一致性 非锁定读，即使读取的行数已经被SELECT ... FOR UPDATE了，但是也是可以进行读取的。
PS：… FOR UPDATE以及LOCK IN SHARE MODE必须在一个事务中，如果事务commit了，锁也就释放了，所以在使用的时候务必加上begin;start transaction或者set autocommit=0;
例子如下：
会话A：开启事务，执行LOCK IN SHARE MODE;锁定
MySQL> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;
+---+----+----+
| a | b | c |
+---+----+----+
| 1 | c0 | c2 |
+---+----+----+
1 row in set (0.00 sec)
mysql>
同时在另外一个窗口开启会话B，执行dml操作
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set a=111 where a=1;
…这里会卡住，没有信息。
再开启一个会话C，查询INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS表，就会看到锁的详细信息：
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 |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 3015708:797:3:2 | 3015708 | X | RECORD | `test`.`t1` | PRIMARY | 797 | 3 | 2 | 1 |
| 3015706:797:3:2 | 3015706 | S | RECORD | `test`.`t1` | PRIMARY | 797 | 3 | 2 | 1 |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)
mysql>
mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 3015708 | 3015708:797:3:2 | 3015706 | 3015706:797:3:2 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_state` AS waiting_trx_status,it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query,
-> it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query
-> FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2
-> WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`;
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_trx_status | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
| 3015708 | LOCK WAIT | 18 | update t1 set a=111 where a=1 | 3015706 | 21 | NULL |
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
mysql>
会话A开启的事务1（事务id：3015706）执行了SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;
语句已经在a=1的行上加了S锁，所以会话B开启的事务2（事务id：23015708）执行的update t1 set a=111 where a=1;sql语句往a=1的行上加X锁，就被会话A的事务1阻塞了，所以事务2的状态值就是LOCK WAIT，一直在等待。直到事务等待超时，报错如下：
mysql> update t1 set a=111 where a=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
此时，会话B中的事务2就终止了update t1 set a=111 where a=1;的dml请求操作。
6，自增长和锁
自增长在数据库中是非常常见的一种属性，在Innodb的存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对有自增长字段的表进行insert时候，这个计数器会被初始化，执行如下的sql语句来得到计数器的值。
SELECT MAX(auto_inc_col) FROM tablename FOR UPDATE;
插入操作会依据这个自增长的计数器值+1赋予自增长列，这个实现方式称为AUTO-INC Locking，这种锁其实是一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql语句后立即释放。
从mysql 5.1.22版本开始，提供了一些轻量级互斥的自增长实现机制，这种机制大大提高自增长值插入的性能。还提供了一个参数innodb_autoinc_lock_mode，默认值为1.
自增长的分类：
在mysql的innodb表中，自增长列必须是索引，而且必须为索引的第一列，如果是第二个列会报错如下所示：
mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>
mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),PRIMARY KEY (a),KEY (b,a));
Query OK, 0 rows affected (0.01 sec)
mysql>
而在myisam表中，则没有这样的限制，如下所示：
mysql> CREATE TABLE t_myisam(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a))engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql>
7，MySQL外键和锁
在innodb存储引擎中，对于一个外键列，如果没有显式的针对这个列添加索引Innodb存储引擎会自动的对其添加一个索引，这样可以避免表锁，这点比Oracle做的较好一些，oracle需要自己手动添加外键锁。