在执行一个JOB时,发现表死锁了。
情况如下:OBJECTNAME:TMP_STUDENT_STUDYSTATUS
LOCKTYPE:TM
LOCKMODE:Row-X (SX)
PROGRAMNAME:ORACLE@USER-DB(J001)
锁的表是TMP_STUDENT_STUDYSTATUS。
执行的JOB所调用的存储过程如下
CREATE OR REPLACE PROCEDURE Psch_Studentstudystatus IS
/**
学员学习状态整理
JOB03
*/
v_num NUMBER;
BEGIN
BEGIN
DELETE TMP_STUDENT_STUDYSTATUS;
COMMIT; INSERT INTO TMP_STUDENT_STUDYSTATUS (studentid,schoolyear,studyStatus)
SELECT studentid ,schoolyear, CASE pass WHEN 1 THEN 'P' WHEN 0 THEN 'Y' ELSE 'N' END AS STUDYSTATUS
FROM (
SELECT t1.studentid ,t1.schoolyear, Getstudentstudyinstance2(t1.studentid,t1.schoolyear) AS pass
FROM PUBSTUDENTCURRENT t1
WHERE t1.registstatus='Y'
);
COMMIT;
UPDATE PUBSTUDENTCURRENT t1
SET t1.studystatus =
(SELECT STUDYSTATUS FROM TMP_STUDENT_STUDYSTATUS t2 WHERE t1.studentid=t2.studentid AND t1.schoolyear=t2.schoolyear)
WHERE t1.registstatus='Y' ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; RAISE_APPLICATION_ERROR(-20000,'Psch_RANK error! ' || SQLERRM); RETURN;
END;
END;
/TMP_STUDENT_STUDYSTATUS表只是个临时表,其他地方都没有使用到。为什么在INSERT的时候也会造成锁呢?
请各位赐教了,谢谢
情况如下:OBJECTNAME:TMP_STUDENT_STUDYSTATUS
LOCKTYPE:TM
LOCKMODE:Row-X (SX)
PROGRAMNAME:ORACLE@USER-DB(J001)
锁的表是TMP_STUDENT_STUDYSTATUS。
执行的JOB所调用的存储过程如下
CREATE OR REPLACE PROCEDURE Psch_Studentstudystatus IS
/**
学员学习状态整理
JOB03
*/
v_num NUMBER;
BEGIN
BEGIN
DELETE TMP_STUDENT_STUDYSTATUS;
COMMIT; INSERT INTO TMP_STUDENT_STUDYSTATUS (studentid,schoolyear,studyStatus)
SELECT studentid ,schoolyear, CASE pass WHEN 1 THEN 'P' WHEN 0 THEN 'Y' ELSE 'N' END AS STUDYSTATUS
FROM (
SELECT t1.studentid ,t1.schoolyear, Getstudentstudyinstance2(t1.studentid,t1.schoolyear) AS pass
FROM PUBSTUDENTCURRENT t1
WHERE t1.registstatus='Y'
);
COMMIT;
UPDATE PUBSTUDENTCURRENT t1
SET t1.studystatus =
(SELECT STUDYSTATUS FROM TMP_STUDENT_STUDYSTATUS t2 WHERE t1.studentid=t2.studentid AND t1.schoolyear=t2.schoolyear)
WHERE t1.registstatus='Y' ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; RAISE_APPLICATION_ERROR(-20000,'Psch_RANK error! ' || SQLERRM); RETURN;
END;
END;
/TMP_STUDENT_STUDYSTATUS表只是个临时表,其他地方都没有使用到。为什么在INSERT的时候也会造成锁呢?
请各位赐教了,谢谢
,这是锁,但不是死锁看看V$lock和v$session_wait中有什么信息?
v$locked_object
解决办法:
1.尽可能减小事务的大小.
2.优化你的程序,提高运算速度.
LOCKMODE:Row-X (SX)
ROW锁等待
可能是INSERT INTO TMP_STUDENT_STUDYSTATUS (studentid,schoolyear,studyStatus)
SELECT studentid ,schoolyear, CASE pass WHEN 1 THEN 'P' WHEN 0 THEN 'Y' ELSE 'N' END AS STUDYSTATUS
FROM (
SELECT t1.studentid ,t1.schoolyear, Getstudentstudyinstance2(t1.studentid,t1.schoolyear) AS pass
FROM PUBSTUDENTCURRENT t1
WHERE t1.registstatus='Y'
); 产生...