Lock's Type

[leandroaugustos]

Friends,

what does TYPE mean on Locks sheet on the Sessions page ???

Example: Type "TM"

Thanks a lot.

 

[Marco Kalter]

From the "Oracle9i Database Performance Tuning Guide and Reference":

Common Lock Types

Several common locks are described in this section.

TX - Row Transaction Lock
This lock is required in exclusive mode (mode 6) to change data.
One lock is acquired for each active transaction. It is released when the transaction ends due to a commit or rollback.
If a block containing the row(s) to be changed does not have any ITL (interested transaction list) entries left, then the session requests the lock in shared mode (mode 4). It is released when the session gets an ITL entry for the block.
If any of the rows to be changed are locked by another session, then locking session's transaction lock is requested in exclusive mode. When the locking transaction ends, this request ends, and the rows are covered under the requesting session's existing TX lock.
The lock points to the rollback segment and transaction table entries for the transaction.
Do the following to avoid contention on this enqueue:
To avoid contention on TX-6 enqueues, review the application.
To avoid contention on TX-4 enqueues, consider increasing INITRANS for the object.

TM - DML Lock
This lock is required in exclusive mode for executing any DDL statements on a database object; for example, lock table in exclusive mode, alter table, drop table.
This lock is also acquired in shared mode when executing DML statements such as INSERT, UPDATE, or DELETE. This prevents other sessions from executing a DDL statement on the same object concurrently.
For every object whose data is being changed, a TM lock is required.
The lock points to the object.
To avoid contention on TM enqueues, consider disabling the table lock for the object. Disabling the table lock prevents any DDL from executing on the object.

ST - Space Transaction Lock
There is only one lock for each database (not instance).
This lock is required in exclusive mode for any space management activity (creation or dropping any extents) except with locally managed tablespaces.
Object creation, dropping, extension, and truncation all serialize on this lock.
Most common causes for contention on this lock are sorting to disk (not using true temporary tablespaces) or rollback segment extension and shrinking.
Do the following to avoid contention on this enqueue:
Use true temporary tablespaces, utilizing tempfiles. Temporary segments are not created and dropped after every sort to disk.
Use locally managed tablespaces
Size rollback segments to avoid dynamic extension and shrinking, or use automatic undo management.
Avoid application practices that create and drop database objects.

UL - User Defined Locks
Users can define their own locks.

See Also:
Oracle9i Database Concepts for more information on locks

 

[Christo Kutrovsky]

I am using the following queries to find locks in SESSIONS, you can set the tabs and maybe you will like them (just copy/paste them):

Locks (holding):
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(lmode)) held,
decode(request,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(request)) requested,
o.owner as owner_or_type, o.object_Name as NAME
from v$lock l, sys.all_objects o
where type = 'TM'
and l.sid = :sid
and o.object_id = l.id1
union all
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, '4 WAIT for ITL free',
5, 'S/Row-X (SSX)',
6, '6 ITL entry',
to_char(lmode)) held,
decode(request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
to_char(request)) requested,
'ROLLBACK' as owner_or_type, r.name as NAME
from v$lock l, v$rollname r
where type = 'TX'
and l.sid = :sid
and r.usn = trunc(l.ID1/65536)
union all
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(lmode)) held,
decode(request,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(request)) requested,
'LOCK NAME' as owner_or_type, nvl(al.name, l.id1) as NAME
from v$lock l, sys.dbms_lock_allocated al
where type = 'UL'
and l.sid = :sid
and al.lockid (+) = l.id1

-----------------------------------------------

Locks (blocked by - slow on big systems):
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'no',
1, 'BLOCKED',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(lmode)) held,
decode(request,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(request)) requested,
'BY SID' as owner_or_type, to_char(SID) as NAME
from v$lock
where (id1, id2) in (select id1, id2 from v$lock where sid = :sid and request 0)
and block = 1
and sid :sid
union all
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'no',
1, 'BLOCKING',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(lmode)) held,
decode(request,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(request)) requested,
'SID WAITING ON ME' as owner_or_type, to_char(SID) as NAME
from v$lock
where (id1, id2) in (select id1, id2 from v$lock where sid = :sid and block = 1)
and request 0
and sid :sid

Hope they help

If you like them (any of you) post a reply please.

 

[ScottMattes]

I was looking for something like this, thank you.

Not being a DBA type, is there some way to modify the Locks - Holding query to not need DBMS_LOCK_ALLOCATED?

Thank you.