Oracle Database Discussions

Hi.

I have a enqueue locking problem with a 9.2.0.4 database.

The application code makes a lot of use of SELECT FOR UPDATE, and also the users need to delay commits whilst they look at data in another system. So there is always plently of locks on this system. This is not usually a problem because users update their own rows and individual updates should only require RX locks.

I have seen recently though, sessions having to wait for X locks on rollback segment blocks.

Session 64 has these locks:
Locks:
DML/DATA ENQ H: RS R: NONE - LICENCE_CLAUSES
DML/DATA ENQ H: RS R: NONE - LICENCE_PACKAGE_NOTES
DML/DATA ENQ H: RS R: NONE - LICPKG_MKT_REVENUES
DML/DATA ENQ H: RX R: NONE - LIC_PKG_MATS
DML/DATA ENQ H: RX R: NONE - LICENCE_PRODUCTIONS
DML/DATA ENQ H: RX R: NONE - LICPKG_AGR_COSTS
DML/DATA ENQ H: RS R: NONE - TX_SLOTS
DML/DATA ENQ H: RX R: NONE - FOR_TERRITORIES
DML/DATA ENQ H: RX R: NONE - SELECTED_LANGUAGES
DML/DATA ENQ H: RX R: NONE - PKGMKT_EXP_COSTS
DML/DATA ENQ H: RX R: NONE - LICENCES
DML/DATA ENQ H: RS R: NONE - EXTERNAL_TAPE_REQS
DML/DATA ENQ H: RX R: NONE - LICPRD_MKT_TERMS
DML/DATA ENQ H: RX R: NONE - COSTING_TERRITORIES
DML/DATA ENQ H: RX R: NONE - PKGMKT_PROD_COSTS
DML/DATA ENQ H: RX R: NONE - SELECTED_TERRITORIES
DML/DATA ENQ H: RX R: NONE - LICENCE_PACKAGES
DML/DATA ENQ H: RS R: NONE - LICENCE_TX_SLOTS
DML/DATA ENQ H: RX R: NONE - MARKET_GATEWAY
DML/DATA ENQ H: RX R: NONE - SELECTED_MEDIA
DML/DATA ENQ H: RX R: NONE - LICENCE_DIARIES
DML/DATA ENQ H: RS R: NONE - LICPRD_MKT_REVENUES
DML/DATA ENQ H: RX R: NONE - RIGHTS_COSTINGS
DML/DATA ENQ H: RX R: NONE - PKGMKT_AGREE_COSTS
DML/DATA ENQ H: RX R: NONE - AVAILABILITY_REQUESTS
DML/DATA ENQ H: RX R: NONE - PROD_LICENCE_DATES
DML/DATA ENQ H: RX R: NONE - LICPKG_PMG_COSTS
DML/DATA ENQ H: RS R: NONE - MEMO_LINES
DML/DATA ENQ H: RS R: NONE - LIC_PKG_OTHERCOSTS
DML/DATA ENQ H: RS R: NONE - MAT_FUL
DML/DATA ENQ H: RX R: NONE - DISTINCT_FOR_TERRS
DML/DATA ENQ H: RX R: NONE - LIC_PROD_MATS
DML/DATA ENQ H: RX R: NONE - PKGMKT_BASE_COSTS
DML/DATA ENQ H: RX R: NONE - PKGMKT_MEDIA_COSTS
TRANSAC ENQ H: X R: NONE - RS+SLOT#720921 WRP#40491
DML/DATA ENQ H: RS R: NONE - PUBLICITY_REQS
DML/DATA ENQ H: RS R: NONE - LIC_PROD_TX_RUNS
DML/DATA ENQ H: RS R: NONE - LIC_PAYMENT_ALLOCATIONS
DML/DATA ENQ H: RX R: NONE - LICPKG_MKT_TERMS

Other sessions show ...
Session Waits:
WAITING: enqueue
Locks:
DML/DATA ENQ H: RS R: NONE - LICENCES
TRANSAC ENQ H: NONE R: X - RS+SLOT#720921 WRP#40491

I need to understand why users updating different rows are holding each other up.

It would be very useful to have a method of investigation to ascertain the blocks involved in the undo contention.

If anyone understands this area of Oracle and could enlighten me, it would be much appreciated.

Here's some session info:

SQL> @locked_row2
Enter value for dbsid: 19

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
------------- -------------- --------------- ------------- ------------------
LICENCES
728779 12 230040 7 AACx7LAAMAAA4KYAAH

SQL> /
Enter value for dbsid: 86

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
------------- -------------- --------------- ------------- ------------------
LICENCES
728779 12 230040 7 AACx7LAAMAAA4KYAAH

SQL> /
Enter value for dbsid: 64

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
------------- -------------- --------------- ------------- ------------------
PKGMKT_EXP_COSTS_I1
1159203 12 203672 0 AAEbAjAAMAAAxuYAAA

Thanks in advance.