Home » RDBMS Server » Performance Tuning » Column Level Locking?
Column Level Locking? [message #65812] Fri, 31 December 2004 05:38 Go to next message
Shikha
Messages: 36
Registered: January 2002
Member
I had a long standing update statement which I had to cancel. But the thread is still open. And the status after killing the session is still showing KILLED.

Now, when I query this table, I get records but certain records are not being fetched. Why?

And in fact some columns in these records get fetched
only. Why?

For example:

SQL>
SQL> SELECT MAT_KEY FROM MATERIALS
2 WHERE MAT_KEY = '9263827';

PK
----------
9263827

1 row selected.

real: 40

SQL> SELECT MAT_NO FROM MATERIALS
2 WHERE MAT_KEY = '9263827';

I cancelled it after about 15 seconds

no rows selected

real: 16345
SQL>

What could be wrong ? Is it not a records level locking if at all?

Thanks,

Shikha
Re: Column Level Locking? [message #65813 is a reply to message #65812] Sun, 02 January 2005 07:03 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First off: the session you killed is probably rolling-back the work it's done. The update took a long time, the rollback could take at least that much !

Secondly, there is no such thing as column-level locking or whatever.
If you would be hitting a lock in the second query, you'd either get a message stating you were locked (and added nowait) or, well, you would be locked and see nothing.
Maybe you could do an explain plan on both queries ? Looks like the table-access is very slow, whereas the index is accessible. I assume there is an index on mat_key, which would mean that the first query has no need to access the table itself.
Wait till the db has come back to normal, after rolling back and then try again.

hth
Previous Topic: Orale Hints?
Next Topic: Slow Archiving
Goto Forum:
  


Current Time: Tue Sep 29 10:12:47 CDT 2020