Database Reference
In-Depth Information
Table 11-2.
TX Lock Contention and Modes
Lock
Mode
Request
Mode
Comment
RAC Equivalent
(request mode)
Possible causes
6
6
Exclusive
KJUSEREX
Classic row-level locking contention. Usually, an
application coding or concurrency issue. Also, check
holder to review why the lock is not released quickly.
6
4
Share
KJUSERPR
1. Block-level concurrency issue (initrans is lower).
2. Concurrent inserts and updates in the presence of a
unique/primary key index or constraint.
3. Updates to column(s) with bitmap indexes.
TM Enqueue Contention
TM resource and enqueue protects table-level concurrency, as we saw in the previous section. To further your
understanding of TM resource contention, I will create TM enqueue contention and review the resource structures.
In Listing 11-6, I explained how TM locks are acquired to implement row-level locking strategy. I will continue
with the same setup as a starting point for this example. While session 12229 is waiting to lock a row, I will try to lock
the table in exclusive mode from a different session connected to instance 3. Since the request mode is incompatible
with other existing locking modes, session 3 will wait for the lock to be available.
REM This listing continues from Listing 11-4.
REM From session 3, we will try to lock the table.
SQL> lock table t1 in exclusive mode;
...
REM From another session, let's review resources and locks.
SQL> SELECT DISTINCT '[0x'
||trim(TO_CHAR(object_id, 'xxxxxxxx'))
||'][0x'
|| trim(TO_CHAR(0,'xxxx'))
|| '],[TM]' res
FROM dba_objects WHERE object_name=upper('&objname');
RES
---------------------------
[0xb6dff3][0x0],[TM]
Output of gv$ges_resource shows that resource is set up in all instances.
SQL> SELECT inst_id, resource_name, master_node
FROM gv$ges_resource WHERE resource_name LIKE '[0xb6dff3][0x0],[TM]%' ;
Inst RESOURCE_NAME MASTER_NODE
---- ------------------------------ -----------
1 [0xb6dff3][0x0],[TM][ext 0x0,0 0
2 [0xb6dff3][0x0],[TM][ext 0x0,0 0
3 [0xb6dff3][0x0],[TM][ext 0x0,0 0
