Duplicate inserts

Question

I'm having following issue with duplicated inserts in case of concurrent requests.

I have two tables:

create table tbl (
  val varchar2(1000)
);

create table tbl2 (
  val varchar2(1000)
);

I need to insert some value in table tbl2 only in case if table tbl is empty. So, in my Java code I do in transaction with isolation level = READ COMMITED:

//start transaction
int result = jdbcTemplate.queryForInt("select count(*) from tbl");
if (result == 0) {
  int update = jdbcTemplate.update("insert into tbl2(val) values(?)", "di" + UUID.randomUUID().toString());
}
//end transaction

The problem here is: Somebody could actually insert data between if (result == 0) and my update statement. So I'll have duplicate entries.

My example is oversimplified, but my real case is much more complicated, but the basic idea is the same: I need to make several selects from within of Javacode before inserting.

So, how is it possible to avoid such kind of situation(I'm interested in db side solution and in java side solution)?

P.S. Database is Oracle.

actually I use 2 different applications to simulate this case: java code from IDE, where I put breakpoint on a line "if (result == 0)" and SQL developer where I insert new line and do commit — hades, Jul 13 '12 at 22:58

score 2 · Answer 1 · answered Jul 13 '12 at 23:26

2

I think the best way to handle such a situation might be in database level. You can use a query like:

insert into tbl2 values(xx) where not exists (select 1 from tbl1)

So your query might become something like this

int update = jdbcTemplate.update("insert into tbl2(val) values(?) where not exists (select 1 from tbl1)", "di" + UUID.randomUUID().toString());

answered Jul 13 '12 at 23:26

Sujay

6,753
2
30
49

Good point. But unfortunately my real code uses Hibernate in conjunction with spring jdbc templates. – hades Jul 14 '12 at 10:21
Well, I've tried your solution. But still I'm able to reproduce my case – hades Jul 14 '12 at 12:05

score 1 · Answer 2 · answered Jul 13 '12 at 22:52

1

Not quite sure why you would want to do what you are doing, but you could lock the table first.

LOCK TABLE tbl IN SHARE MODE;

Remember to COMMIT; to release the locks.

answered Jul 13 '12 at 22:52

AW101

1,620
14
15

2

p.s. Choose your lock mode carefully: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9015.htm – AW101 Jul 13 '12 at 22:54
Is there some other good alternative way to do this stuff without locking table? – hades Jul 13 '12 at 23:03
P.S. I want to do that, because I for instance want to make new Order only in case, when there's enough Items of specified types... – hades Jul 13 '12 at 23:06
If you want to stop someone inserting a row it's the only way, although I wouldn't recommend it. – AW101 Jul 13 '12 at 23:07
1

Another way might be to add a BEFORE INSERT trigger to tbl2 that checks if the insert is allowed or not. – AW101 Jul 13 '12 at 23:18
Will usage of the trigger guarantee me that in 100% of cases this check (select on another table) will guarantee, that no dupliacates will be made? – hades Jul 14 '12 at 15:23
You could use triggers on both tables to enforce that. Though if you are struggling to use normal integrity constraints maybe your data model is wrong. – AW101 Jul 14 '12 at 21:13

Duplicate inserts

2 Answers2