并发过程在数据库中插入数据 [英] Concurrent process inserting data in database

问题描述

考虑postgres数据库中的以下架构.

Consider following schema in postgres database.

CREATE TABLE employee
(
  id_employee serial NOT NULL PrimarKey,
  tx_email_address text NOT NULL Unique,
  tx_passwd character varying(256)
)

我有一个Java类，它可以跟随

I have a java class which does following

conn.setAutoComit(false);

ResultSet rs = stmt.("select * from employee where tx_email_address = 'test1'");
if (!rs.next()) {
    Insert Into employee Values ('test1', 'test1');
}
ResultSet rs = stmt.("select * from employee where tx_email_address = 'test2'");
if (!rs.next()) {
    Insert Into employee Values ('test2', 'test2');
}
ResultSet rs = stmt.("select * from employee where tx_email_address = 'test3'");
if (!rs.next()) {
    Insert Into employee Values ('test3', 'test3');
}
ResultSet rs = stmt.("select * from employee where tx_email_address = 'test4'");
if (!rs.next()) {
    Insert Into employee Values ('test4', 'test4');
}

conn.commit();
conn.setAutoComit(true);

这里的问题是，如果上述事务中有两个或多个并发实例试图写入数据.只有一个事务最终将成功，其余事务将抛出SQLException唯一键约束冲突".我们如何解决这个问题.

The problem here is if there are two or more concurrent instance of the above mentioned transaction trying to write data. Only one transaction would eventually succeeds and rest would throw SQLException "unique key constraint violation". How do we get around this.

PS:我只选择了一个表和简单的插入查询来演示该问题.我的应用程序是基于Java的应用程序，其唯一目的是将数据写入目标数据库.并且可能存在并发进程，并且很有可能某个进程试图在同一数据中写入数据(如上例所示).

PS: I have chosen only one table and simple insert queries to demonstrate the problem. My application is java based application whose sole purpose is to write data to the target database. and there can be concurrent process doing so and there is very high probability that some process might be trying to write in same data(as shown in example above).

推荐答案

一种解决方案是使用表级排他锁，使用命令

A solution is to use a table level exclusive lock, locking for write while allowing concurrent reads, using the command LOCK. Pseudo-sql-code:

select * from employee where tx_email_address = 'test1';
if not exists
   lock table employee in exclusive mode;
   select * from employee where tx_email_address = 'test1';
   if still not exists //may be inserted before lock
      insert into employee values ('test1', 'test1');
      commit; //releases exclusive lock

请注意，使用此方法将阻止所有其他写入，直到释放锁为止，从而降低了吞吐量.

Note that using this method will block all other writes until the lock is released, lowering throughput.

如果所有插入都依赖于父行，那么更好的方法是只锁定父行，序列化子插入，而不是锁定整个表.

If all inserts are dependent on a parent row, then a better approach is to lock only the parent row, serializing child inserts, instead of locking the whole table.

这篇关于并发过程在数据库中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！