tx tm oracle,TM / TX Locks ( Tom Kyte and Oracle Docu)

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...