如何启用多线程/连接修改同一个mysql表？

Question

我有一个程序，有 2 个线程正在运行，每个线程都有自己的数据库 JDBC 连接，它们将访问/修改相同的数据库表 A，如下所示。表A只有2列（id，name），主键是id和name的组合。

statement stmt;

// first delete it if the record has exist in table
stmt.addBatch("delete from A where id='arg_id' and name='arg_name';");

// then insert it to table
stmt.addBatch("insert into A values (arg_id, arg_name);");

stmt.executeBatch();

2 个线程可能会向表中插入相同的数据，并且出现以下异常，

java.sql.BatchUpdateException: Duplicate entry '0001-joey' for key 1
        at com.mysql.jdbc.Statement.executeBatch(Statement.java:708)
        at com.mchange.v2.c3p0.impl.NewProxyStatement.executeBatch(NewProxyStatement.java:743)
        at proc.Worker.norD(NW.java:450)

您知道如何解决此问题吗？谢谢。

问候， 乔伊

Answer 1

为什么不在数据库上引入简单的乐观锁机制呢？

添加版本列并在执行删除或更新事务时跟踪版本号。

您的表看起来像

create table test(
id int not null primary key,
name varchar,
rowversion int default = 0);

每次检索行时，您都应该检索行版本，以便可以

update test set name='new name' rowversion=rowversion+1 where id=id and rowversion=retrieved row version;

对删除执行相同的操作，

delete from test where id=id and rowversion=retrievedRowVersion;

这是一种利用 dbms 并发管理功能的简单机制。检查此链接以获取有关乐观锁定的更多信息 http://en.wikipedia.org/wiki/Optimistic_concurrency_control#示例

这显然只是并发管理的一个非常简单的实现，但您的问题必须考虑到这些。

此外，对于双重插入，您的交易被拒绝的事实是好的，这意味着不会插入重复的键。您应该只处理异常并通知用户发生了什么。

Answer 2

将两个语句包装在一个事务中：

BEGIN;
DELETE FROM a WHERE ...;
INSERT INTO a VALUES (...);
COMMIT;

注意，只要表仅由主键组成，则仅当表最后未修改时才会出现这种冲突；我假设您想要添加更多列，在这种情况下，您应该使用 UPDATE ... WHERE 语法来更改值。

Answer 3

您使用任何类型的同步吗？首先，您需要将修改表的代码包装在：

synchronized(obj)
{
    // code
}

其中 obj 是两个线程都可以访问的对象。
我不知道你的表修改的确切语义，但是如果它们都插入了 id，你还需要保存一个“全局”id 并在每个线程中自动递增它，这样它们就不会得到相同的值。