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

Question

我有一个小型PHP脚本，它可以在表中每步固定的行数上工作，以创建统计数据，直到所有行都被处理为止。

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

1. 只知道它是MySQL数据库主要版本5
2. 不知道使用什么引擎(MyISAM或.)
3. 只能保证数据库用户有选择、插入、更新、删除权限

Answer 1

InnoDB是自动的：

在InnoDB中，您根本不需要锁定任何东西，因为MySQL运行在可重复读取事务隔离级别。当您开始一个事务，然后选择某件事情时，启动了第一个SELECT事务，因为这是可重复读取的，在提交之前，您对数据的视图将是一致的和不变的。

这意味着在InnoDB中开始、选择、选择、提交可重复读取与选择、选择不同。

MyISAM是表锁：

在MyISAM中，您可以在正在读取的表上放置读锁，并在要写入的表上设置读/写锁。用锁表增量地构建锁列表是不可能的，因此必须在一次访问中锁定要触摸的所有内容，并且必须在UNLOCK TABLES之前无法访问未锁定的所有锁。锁表需要额外的特权，在MyISAM中提供没有锁优先级的SELECT priv是没有意义的。

泛型是乐观锁定：

在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