Question How to Know That A Record in Oracle Table Is Locked?

[phoebe_ccy]

Hi,

I am currently working on an Inventory system. In Program A, I need to allocate Item X for a process. Therefore, I need to lock the item in Inventory table so that other program cannot use Item X anymore.

Here is how I locked Item X in Inventory table (from program A):

SELECT * FROM FIINVTBL
WHERE INV_ITEM = 'X'
FOR UPDATE;

At the same time, Program B is trying to get Item X too. How can I know that records for Item X in FIINVTBL is locked? I need to prompt out a message in Program B so that alert user. How can I know that the record is locked when I select the item from inventory table?

Please help.

 

[cjard]

You can FOR UPDATE WITH NOWAIT and oracle will throw an exception as soon as you try to acquire a locked resource

Oracle Deadlocks

That said, I prefer optimistic locking as implemented in .net; you let any user download any row, but when updating it is a condition of the update that all the row values match the old values. If it's not the update will affect 0 rows and .net throws a ConcurrencyViolation exception. At this point you can tell your user about it and make them decide to:

keep their changes, overwriting other
keep other changes, discarding theirs
merge changes, preferring other values (where both values are updated, theirs are kept)
merge changes, preferring my values (where both values are updated, theirs are kept)

or you can take that decision for them (depends on their technical level)


When you make a tableadapter in your dataset, if the Use Optimistic COncurrency is ticked in the advanced options, then the UPDATE statement gets much more complicated... but it's done by Visual Studio so don't worry about it

 

[cjard]

Using Optimistic Concurrency