最近發生的幾起 enq: TX - row lock contention 等待事件很怪，通過 blocking session id 檢視，不是語句是 select，就是會話是 inactive 的。
實驗
準備工作
- 進入 hr 使用者，同時檢視會話 id，下面會稱為 會話 38
1
2
3
4
SQL>
select
userenv(
'sid'
)
from
dual;
USERENV(
'SID'
)
--------------
3
- 建立測試表
create
table
emp_bak
as
select
*
from
employees
- 建立被鎖會話，同時檢視會話 id，下面會稱為 會話 28
SQL>
select
userenv(
'sid'
)
from
dual;
USERENV(
'SID'
)
--------------
28
測試
- 會話 38 産生鎖操作，注意，此處不進行送出操作，且操作完不進行 exit 操作
5
6
7
8
9
10
SQL>
SELECT
employee_id, first_name, last_name, salary
2
FROM
emp_bak
3
WHERE
employee_id = 166;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- ---------------------------------------- -------------------------------------------------- ----------
166 Sundar Ande 6400
SQL>
update
emp_bak
2
set
salary = salary + 100
3
where
employee_id = 166;
1 row updated
- 會話 28，為了區分操作語句，此處我們執行 delete 操作，此時會出現 hang，暫且不去管它
SQL>
delete
from
emp_bak
2
where
employee_id = 166
- 此時，我們新啟會話查一下鎖情況 由于長時間未對資料庫進行操作，是以會話狀态為 INACTIVE 狀态，鎖的語句為 update
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
col event
for
a30
col username
for
a8
col process
for
a7
col machine
for
a7
col program
for
a30
col sql
for
a80
SELECT
a.sid,
b.status,
b.event,
b.USERNAME,
b.PROCESS,
b.MACHINE,
b.program,
CASE
WHEN
rawtohex(b.SQL_ADDRESS) <>
'00'
THEN
'CURR'
ELSE
'PREV'
END
STAT,
c.sql_text
"SQL"
FROM
v$lock a, v$session b, v$sql c
WHERE
(a.id1, a.id2)
IN
(
SELECT
ID1, ID2
FROM
gv$lock
WHERE
TYPE =
'TX'
AND
request > 0)
AND
a.sid = b.sid
AND
CASE
WHEN
rawtohex(b.SQL_ADDRESS) <>
'00'
THEN
b.SQL_ADDRESS
ELSE
b.PREV_SQL_ADDR
END
= c.address
AND
CASE
WHEN
b.SQL_HASH_VALUE > 0
THEN
b.SQL_HASH_VALUE
ELSE
b.PREV_HASH_VALUE
END
= c.hash_value;
Oracle：select 或 inactive 會話語句産生鎖？ - 那麼此時，我們在會話 38 上執行 select 語句，查詢的狀态是怎樣的呢？
SQL>
SELECT
employee_id, first_name, last_name, salary
2
FROM
emp_bak
3
WHERE
employee_id = 166;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- ---------------------------------------- -------------------------------------------------- ----------
166 Sundar Ande 6500
Oracle：select 或 inactive 會話語句産生鎖？ - 我們此時可以再關聯 v$transaction，來檢視具體資訊
SELECT
a.sid,
b.status,
b.event,
b.USERNAME,
b.PROCESS,
b.MACHINE,
b.program,
CASE
WHEN
rawtohex(b.SQL_ADDRESS) <>
'00'
THEN
'CURR'
ELSE
'PREV'
END
STAT,
c.sql_text
"SQL"
,
d.start_time,
d.status,
d.xid,
d.USED_UBLK,
d.USED_UREC
FROM
v$lock a, v$session b, v$sql c, v$
transaction
d
WHERE
(a.id1, a.id2)
IN
(
SELECT
ID1, ID2
FROM
gv$lock
WHERE
TYPE =
'TX'
AND
request > 0)
AND
a.sid = b.sid
AND
CASE
WHEN
rawtohex(b.SQL_ADDRESS) <>
'00'
THEN
b.SQL_ADDRESS
ELSE
b.PREV_SQL_ADDR
END
= c.address
AND
CASE
WHEN
b.SQL_HASH_VALUE > 0
THEN
b.SQL_HASH_VALUE
ELSE
b.PREV_HASH_VALUE
END
= c.hash_value
AND
rawtohex(d.addr(+)) = b.taddr;
Oracle：select 或 inactive 會話語句産生鎖？
結論
- blocking session id 記錄的是誰鎖的自己