INNODB的自动增加字段解决方法一例

mysql> select * from parent;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
|  5 |
|  6 |
+----+
5 rows in set (0.00 sec)

mysql> select * from autoid;
+------+
| id   |
+------+
|    6 |
+------+
1 row in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> select  @autoid:=id+1 from autoid FOR UPDATE;
/*设置了for update锁
  在commit之前，其他mysql连接不能再次读取autoid表中的相应行的数据
  这样就不会产生两个用户再时读或当此事务未update表autoid时，另一个用户也读取表autoid中的相同行，从而避免了两个用户取到的@autoid值都是7*/
+------------------+
| @autoid:=id      |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

mysql> update autoid set id=@autoid;
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;    提交事务，因为尽量减少autoid表的锁定冲突，所以决定查询完成后既行更新，并提交事务；   如果把commit放在下面的插入语句后面，则可能增加其它插入语句读取autoid表中对应行的等待时间

mysql> insert into parent set id=@autoid; 
Query OK, 1 row affected (0.00 sec)


Query OK, 0 rows affected (0.01 sec)

mysql> select * from parent;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
5 rows in set (0.00 sec)

mysql> select * from autoid;
+------+
| id   |
+------+
|    7 |
+------+
1 row in set (0.00 sec)

===================================================================
锁定读SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE

在一些环境中，一个持续读是不方便的。比如，你可能想要往表的子表里添加一个新行，并确信该子表在父表中有一个根。

下列例子显示如何在你应用程序代码中实现参考的完整性。
假设你使用一个持续读去读取父表并且看到表中子表的根。不能安全地往子表添加子行吗？不，因为可能同时发生一些其它用户从父表删除父行，而你没有注意到它的情况。
解决办法是在使用LOCK IN SHARE MODE的锁定模式执行SELECT：

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

在 共享模式执行一个读意味着我们读最新的可用数据，并在我们读的行设置一个共享锁定。共享模式锁防止其它人更新或删除我们已读的行。同时，如果最新的数据属 于其它客户端尚未提交的事务，我们等着知道那个事务被提交。我们看到前述的查询返回父'Jones'，我们可以安全地往子表添加子记录并提交我们的事务。

让 我们看另外一个例子：我们在表child_codes 中有一个整数计数器域，我们用该表给每个添加到子表里的子项指派一个唯一的识别符。显然，使用持续读或者共享模式读去读取当前计数器的值并是一个好主意， 因为数据库的两个用户可能看到计数器的同一个值，如果两个用户试着用同一识别符往该表添加子项，就会发生一个重复键（duplicate-key）错误。

在这里，如果两个用户同时读计数器，当试图更新计数器之时，至少它们中有一个会发生死锁错误并终止，因此LOCK IN SHARE MODE并不是一个好的解决方法。
在这种情况下，有两个好方法去实现读计数器和增长计数器值：(1) 先更新计数器，让计数器值增1，之后读计数器，或者(2)用锁定模式FOR UPDATE先读计数器，之后计数器值增加。后一个途径可被如下实现：

SELECT counter_field FROM child_codes FOR UPDATE;

UPDATE child_codes SET counter_field = counter_field + 1;

SELECT ... FOR UPDATE读最新的可见数据，在每个它读取的行设置独占锁定。因此，它设置与搜索的SQL UPDATE可能会在行上设置的锁定同样的锁定。
请注意，以上仅是一个SELECT ... FOR UPDATE如何起作用的例子。在MySQL中，事实上生成一个唯一识别符的特殊任务可被用对该表的单独访问来完成：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);

SELECT LAST_INSERT_ID();

SELECT语句仅仅取回识别符信息（专门对当前连接）。它不访问任何表。