My application is issuing a SELECT FOR UPDATE query on a Oracle DB. There is only one thread querying the database, so there is no question of some other thread locking rows. The query is supposed to return a row, but it doesn't, for many iterations (done fromthe application), and only then it returns a row. I looked at the Ibatis log to see if something helps, but nothing such. Below is the log. The same query executed without FOR UPDATE on the sql client returns a row. Anyone knows what is going wrong?
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Connection
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Connection
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Preparing Statement: select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE, Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT, JE.EMAILADDR, JE.IS_PRIMARY_EMAIL from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR, ARSND_JOB_EMAILS JE, ( select JOB_ID from ARSND_JOB_QUEUE
Q2 where PROCESSING_STATE = ? and WORKING_INSTANCE IS null and WORKING_THREAD = 0 and ORGNAME not in ( ? ) order by LAST_UPDATED ) OldestReadyFirst where J.JOB_ID = Q.JOB_ID and JR.JOB_ID=J.JOB_ID and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT and JE.JOB_ID=JR.JOB_ID and
JE.RESEND_COUNT=JR.RESEND_COUNT and JE.USERNAME=JR.USERNAME and JE.ORGNAME=JR.ORGNAME and Q.JOB_ID = OldestReadyFirst.JOB_ID and rownum = 1 for update of Q.WORKING_INSTANCE NOWAIT
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Preparing Statement: select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE, Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT, JE.EMAILADDR, JE.IS_PRIMARY_EMAIL from ARSND_JOB_QUEUE Q, ARSND_JOBS
J, ARSND_JOB_RECIPIENTS JR, ARSND_JOB_EMAILS JE, ( select JOB_ID from ARSND_JOB_QUEUE Q2 where PROCESSING_STATE = ? and WORKING_INSTANCE IS null and WORKING_THREAD = 0 and ORGNAME not in ( ? ) order by LAST_UPDATED ) OldestReadyFirst where J.JOB_ID = Q.JOB_ID and
JR.JOB_ID=J.JOB_ID and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT and JE.JOB_ID=JR.JOB_ID and JE.RESEND_COUNT=JR.RESEND_COUNT and JE.USERNAME=JR.USERNAME and JE.ORGNAME=JR.ORGNAME and Q.JOB_ID = OldestReadyFirst.JOB_ID and rownum = 1 for update of Q.WORKING_INSTANCE NOWAIT
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Executing Statement: select Q.JOB_ID, J.ORGNAME, J.CREATED,
J.CONTENT_BASENAME, J.CONTENT_TYPE, Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT, JE.EMAILADDR, JE.IS_PRIMARY_EMAIL from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR, ARSND_JOB_EMAILS JE, ( select JOB_ID from ARSND_JOB_QUEUE Q2 where PROCESSING_STATE = ? and WORKING_INSTANCE IS null and WORKING_THREAD = 0 and ORGNAME not in ( ?
) order by LAST_UPDATED ) OldestReadyFirst where J.JOB_ID = Q.JOB_ID and JR.JOB_ID=J.JOB_ID and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT and JE.JOB_ID=JR.JOB_ID and JE.RESEND_COUNT=JR.RESEND_COUNT and JE.USERNAME=JR.USERNAME and JE.ORGNAME=JR.ORGNAME and Q.JOB_ID = OldestReadyFirst.JOB_ID and
rownum = 1 for update of Q.WORKING_INSTANCE NOWAIT
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Executing Statement: select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE, Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT, JE.EMAILADDR, JE.IS_PRIMARY_EMAIL from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR, ARSND_JOB_EMAILS JE, ( select JOB_ID from ARSND_JOB_QUEUE Q2 where PROCESSING_STATE =
? and WORKING_INSTANCE IS null and WORKING_THREAD = 0 and ORGNAME not in ( ? ) order by LAST_UPDATED ) OldestReadyFirst where J.JOB_ID = Q.JOB_ID and JR.JOB_ID=J.JOB_ID and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT and JE.JOB_ID=JR.JOB_ID and
JE.RESEND_COUNT=JR.RESEND_COUNT and JE.USERNAME=JR.USERNAME and JE.ORGNAME=JR.ORGNAME and Q.JOB_ID = OldestReadyFirst.JOB_ID and rownum = 1 for update of Q.WORKING_INSTANCE NOWAIT
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Parameters: [10, DEFAULTORG]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Parameters: [10, DEFAULTORG]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Types: [java.lang.Integer, java.lang.String]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132}
Types: [java.lang.Integer, java.lang.String]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.ResultSet : {rset-110133} ResultSet
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.ResultSet : {rset-110133} ResultSet
Thanks
Kannan |
Locked
