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?
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.