1

I have a table with the following fields - list_name, version, list, message, creator. Each update on the table for a specific list_name essentially adds a new version of that list_name to the table along with the updated list.

My update query prepared statement looks like:

"INSERT INTO tableA (list_name, version, list, message, creator)" 
 + " SELECT tableA.list_name, tableA.version+1, ?, ?, ? FROM tableA ta" 
 + " JOIN (SELECT MAX(version) version, list_name FROM tableA WHERE list_name=? GROUP BY list_name) max_version" 
 + " ON ta.version=max_version.version and ta.list_name=max_version.list_name" 
 + " WHERE ta.version=?";

Consider a scenario where one client A reads version 1 as the latest version and tries to insert a new row with version 2. Meanwhile another client B sees version 1 as the latest as well and effectively overwrites the updates of client A. How to avoid such races? I read somewhere that SELECT FOR UPDATE causes serious performance issues. Is there an alternative approach for my use case?

deGee
  • 781
  • 1
  • 16
  • 34

1 Answers1

1

I am not sure that SELECT ... FOR UPDATE causes serious performance issues in Oracle generally speaking.

In Oracle if you want to use pessimstic locking you need to use SELECT ... FOR UPDATE. But you can do optimistic locking without SELECT ... FOR UPDATE.

See this discussion that details optimistic locking algorithm: Optimistic Locking by concrete (Java) example (it's for Java but you can do it without Java: basically you need to implement some timestamp column in your table and to check in each transaction that this timestamp has not been updated by a concurrent transaction).

In your case it is not such a locking issue: you need to avoid duplicates in a table. If the primary key is auto incremented it cannot be used and you need to define a unique constraint to avoid duplicates.

For example:

alter table tableA add constraint unique_cons unique(list_name, version);

Several concurrent sessions cannot insert rows having same values for a given (list_name, version) .

  • the first transaction can do it: there will be an exclusive lock for this row
  • all concurrent transactions trying to insert rows with same (list_name, version) are going to be blocked
  • after first transaction commit all other concurrent transactions which where waiting on the first one will get an error ORA-00001: unique constraint (...) violated.
pifor
  • 7,419
  • 2
  • 8
  • 16
  • In my use case, update doesn't mean updating the same row. It appends a new row with an updatedVersion (latestVersion+1). Chances are that two threads end up inserting new rows with the same latest version and over-writing each other's updates. – deGee May 22 '20 at 06:18
  • Do you have a primary key ? If yes this should not happen. – pifor May 22 '20 at 06:50
  • The primary key is an auto-increment id. – deGee May 22 '20 at 08:29
  • Can you please explain why the above race won't happen. Sorry, but the resolution is not very clear to me – deGee May 22 '20 at 08:30