READLOCK=EXCLUSIVE Option
A READLOCK option is to set READLOCK to EXCLUSIVE.
Here is an example where controlling the shared lock locking level is necessary.
• User1 submits a multiple query transaction that retrieves data into a table field that the user is allowed to change before writing changes back into the table.
• At the same time, User2 submits a multiple query transaction to retrieve the same set of data into his table field, makes changes to the data, and writes the changes back to the table.
• User1 and User2 will deadlock. Each is waiting for the other to finish and release the shared lock, so that each one can get an exclusive lock to make changes.
If the retrievals and changes had not been done with a multiple query transaction, no deadlock has occurred, because the shared locks are released before the requests for exclusive locks are made. But the exclusive lock transaction is necessary to prevent data from changing between the times you read the data and write to it.
It is preferable to exclusively lock the data when reading it into the table field, so that no other user can also retrieve the same set of data until the first user is finished.
If it is likely that User1 will hold locks for a long time before committing changes, then alternative methods of "optimistic locking" should be investigated.
Set READLOCK=EXCLUSIVE
To set READLOCK to EXCLUSIVE, use the following statement:
set lockmode session where readlock = exclusive;