CREATE TABLE divisions (
division_id CHAR(3) CONSTRAINT divisions_pk PRIMARY KEY,
name VARCHAR2(15) NOT NULL
);
CREATE TABLE jobs (
job_id CHAR(3) CONSTRAINT jobs_pk PRIMARY KEY,
name VARCHAR2(20) NOT NULL
);
CREATE TABLE employees2 (
employee_id INTEGER CONSTRAINT employees2_pk PRIMARY KEY,
division_id CHAR(3)
CONSTRAINT employees2_fk_divisions
REFERENCES divisions(division_id),
job_id CHAR(3) REFERENCES jobs(job_id),
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
salary NUMBER(6, 0)
);
我在sqlplus中执行一些INSERT INTO操作,但网络连接中断了,没有commit或rollback。
之后再次sqlplus执行相同的INSERT INTO语句时就hang了,查看锁表的语句:
INSERT INTO divisions ( division_id, name ) VALUES ( 'SAL', 'Sales' )
这个就是所有这些INSERT INTO的第一条语句。
使用
SELECT A.USERNAME,
A.MACHINE,
A.PROGRAM,
A.SID,
A.SERIAL#,
A.STATUS,
C.PIECE,
C.SQL_TEXT
FROM V$SESSION A,
V$SQLTEXT C
WHERE A.SID IN (SELECT DISTINCT T2.SID
FROM V$LOCKED_OBJECT T1,
V$SESSION T2
WHERE T1.SESSION_ID = T2.SID)
AND A.SQL_ADDRESS = C.ADDRESS(+)
ORDER BY C.PIECE;
查看:该条INSERT有两个piece:
SID SERIAL# STATUS PIECE SQL_TEXT
200 77 ACTIVE 0 INSERT INTO divisions ( division_id, name ) VALUES ( 'SAL',
200 77 ACTIVE 1 'Sales' )
再用:
SELECT object_id, session_id, locked_mode FROM v$locked_object;
SELECT t2.username,
t2.sid,
t2.serial#,
t2.logon_time
FROM v$locked_object t1, v$session t2
WHERE t1.session_id = t2.sid
ORDER BY t2.logon_time;
查看:
OBJECT_ID SESSION_ID LOCKED_MODE
10271 200 2
10267 200 3
为什么这种情况:
1、INSERT语句会会hang有锁?
2、为什么一条INSERT语句有两个PIECE?
3、为什么一条INSERT语句有两个锁?
谢谢!