CODEWITHSUNDEEP
javasqltransactionstransactionscope
hades
hades

Reputation: 1087

Duplicate inserts

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.

Upvotes: 0

Views: 1305

Answers (2)

Sujay
Sujay

Reputation: 6783

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());

Upvotes: 2

AW101
AW101

Reputation: 1640

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.

Upvotes: 1

Related Questions