This discussion is archived
10 Replies Latest reply: Feb 21, 2013 12:21 AM by 992432 RSS

TM / TX Locks ( Tom Kyte and Oracle Docu)

538022 Newbie
Currently Being Moderated
Hello guys,
i have found a question about dml_locks on "Ask Tom" and was a little bit confused.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25323841260334

=> The answer of Tom:
do you have my book "Expert one on one Oracle"
the TX is not a row lock, TM is the "row lock" actually.

And now the oracle documentation:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref2066

=> Row Locks (TX)
Row-level locks are primarily used to prevent two transactions from modifying the same row. When a transaction needs to modify a row, a row lock is acquired.

=> Table Locks (TM)
Table-level locks are primarily used to do concurrency control with concurrent DDL operations, such as preventing a table from being dropped in the middle of a DML operation

I understand Toms example with the transaction and the update.. but what is his statement "the TX is not a row lock, TM is the "row lock" actually" about?
The oracle documentation says that a TX is a row lock and used for data protection (consistency) or does he mean the different lock types - they are documented in the "Summary of Table Locks" (oracle documentation link from above)

Regards
Stefan
  • 1. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    mbobak Oracle ACE
    Currently Being Moderated
    Hi Stefan,

    I also don't understand why Tom said what he said...I think it's wrong....but Tom's not usually wrong...so...

    Anyhow, I dropped him a note and asked him to weigh in here, to clarify his comment on AskTom.

    Hopefully, we'll hear from him soon,

    -Mark
  • 2. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    tkyte Employee ACE
    Currently Being Moderated
    the TX lock represents "your transaction", it is "you"

    the TM lock is a lock on the table itself.

    when another transaction enqueues on you, it'll enqueue on your TX lock, the but fact that you have rows locked in table T is indicated by the TM lock.

    I guess it is a matter of perspective/semantics. There is but one TX lock for "you", there will be a TM lock held by you on tables you've modified - and that table lock will generally be of the Row Exclusive Table Lock (RX) type - which indicates you've actually made updates to rows in table (whereas the row share - RS - lock indicates you've reserved rows for update via the select for update)

    You cannot actually in truth see ANY ROW LOCKS in Oracle - they are not manifested in a memory structure to be queried up.

    All you can do is look for TM locks held RS or RX and then make the claim "that transaction has some rows locked in that table"


    So, the TX lock - you had one (in the above example) without touching a row in any table. When you do touch a row in a table, you get a TM lock on that table - that indicates you have row locks in there.

    It is true (and probably why they wrote it the way they did in the concepts) that the TX lock is what other sessions will enqueue on (wait on). That single TX lock is what everyone else will wait one regardless of which row(s) they are trying to gain access to if you are blocking them.

    That is why, by the way, a rollback to savepoint - which releases the locks gathered since the savepoint - does NOT release transactions waiting on those locks. Try this script to see what I mean by that...

    change ops$tkyte to your test schema of course...


    connect /
    set linesize 1000
    column nm format a10

    drop table t;
    drop user a cascade;
    drop user b cascade;
    drop user c cascade;
    grant create session to a identified by a;
    grant create session to b identified by b;
    grant create session to c identified by c;

    create table t ( x int );
    insert into t values ( 1 );
    commit;
    grant all on t to public;

    prompt in another session, connect as A and:
    prompt savepoint foo;;
    prompt update ops$tkyte.t set x = x+1;;
    prompt then in another session, connect as B and:
    prompt update ops$tkyte.t set x = x+1;;
    prompt then come back here and hit enter....
    pause

    select
    (select username from v$session where sid=a.sid) blocker,
    a.sid,
    ' is blocking ',
    (select username from v$session where sid=b.sid) blockee,
    b.sid
    from v$lock a, v$lock b
    where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2
    /
    select sid,
    (select username from v$session s where s.sid = v$lock.sid) uname,
    type, id1, id2,
    (select object_name from user_objects where object_id = v$lock.id1) nm
    from v$lock
    where sid in (select sid from v$session where username in ('A','B','C',user) )
    /

    prompt in session A issue:
    prompt rollback to foo;;
    prompt note that B is blocked, then come back here and hit enter:
    pause

    select
    (select username from v$session where sid=a.sid) blocker,
    a.sid,
    ' is blocking ',
    (select username from v$session where sid=b.sid) blockee,
    b.sid
    from v$lock a, v$lock b
    where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2
    /
    select sid,
    (select username from v$session s where s.sid = v$lock.sid) uname,
    type, id1, id2,
    (select object_name from user_objects where object_id = v$lock.id1) nm
    from v$lock
    where sid in (select sid from v$session where username in ('A','B','C',user) )
    /

    prompt Now in another session log in as C and:
    prompt update ops$tkyte.t set x = x+1;;
    prompt note: it does not block - b is still blocked.
    prompt then in session A issue:
    prompt commit;;
    prompt and come back here an hit enter
    pause

    select
    (select username from v$session where sid=a.sid) blocker,
    a.sid,
    ' is blocking ',
    (select username from v$session where sid=b.sid) blockee,
    b.sid
    from v$lock a, v$lock b
    where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2
    /
    select sid,
    (select username from v$session s where s.sid = v$lock.sid) uname,
    type, id1, id2,
    (select object_name from user_objects where object_id = v$lock.id1) nm
    from v$lock
    where sid in (select sid from v$session where username in ('A','B','C',user) )
    /

    prompt When A committed, B was released but instantly blocked by C...
    prompt and that is where we are now...
  • 3. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    mbobak Oracle ACE
    Currently Being Moderated
    Ok, I think I understand what you're getting at, Tom. And I agree that it is a matter of semantics, at least to some degree.

    I'd argue that the TM lock is a 'table' lock and that TX is 'row' lock.

    I do so for three reasons:

    1.) TM lock is taken once against a given table, regardless of how many rows are locked in that table.
    2.) There is no relationship between TM lock and any row-level lock.
    3.) Even though there's no query-able structure in Oracle that can show you a row-level lock, they can be seen in block dumps, and are associated w/ the TX lock. A specific locked row in the row directory has it's lock byte pointed at an ITL slot, which in turn points to a specific undo segment, slot and sequence, which is the very essence or definition of a TX lock.

    The third point is my strongest argument for the "TM lock is table level, TX lock is row level" argument.

    -Mark
  • 4. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    tkyte Employee ACE
    Currently Being Moderated
    I'd be in favor of not actually calling TX a lock at all - but rather "the thing that other things enqueue on"

    The row locks are associated with the transaction - the TX 'lock' is really the 'transaction'.

    But I see your point
  • 5. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    mbobak Oracle ACE
    Currently Being Moderated
    Agreed. A TX is really a transaction enqueue or transaction lock.

    I sort of danced around saying it, when I said "....points to a specific undo segment, slot and sequence, which is the very essence or definition of a TX lock.", but I didn't really come out and say it, but it's what I was thinking.

    If I may attempt to summarize:

    A TX lock is the transaction itself, and can be interpreted as a row-level lock, in the sense that it is the lock structure that is used to "protect" uncommitted changes to rows.

    Hope that's clear,

    -Mark
  • 6. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    mbobak Oracle ACE
    Currently Being Moderated
    Tom,

    I had another thought related to this, that I think pretty much proves that TM enqueue should in no way be associated w/ a row-level lock.

    If I do this:
    devdb01.pre.il.pqe:(/home/oracle):$sqlplus mbobak

    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 4 14:16:12 2008

    Copyright (c) 1982, 2007, Oracle.  All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production


    Session altered.

    MBOBAK@refdvl> create table test_me(a number);

    Table created.

    MBOBAK@refdvl> alter table test_me disable table lock;

    Table altered.

    MBOBAK@refdvl> insert into test_me values(1);

    1 row created.

    MBOBAK@refdvl> select * from v$lock where sid=(select sid from v$mystat where rownum=1);

    ADDR             KADDR                   SID TY        ID1        ID2      LMODE
    ---------------- ---------------- ---------- -- ---------- ---------- ----------
       REQUEST      CTIME      BLOCK
    ---------- ---------- ----------
    00000000910A8CC0 00000000910A8D38        515 TX     655394        959          6
             0         47          0

    0000000092835270 00000000928352C8        515 AE      44445          0          4
             0         93          0


    MBOBAK@refdvl>
    So, by disabling table locks, you can eliminate the TM enqueue all together, yet you can still do DML on the table, implying that row-level locking still works, even when the TM enqueue is nowhere to be found.

    I should probably start another thread for this, but, anyone know what the AE enqueue is all about in 11g?

    -Mark
  • 7. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    tullio0106 Newbie
    Currently Being Moderated
    I'm a little bit confused.
    When the first user tries to lock (SELECT for update) a row TX Exclusive lock is requested and obtained.
    When the second user tries to lock (SELECT for update) a row TX Exclusive lock is requested but not obtained and the TX lock is blocked.
    However the object identified by ID1/ID2 is not the row the user is trying to read.
    The correct row is identified by the ID1/ID2 of the TM lock the first user held.
    Could someone explain me better that puzzle ?
    Tks
    Tullio
  • 8. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    tullio0106 Newbie
    Currently Being Moderated
    Repost
  • 9. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    18518 Newbie
    Currently Being Moderated
    Tullio: as Tom said, you cannot identify the rows by looking at the TM or TX lock. See, each of the sessions in the example has:

    1- Exactly one transaction, materialized in active undo entries (i.e. several undo records but only one undo number+slot+sequence). Since a session does not have more than one transaction (forget about recursive/autonomous for a moment), it only gets one active undo number+slot+sequence. All undo records under those get marked as inactive when the session commits or rolls back

    2- Exactly one TX lock, with ID1 and ID2 pointing at the transactions' active undo (if you are waiting to make your first change, you still don't have any undo so ID1 and ID2 are zero). This means from v$lock you can join say to v$transaction and look at some stats. Again, a session almost never has more than one transaction=active undo, and consequently no matter how many tables your transaction touches you will still get only one TX "lock" -which vanishes from memory when your session commits or rolls back.

    3- One TM lock for each table (and another one for each table partition, etc.) the transaction is already modifying, with ID1 pointing at the object_id that the TM lock is protecting and zero for ID2. Note what you copied from the concepts: +the TM lock does NOT protect "your" rows+, but each TM lock does protect each object where you are locking at least one row. The object is protected from being altered or dropped before you finish your transaction

    I believe your confusion stems from the fact that each distinct TM row in v$lock corresponds to a different session+object pair, so if v$lock has N rows of type TM, you actually have N distinct TM locks that correspond to sessions saying "don't you dare drop this table, I'm actively modifying some rows here".

    On the other hand, that is not exactly the case with TX locks: Even though each TX row in v$lock is a different memory structure, you will find several TX "locks" which all point to the same transaction more than once in v$lock. But then, for a given transaction (read: active undo) there will be only one TX row in v$lock with lmode != 0, and that will be for the SID which "owns" the transaction. All other rows showing a TX lock with those same ID1 and ID2 will have request != 0, one for each of the SIDs which are waiting in line for the transaction's owner to commit or roll back.

    A TX entry in v$lock is not a lock in the same sense of a TM entry, but more like a transaction enqueue mechanism. If it's confusing to look at such different things in the same fixed view v$lock, you can always use v$transaction_enqueue which isolates those "TX enqueue mechanisms" from all the rest. Fortunately, the rest do have semantics more like the intuitive definition of a lock -whatever that means.

    So you won't find anything in the dynamic views to let you know which rows a transaction is locking, not even your own session's transaction. The information is elsewhere: Oracle stores row level locks (the ones with the "intuitive" semantics) on the table blocks themselves -that's what the ITL is for. Incidentally, that's one of the beauties of Oracle, because it renders lock escalation unnecessary.

    As Mark points out, you could dump all blocks of all objects where you have TM locks, look at each blocks' ITL, and find your link between "your" rows and your transaction's active undo entry there. The only other way to skip dumping the whole segment is to follow the lead from the transaction: Until we get an "undo segment miner", the rows a specific session is locking can be digged in an easier way (sort of) by dumping the datafile blocks corresponding to the session's active undo records, then dump all table blocks mentioned there, then finding the ITL entries belonging to your transaction which will in turn point to the locked rows.

    BUT! You can get to know the one row you are waiting for (probably the one you most care about when troubleshooting): Only when you are waiting for another transaction to end, that row is pointed to by v$session's columns
    ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#. Note even this row is not one you have already locked. Rather, it is a row you know somebody else than you is locking, and probably the only case where you can "find a locked row" without resorting to block dumping.
  • 10. Re: TM / TX Locks ( Tom Kyte and Oracle Docu)
    992432 Newbie
    Currently Being Moderated
    Hello,
    I hope my issue fits in here, as I had not found any good explanation anywhere else:

    Why does a session, updating 0 rows on a simple table, create a table lock at all?
    (TM with LMODE=3)

    I came to that issue because session a forgot to commit and session b does an exclusive lock on same table.

    Thx Martin