用于锁定操作或避免并发数据操作的防御策略

Question

我有一个小型 PHP 脚本，它可以在表中的每个步骤处理固定数量的行 创建统计信息，直到处理完所有行。

我想知道避免脚本并发运行的最佳策略是什么 SQL/DML语句如果我

1. 只知道它是MySQL数据库主要版本5
2. 不知道使用什么引擎（MyISAM或...）
3. 只能保证数据库用户具有SELECT，INSERT，UPDATE，DELETE权限

Answer 1

InnoDB 是自动的：

在 InnoDB 中你根本不需要锁定任何东西，因为 MySQL 运行在 REPEATABLE READ 事务隔离级别。当您开始一个事务，然后选择某些内容时，第一个 SELECT 就会启动一个事务，并且由于这是可重复读取，因此您对数据的视图将保持一致且不变，直到您提交为止。

这意味着InnoDB REPEATABLE READ中的BEGIN、SELECT、SELECT、COMMIT与SELECT、SELECT不同。

MyISAM 是表锁：

在 MyISAM 中，您需要 LOCK TABLES a, b, c READ, d WRITE 在您正在读取的表上放置读锁，并在您正在写入的表上放置读/写锁到。使用 LOCK TABLES 不可能增量地构建一个锁列表，因此您必须一次性锁定您将要触及的所有内容，并且在 UNLOCK TABLES。 LOCK TABLES 需要额外的权限，并且在 MyISAM 中给予 SELECT 权限而不提供 LOCK 权限没有什么意义。

通用的是乐观锁定：

在没有事务的MyISAM和InnoDB中，语句以原子方式执行。这足以实现乐观锁定。在乐观锁定中，假设您有一行包含主键和数据。将 state 和 owner 列添加到其中。

root@localhost [kris]> create table d ( id serial, d varchar(20), state enum('unclaimed', 'claimed', 'done') not null, owner integer unsigned not null);
Query OK, 0 rows affected (0.51 sec)

root@localhost [kris]> insert into d values (1, 'one', 'unclaimed', 0), (2, 'two','unclaimed', 0);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [kris]> select * from d;
+----+------+-----------+-------+
| id | d    | state     | owner |
+----+------+-----------+-------+
|  1 | one  | unclaimed |     0 |
|  2 | two  | unclaimed |     0 |
+----+------+-----------+-------+
2 rows in set (0.00 sec)

您现在可以为您的处理器声明一行或多行：

root@localhost [kris]> update d 
    set owner = 1, state = 'claimed' 
where 
    state = 'unclaimed' 
order by id limit 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

警告声明该语句对于复制来说不安全，但是 ORDER BY 应该使 LIMIT 对于复制来说是安全的。结果是我们为所有者 id 为 1 的所有者声明了一行。这在并发访问中是安全的，因为更新是以原子方式执行的：

root@localhost [kris]> select * from d;
+----+------+-----------+-------+
| id | d    | state     | owner |
+----+------+-----------+-------+
|  1 | one  | claimed   |     1 |
|  2 | two  | unclaimed |     0 |
+----+------+-----------+-------+
2 rows in set (0.00 sec)

我们需要选择回当前正在处理的内容，并处理它：

root@localhost [kris]> select id, d from d where state = 'claimed' and owner = 1;
+----+------+
| id | d    |
+----+------+
|  1 | one  |
+----+------+
1 row in set (0.00 sec)

我们还完成后需要将其设置为完成：

root@localhost [kris]> update d set state = 'done', owner = 0 where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0