Home > Locking, Trouble > TM enqueues and deadlock with one user session

TM enqueues and deadlock with one user session

For quite some time now, I have been thinking about writing some blog items, but never did. A recent blog entry on http://jonathanlewis.wordpress.com/2011/04/08/deadlock prompted me to give it a try.

So the first post is just a repetition of the comment I made:

TM-enqueues for foreign key locking seems to be a well known fact. What I personally would like to know, what other reasons can cause TM-Locks.
A few times already I had issues with them, not involving foreign keys.

Another reason are exclusive lock and the following. Did anyone know, that a single user connected session can cause a deadlock?

on 10.2.0.3 and 11.1.0.7 verified:


DROP TABLE TESTSZ;
CREATE TABLE TESTSZ
( I NUMBER,
  K VARCHAR2(50));
 INSERT INTO TESTSZ
 SELECT MOD(ROWNUM,1000), LPAD(‘X’,40,’Y') FROM DUAL CONNECT BY ROWNUM < 1000000;
 COMMIT;

LOCK TABLE TESTSZ IN EXCLUSIVE MODE;
 INSERT INTO TESTSZ X
 WITH X1 AS  (SELECT /*+ no_PARALLEL(T) MATERIALIZE*/ 2000, K FROM TESTSZ T
 WHERE I=1)
 SELECT * FROM X1;
 rollback;

LOCK TABLE TESTSZ IN EXCLUSIVE MODE;
 set timing on
 INSERT INTO TESTSZ X
 WITH X1 AS (SELECT /*+ PARALLEL(T,4) MATERIALIZE*/ 2000, K FROM TESTSZ T
 WHERE I=1)
 SELECT * FROM X1;
 rollback;

Result of the last insert statement:

INSERT INTO TESTSZ X
*
FEHLER in Zeile 1:
ORA-12801: error signaled in parallel query server P000
ORA-00060: deadlock detected while waiting for resource

Before anyone complains there are more than one session involved,
yes, but the importance is only one “user” connected session.

Moving back to TM locks, in the case above with the factored subquery
in combination with parallel query, the deadlock occurs while the sessions
are trying to get TM-locks.

SID PROGRAM LAST_CALL_ET BLOCKING_SESSION EVENT SECONDS_IN_WAIT
116 sqlplus.exe               48        PX Deq: Parse Reply     3  WAITING
117 oracle@cyber-ora10 (P005) 48        PX Deq: Execution Msg   3  WAITING
128 oracle@cyber-ora10 (P004) 48        PX Deq: Execution Msg   3  WAITING
115 oracle@cyber-ora10 (P003) 48        PX Deq: Execution Msg   3  WAITING
129 oracle@cyber-ora10 (P002) 48  116   enq: TM – contention   48 WAITING
120 oracle@cyber-ora10 (P001) 48  116   enq: TM – contention   48 WAITING
136 oracle@cyber-ora10 (P000) 48  116   enq: TM – contention   48 WAITING

To finish, Oracle support agreed, that this is not supposed to happen. The workaround is to use the inline hint.

And to credit, this originally happened to a co-worker sitting two desks next to me.

Advertisements
Categories: Locking, Trouble
  1. 21/04/2011 at 08:04

    see the additinonal more detailed insight Jonathan Lewis provided to my comment on his site.

  2. 22/04/2011 at 14:12

    In a very simple test, standard deadlock detection already signals after 3 seconds. So there must be some other explanation. Following up, out of curiosity I changed on my test system the setting of the distributed_lock_timeout parameter fomr 60 to 180 and retested after instance bounce.

    On 10.2.0.4, parameter change had no effect:

    SQL> select version from v$instance;
    
    VERSION
    ----------
    10.2.0.4.0
    
    SQL> select name, value from v$parameter where name='distributed_lock_timeout';
    
    NAME                      VALUE
    ------------------------- -----
    distributed_lock_timeout  180
    
    
    SQL> LOCK TABLE TESTSZ IN EXCLUSIVE MODE;
    
    Tabelle(n) wurde(n) gesperrt.
    
    SQL> set timing on
    SQL> INSERT INTO TESTSZ X
      2   WITH X1 AS
      3    (SELECT /*+ PARALLEL(T,4) MATERIALIZE*/ 2000, K FROM TESTSZ T WHERE I=1)
      4   SELECT * FROM X1;
    INSERT INTO TESTSZ X
    *
    FEHLER in Zeile 1:
    ORA-12801: error signaled in parallel query server P000
    ORA-00060: deadlock detected while waiting for resource
    
    Abgelaufen: 00:01:04.66
    

    So there must be some more to this, than I currently undertstand. But whatever it is, I don’t think it is that terribly important, so I leave it as it is.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: