Hello,

I have a question concering update statements and locks.

I executed a statement like that

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.


Harry

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?

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.

???