Database read locking

Question

I have a use case where I need to do the following things in one transaction:

1. start the transaction
2. INSERT an item into a table
3. SELECT all the items in the table
4. dump the selected items into a file (this file is versioned and another program always uses the latest version)
5. If all the above things succeed, commit the transaction, if not, rollback.

If two transactions begin almost simultaneously, it is possible that before the first transaction A commits what it has inserted into the table (step 4), the second transaction B has already performed the SELECT operation(step 2) whose result doesn't contain yet the inserted item by the first transaction(as it is not yet committed by A, so not visible to B). In this case, when A finishes, it will have correctly dumped a file File1 containing its inserted item. Later, B finishes, it will have dumped another file File2 containing only its inserted item but not the one inserted by A. Since File2 is more recent, we will use File2. The problem is that File2 doesn't contain the item inserted by A even though this item is well in the DB.

I would like to know if it is feasible to solve this problem by locking the read(SELECT) of the table when a transaction inserts something into the table until its commit or rollback and if yes, how this locking can be implemented in Spring with Oracle as DB.

Answer 1

You need some sort of synchronization between the transactions:

1. start the transaction
2. Obtain a lock to prevent the transaction in another session to proceed or wait until the transaction in the other session finishes
3. INSERT an item into a table
4. SELECT ......
5. ......
6. Commit and release the lock

---

The easiest way is to use LOCK TABLE command, at least in SHARE mode (SHARE ROW EXCLUSIVE or EXCLUSIVE modes can also be used, but they are too restrictve for this case).
The advantage of this approach is that the lock is automatically released at commit or rollback.
The disadvantage is a fact, that this lock can interfere with other transactions in the system that update this table at the same time, and could reduce an overall performance.

---

Another approach is to use DBMS_LOCK package.
This lock doesn't affect other transactions that don't explicitely use that lock.
The drawaback is that this package is difficult to use, the lock is not released on commit nor rollback, you must explicitelly release the lock at the end of the transaction, and thus all exceptions must be carefully handled, othervise a deadlock easily could occur.

---

One more solution is to create a "dummy" table with a single row in it, for example:

CREATE TABLE my_special_lock_table(
   int x
);
INSERT INTO my_special_lock_table VALUES(1);
COMMIT:

and then use SELECT x FROM my_special_lock_table FOR UPDATE
or - even easier - simple UPDATE my_special_lock_table SET x=x in your transaction.
This will place an exclusive lock on a row in this table and synchronize only this one transaction.
A drawback is that another "dummy" table must be created.
But this solution doesn't affect the other transactions in the system, the lock is automatically released upon commit or rollback, and it is portable - it should work in all other databases, not only in Oracle.