1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Can two users update the same row at the same time? if so how?

Discussion in 'SQL PL/SQL' started by manoj.vajpee, Dec 18, 2014.

  1. manoj.vajpee

    manoj.vajpee Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    110
    Can two users update the same row at the same time? if so how?
     
  2. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    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.
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    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.