Tuesday, November 8, 2011

Direct Path INSERT requires exclucive lock

There is a table, where we insert rows using serial direct-path INSERT [INSERT /*+ APPEND */].
The INSERT command is executed periodically by a job.
Manually, I run a DELETE command on this table to purge rows no longer needed.
At some point, the job mentioned executes the direct-path INSERT and it gets blocked by the DELETE command with an "enq: TM - contention" lock.

INST_IDSIDID1ID2LMODEBLOCKREQUESTOBJECT_NAMELOCK_TYPEPROGRAM
280167604880310AUD$_BU_IDX2DMLsqlplus@ecdb2 (TNS V1-V3)
2--->82967604880006DMLoracle@ecdb2 (J001)

According to Oracle Database Administrator's Guide 10g Release 2 (10.2), this is normal behavior:

    During direct-path INSERT, the database obtains exclusive locks on the table (or on all partitions of a partitioned table). As a result, users cannot perform any concurrent insert, update, or delete operations on the table, and concurrent index creation and build operations are not permitted. Concurrent queries, however, are supported, but the query will return only the information before the insert operation.

No comments:

Post a Comment