Did I know that?

On an internal chat channel a while back, someone wondered if, as part of a process, they could lock a table, do some stuff and truncate the table as part of the same transaction, with the idea that with two concurrent sessions, the second session would wait at the lock table stage before progressing.

At the end of the day, it doesn’t really matter why they wanted to do this, whether their thinking was flawed or whether there were better approaches, because the reason why this particular approach can’t work is a simple illustration of a very basic Oracle concept.

Session A

SQL>
SQL> create table truncate_me
  2  (col1 number);

Table created.

SQL> lock table truncate_me in exclusive mode;

Table(s) Locked.

Session B

SQL>
SQL>
SQL>
SQL> lock table truncate_me in exclusive mode;

... waiting ...

Session B waits, naturally.

Back to Session A:

SQL> truncate table truncate_me;
truncate table truncate_me
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL>

Eh? What was that?

Back to Session B – which is no longer waiting:

SQL> lock table truncate_me in exclusive mode;     <-- Still previous statement

Table(s) Locked.

SQL>

Maybe when you see this, you’re like me and there’s a brief pause while you look up and to the side (in what hopefully looks meaningful though) before you “remember” that Oracle issues a COMMIT both BEFORE and AFTER a DDL statement.

Then, maybe like me, you start questioning whether you really knew that? You used to know that, right? Maybe you had forgotten? Maybe you can’t tell if you had known and had forgotten, you still knew but you’re just a bit slow (and getting slower all the time) or you had never known…

Tom Kyte always says that he learns something new every day.

I wish I did.

Yesterday I learned lots of stuff while looking at 10104 trace file information – I enjoyed it. But how much of it will stick?. And for how long?

I often relearn something every day.

But that’s just a positive spin isn’t it?

A positive spin on finding out I’m forgetting stuff all the time.

And probably, most of the time, I’m forgetting stuff and not finding out.

Which is doubly bad.

Advertisements