一次大量enq:TX - row lock contention锁等待的问题(三)

timeout after: never

wait id: 6304859

blocking: 339 sessions

current sql: update so1.INS__ins t set t.expire_date=to_date('20121231 23:59:59','yyyymmdd hh24:mi:ss') where (t.offer_inst_id,t.use

r_id) in

(select a.offer_inst_id,a.user_id from wlanCPss2 a )

short stack: ksedsts()+644<-ksdxfstk()+44<-ksdxcb()+3384<-sspuser()+116<-47dc<-poll()+12<-sntpoltsts()+884<-snttmoredata()+52<-nsmore2recv()+246

0<-nioqts()+6588<-ksuttctest()+128<-updrow()+16292<-qerupFetch()+856<-updaul()+1100<-updThreePhaseExe()+336<-updexe()+344<-opiexe()+14476<-kpoal8()+4616<-opi

odr()+720<-ttcpip()+1028<-opitsk()+1508<-opiino()+940<-opiodr()+720<-opidrv()+1132<-sou2o()+136<-opimai_real()+608<-ssthrdmain()+268<-main()+204<-__start()+1

12

6，通过sid定位用户信息，确定为有人用plsql在运行上述sql

www.2cto.com

[sql]

SESSION_ID SAMPLE_TIME PROGRAM MACHINE

---------- --------------------------------------------------------------------------- ------------------------------------------------ ---------------

8863 28-SEP-12 03.49.13.605 PM plsqldev.exe XX-VM-RDS1

7，杀掉了问题sid， 系统恢复。问题出在了开发人员在业务高峰期批量更新数据，高峰时间段批量更新数据是大忌！！！！