| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> transaction lock (TX) on table block with PK
In what case is this possible?
the update statements I saw were something like;
update my_table set other_column=other_column+1 where id=:my_id;
other_column is not being referenced..
I cannot really reproduce it via sqlplus, but as soon as I have 2
sessions issuing the statement above on 2 different PK's, I see
enqueue waits on other sessions creeping up... They're all waiting for
the same block. The block is probably some table header block.
system_at_NCAA> @what_segment 5 8210
new 1: select segment_name, partition_name from dba_extents where
file_id=5 and 8210 between block_id and block_id+blocks
SEGMENT_NAME PARTITION_NAME
-------------------- ------------------------------DIV I have a perl script that identifies locks, below is a partial output Notice, my (sqlplus) session is above, we session(s) below
ncaa_live Sid: 30 Lock Typ: Transaction Mode Held: Exclusive
Mode Req: None Id1: 655380 ID2: 5346 SecsHeld: 16
osuser: oracle machine: saltlake program: sqlplus_at_saltlake (TNS V1-V3)
object#: -1 file# 0 block# 0 row# 0 sql_address 00
Current Statement (v$session.sql_address)
This session has the following SQL in v$open_cursor:
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
BEGIN DBMS_OUTPUT.DISABLE; END;
select lower(user) ||'@'|| substr(global_name,1,decode(dot,0
rollback
SELECT NULL FROM DUAL FOR UPDATE NOWAIT
update div set team_count=team_count+1 where group_id=1
SELECT USER FROM DUAL
ALTER SESSION SET TIME_ZONE='-05:00'
BEGIN DBMS_OUTPUT.ENABLE(64000); END;
SELECT DECODE('A','A','1','2') FROM DUAL
------------------------------ End session info
------------------------------
ncaa_live Sid: 14 Lock Typ: Transaction
Mode Held: None
Mode Req: Exclusive Id1: 655380 ID2: 5346
SecsHeld: 8
osuser: prod machine: dragon program: jason_moe_03130_at_dragon (TNS V1-V3)
object#: 6147 file# 5 block# 8210 row# 0 sql_address 0000000066B09190 Current Statement (v$session.sql_address) UPDATE DIV SET TEAM_COUNT=TEAM_COUNT+1 WHEREGROUP_ID=:B1
ncaa_live Sid: 17 Lock Typ: Transaction Mode Held: None
Mode Req: Exclusive Id1: 655380 ID2: 5346 SecsHeld: 12
osuser: prod machine: dragon program: jason_moe_03130_at_dragon (TNS V1-V3)
object#: 6147 file# 5 block# 8210 row# 0 sql_address 0000000066B09190 Current Statement (v$session.sql_address) UPDATE DIV SET TEAM_COUNT=TEAM_COUNT+1 WHERE GROUP_ID=:B1 ------------------------------ End session infoSecsHeld: 9
------------------------------
ncaa_live Sid: 23 Lock Typ: Transaction Mode Held:None Mode Req: Exclusive Id1: 655380 ID2: 5346
osuser: prod machine: smaug program: jason_moe_03130_at_smaug (TNS V1-V3)
object#: 6147 file# 5 block# 8210 row# 0 sql_address 0000000066B09190 Current Statement (v$session.sql_address) UPDATE DIV SET TEAM_COUNT=TEAM_COUNT+1 WHEREGROUP_ID=:B1
 |
 |