Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010

    Unanswered: Update Select and locking


    I have a question concering update statements and locks.

    I executed a statement like that

    update table set field = NVL((select field from othertable as cart where field= othertable.field and ....)," ")
    What I got is a mail from our DB Admin, that says that I'm holding 4.798.581 locks and that I should avoid such large transactions because the transaction log will overflow and prevent other statements from being executed.

    So my question:
    Is there an implicit transaction created? I assumed that there will be one row locked, updated and then released again.

    Can please anybody explain what was going on while executing this statement?

    Thank you.


  2. #2
    Join Date
    Sep 2010
    Germany, Brunswick
    Which table had the locks? The "table", "othertable" or both?
    Your DBA should know/find out this.

    If you update a lot of rows and nobody else works with this table now, its better to lock the table in exclusive mode:
    lock table tab1 in exclusive mode;

    But that should not cause the problem...

    Whats your isolation level?

  3. #3
    Join Date
    Sep 2010
    Sorry for the late answer, but I had my summerholiday .
    I#m actually not quite sure what kind of locking they use. Our admin is out of office at the moment.

    What I dont know, is if the query is somekind of "atomic". When I start the update, is there an transaction started? Will there be a rollback if one of the updates fails? I think no. But if not where do all the locks come from?

    I thought one row is getting locked, updated and then the lock being released.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts