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.

About these ads

One Response to Did I know that?

  1. Pingback: Prod DBA 2.0 says no** « OraStory

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 72 other followers

%d bloggers like this: