| Column Level Locking? [message #65812] |
Fri, 31 December 2004 05:38  |
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  |
Frank
Messages: 7901
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
