Re: What to do with this situation?

: I wanna know how Oracle & PL/SQL deals with a situation like this.
: And how to prevent this?

1. Pessimistic locking - both users SELECT FOR UPDATE with the NOWAIT option the row(s) that they wish to update. Only one user will succeed, the other will get an ORA message.
2. Optimistic locking - Both users SELECT the rows in the normal way and there is some sort of a check done when they commit to see that they are no overwriting any other updates. A simple counter on the rows should be sufficient. The updates will only be allowed if the counter is the same on the table as in the data that the user is using to update the table with - actually one less as they should increment the counter on every update. If the table and data counts are different you do not allow the user to commit the record as someone else go there first. Can be coded in packages or triggers or elsewhere in PL/SQL.

Both mechanisms have their drawbacks : 1) A user could lock some data and then go to lunch; thus preventing anyone doing anything but reading the data; 2) users may not like updating records and only finding out at commit time that someone has got there first.

Guy Harrison's 'Oracle SQL : High-Performance Tuning' book ahs some more details on the above.

Locking the entire table is not required and is a waste of resources. Use the row-level locking that Oracle gives you via the SELECT .. FOR UPDATE cluase.

The NOWAIT clause is also a very useful tool as it means that DML transactions can exit immediately rather than sit there waiting for a lock to be released - just remember to correctly handle the resulting error/exception. Also remember that whatever locking mechanisms you choose to use - table or row - that Oracle will always allow others read access to the data. You would have to do some of your own coding to prevent users from reading locked data - ie catching the NOWAIT error and translating that to a relevant action and/or message.

IAP

--
In an attempt to reduce junk email I use an invalid 'From' address.
My correct email address can be determined by replacing 'not.valid' with 
'value.net'