ITPub博客

首页 > Linux操作系统 > Linux操作系统 > TX\TM锁

TX\TM锁

原创 Linux操作系统 作者:zecaro 时间:2011-01-24 20:03:26 0 删除 编辑

       最近事情不少,杂七杂八的。没什么心情整理一些东西,人生也有些小迷茫。

       之前想写的内容堆积了不少,我怕再堆下去就不写了。所以,趁现在还有些觉悟的时候,先写上几篇吧。

        前几周,有位同事过来说测试机器删除一张表很慢,一直没删完。查了一下正在运行的sql,(这里的数据是我模拟了一下当时的环境)发现

 SELECT   /*+ leading(a) */  a.SID, a.username, b.hash_value, c.event, b.sql_text
    FROM v$session a, v$session_wait c, v$sql b
   WHERE a.status = 'ACTIVE'
     AND a.sql_hash_value = b.hash_value(+)
     AND a.SID = c.SID(+)
GROUP BY a.SID, a.username, b.hash_value, c.event, b.sql_text
ORDER BY sql_text

SID    EVENT             SQL_TEXT

2593  enq: TX - row lock contention    delete from test

当时在想可能是之前对这张表进行了操作,但没有commit rollback,所以才有enq: TX - row lock contention。

然后,继续查一下。

 select username,
       v$lock.sid,
       trunc(id1/power(2,16)) rbs,
       bitand(id1,to_number('ffff','xxxx'))+0 slot,
       id2 seq,
       lmode,
       request
from v$lock, v$session
where v$lock.type = 'TX'
  and v$lock.sid = v$session.sid
  and v$session.username = USER

 --USER查看当前用户 (这里的数据是我模拟了一下当时的环境,至于当时到底如何,很难说清了,实在不行,就权当实验啦)


USERNAME          SID        RBS       SLOT        SEQ      LMODE    REQUEST
---------- ---------- ---------- ---------- ---------- ---------- ----------
Z    2593    11    19    94126    0    6
Z    2593    7     8     106873   6    0
Z    2545    11    19    94126    6    0

           TX 锁(事务锁):事务发起第一个修改时会得到TX 锁(事务锁),而且会一直持有这个锁,直至事务执行提交(COMMIT)或回滚(ROLLBACK)。TX 锁用作一种排队机制,使得其他会话可以等待这个事务执行。

          第三行,2545持有  LMODE=6的锁(LMODE:Lock mode in which the session holds the lock:,6表示exclusive (X),下面顺便贴了v$lock)   。REQUEST为0(none) ,说明没有发出请求。       

           第一行,2593 请求REQUEST为6的锁(REQUEST:Lock mode in which the process requests the lock:),请求的是RBS       SLOT        SEQ      为 11    19    94126    。即2545的RBS       SLOT        SEQ相关字段。

select SID,XIDUSN, XIDSLOT, XIDSQN
from v$transaction ,v$session
where ses_addr=saddr

SID    XIDUSN    XIDSLOT    XIDSQN

2545    11    19    94126
2593    7    8    106873

--这里查看事务,事务ID 是事务所独有的,表示了回滚段号、槽和序列号,session id为2545 的事务ID 是11    19    94126,对应之前查询的 SID        RBS       SLOT 。

             所以,判断为之前有一个session对表进行了某些操作,但是没有提交或回滚,delete的时候就卡住了,最后把两个session里的那个非delete的给kill掉,就OK了。
 

            之前说过这里的数据是我模拟了一下当时的环境,就是开一个session,建一张测试t表,然后insert几条记录,commit后,update一条,不提交,在另一个窗口用同样的用户开一个session,执行delete from t。

select username,
       v$lock.sid,
     id1,
       id2 ,
       lmode,
       request,
       v$lock.type
from v$lock, v$session
where v$lock.type = 'TM'
  and v$lock.sid = v$session.sid
  and v$session.username = USER
  --USER查看当前用户
 
USERNAME    SID    ID1    ID2    LMODE    REQUEST    TYPE

Z    2545    73113    0    3    0    TM
Z    2593    73113    0    3    0    TM

select object_name, object_id
from user_objects
where object_name in ('T')
 
OBJECT_NAME    OBJECT_ID
T    73113

         可以看到还有两个TM锁,TM 锁(TM lock)用于确保在修改表的内容时,表的结构不会改变。具体的参考了tom的9i&10g编程艺术吧。

              注:以上的sql也参考了不少tom的9i&10g编程艺术。


        最后贴一下v$lock

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1147.htm#REFRN30121

V$LOCK

This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

Column Datatype Description
ADDR RAW(4 | 8) Address of lock state object
KADDR RAW(4 | 8) Address of lock
SID NUMBER Identifier for session holding or acquiring the lock
TYPE VARCHAR2(2) Type of user or system lock

The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:

TM - DML enqueue

TX - Transaction enqueue

UL - User supplied

The locks on the system types are held for extremely short periods of time. The system type locks are listed in Table 6-1.

ID1 NUMBER Lock identifier #1 (depends on type)
ID2 NUMBER Lock identifier #2 (depends on type)
LMODE NUMBER Lock mode in which the session holds the lock:
  • 0 - none

  • 1 - null (NULL)

  • 2 - row-S (SS)

  • 3 - row-X (SX)

  • 4 - share (S)

  • 5 - S/Row-X (SSX)

  • 6 - exclusive (X)

REQUEST NUMBER Lock mode in which the process requests the lock:
  • 0 - none

  • 1 - null (NULL)

  • 2 - row-S (SS)

  • 3 - row-X (SX)

  • 4 - share (S)

  • 5 - S/Row-X (SSX)

  • 6 - exclusive (X)

CTIME NUMBER Time since current mode was granted
BLOCK NUMBER A value of either 0 or 1, depending on whether or not the lock in question is the blocker.

Table 6-1 Values for the TYPE Column: System Types

System Type Description System Type Description

BL

Buffer hash table instance

NA..NZ

Library cache pin instance (A..Z = namespace)

CF

Control file schema global enqueue

PF

Password File

CI

Cross-instance function invocation instance

PI, PS

Parallel operation

CU

Cursor bind

PR

Process startup

DF

datafile instance

QA..QZ

Row cache instance (A..Z = cache)

DL

Direct loader parallel index create

RT

Redo thread global enqueue

DM

Mount/startup db primary/secondary instance

SC

System change number instance

DR

Distributed recovery process

SM

SMON

DX

Distributed transaction entry

SN

Sequence number instance

FS

File set

SQ

Sequence number enqueue

HW

Space management operations on a specific segment

SS

Sort segment

IN

Instance number

ST

Space transaction enqueue

IR

Instance recovery serialization global enqueue

SV

Sequence number value

IS

Instance state

TA

Generic enqueue

IV

Library cache invalidation instance

TS

Temporary segment enqueue (ID2=0)

JQ

Job queue

TS

New block allocation enqueue (ID2=1)

KK

Thread kick

TT

Temporary table enqueue

LA .. LP

Library cache lock instance lock (A..P = namespace)

UN

User name

MM

Mount definition global enqueue

US

Undo segment DDL

MR

Media recovery

WL

Being-written redo log instance


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23650854/viewspace-684580/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-10-28

  • 博文量
    182
  • 访问量
    348813