Shulou(Shulou.com)05/31 Report--

This article will share with you about the difference between v$lockv and $locked_object in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. introduction

Once in the work to modify the data in the table sj_affair, you need to disable the trigger on the table. The result cannot be disabled, and the following error is reported:

ORA-00054: resource busy and acquire with NOWAIT specified

Obviously, the table is locked, so it is intended that kill unlocks the session of the table. The steps are as follows:

1. Find out the session id,serial# that locks the table

SELECT o.object_name,s.sid, s.serial#

FROM v$locked_object l, dba_objects o, v$session s

WHERE l.object_id = o.object_id

AND l.session_id = s.sid

AND o. Objectkeeper nameplate SJP AFFAIR'

2.kill dropped the session

Alter system kill session 'sid, serial#'

Kill queried again after dropping the session and found that there was no lock on the table. So I tried to disable the trigger again, and the result was strange, but it still couldn't be disabled.

Is the lock restored? Check again and find that there is no lock. Why is that?

After searching a lot on the Internet, I found a useful piece of information. The author used v$lock view instead of v$locked_object view to query the lock. Change to query v$locd view:

SELECT o.object_name,s.sid, s.serial#

FROM v$lock l, dba_objects o, v$session s

WHERE l.id1 = o.object_id

AND l.sid = s.sid

AND o. Objectkeeper nameplate SJP AFFAIR'

Then I checked again and found that there was still a lock on SJ_AFFAIR. So kill again, and then disable the trigger and it works.

II. V$LOCK and V$LOCKED_OBJECT

Both V$LOCK and V$LOCKED_OBJECT are used to query locked tables, but they are not the same. Let's first look at their main fields:

1.v$lock

Sid: session SID, usually associated with v$session.

Type: lock type, TM represents table lock or DML lock, TX represents row lock or transaction lock, and UL represents user lock. We mainly focus on TX and TM locks, and the other locks are system locks, which will be released automatically soon. Row locks are not stored separately, and table-level shared locks are required before row-level locks.

Lmode: the mode of lock for session persistence.

SS (row-level shared locks, other objects can only query these rows of data); 3=Row-X (SX, row-level exclusive locks, DML operations are not allowed before commit); 4=Share (shared locks); 5=S/Row-X (shared row-level exclusive locks); 6=Exclusive (exclusive locks)

ID1,ID2: the value meaning of ID1,ID2 varies according to the value of type. For TM lock ID1, it means that the object_id of the locked table can be associated with the dba_objects view to obtain specific table information. The ID2 value is 0. For TX locks, ID1 represents the rollback segment number and transaction slot slot number number occupied by the transaction in decimal value, which is in the form of 0xRRRSSSS _ _ RRRRSS _ _ RRRRS _ RBS _ undo NUMBER,SSSS=SLOT NUMBER,ID2 represents the number of times surrounding the wrap in decimal value, that is, the number of times the transaction slot is reused.

2.V$LOCKED_OBJECT

Session_id: session id. It is usually associated with v$session.

Object_id: identification of the locked object. It is usually associated with dba_objects.

Oracle_username: log in to the oracle user name.

Os_user_name: computer user name such as: Administrator

Locked_mode: the mode of lock for session persistence.

Third, the difference between the two

1.V$LOCKED_OBJECT can only report waiting table-level locks, not waiting row-level locks.

Note: this sentence is said by others on the Internet and has not been verified for the time being. It needs careful consideration. But judging from the problems I have encountered, it seems to be correct. It's just that my problem can no longer be reproduced, so I lose the opportunity to verify it.

2.v$locked_object contains the locked OBJECT in the current DB, while v$lock contains not only the user's, but also the locked object of the system, that is, V$LOCK > v$locked_object

Thank you for reading! This is the end of the article on "what is the difference between v$lockv and $locked_object in the database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.