Discussion in 'SQL PL/SQL' started by manoj.vajpee, Dec 18, 2014.
Can two users update the same row at the same time? if so how?
Oracle uses locks (sometimes called enqueues) to control concurrent access to data. A lock gives you temporary ownership of a database resource such as a table or row of data. Thus, data cannot be changed by other users until you finish with it.
Of course it may not be YOU having the initial lock; any user that gets the row first with an update statement will lock it, and then you will have to wait until that transaction is either committed or rolled back at which time another session or transaction can be started against that same row. You can prove this by starting two sessions to the same database, update a row in one session then, from the other session, try to update the same row. The second session will 'hang', as in just sit there, waiting, until a commit or rollback is issued in the first session. At that point the transaction from the second session will continue and lock the row until a commit or rollback is issued.
There is one point that should be brought up. Locks are only put into place when a DML operation is started (i.e. an UPDATE statement is executed). The locks are only held until a transaction-ending operation is performed (i.e. a COMMIT, ROLLBACK, or DDL operation). This is important because in human terms, it is possible for two people to be 'updating the same row at the same time', when in fact it is not the case for the database.
Take for example a database updated by a web interface. Two people pull up the record for employee 'Bob Jones'. Both see the same data for Bob because no changes have been made. The first person makes some changes and submits them. The row is locked, changed, and unlocked. The second person will not see these changes on their screen. The second person makes some changes and submit them. The row is locked, changed and unlocked.
Because the UPDATE and COMMIT operations happen (almost assuredly) in a fraction of a second, the updates performed by the two users may well happen less than a second from each other. In human terms, they were updating the same row at the same time. From the standpoint of the database, they were not.