Thanks David and Adeel for your responses. Sorry for the late reply, I was waiting for the error to occur again. I also don't know how to state this since db2 is quite alien to me. Anyway, I was able to see db2diag.log and match some of the errors to our own log file. Unfortunately, I still can't determine what the other "deadlock" statements are. So what I will show here are the statements that were rolled-back and threw an exception. Here is what I found:
This statement is executed from a scheduler thread with a 30 second interval. The deadlock occurs very often with this statement. This is probably the problem statement but I don't know what's wrong with it.
"UPDATE Trade SET oasys_send_success = 'S' " +
"WHERE bucket_status = 'B' AND status IN ('RL','SB') AND account_id IN " +
"(SELECT account_id FROM Account WHERE oasys_enabled = 'N') " +
"OR product_code NOT IN ('E', 'A') " +
"OR amended_status <> ' '";
The following statements seem to deadlock at random:
"SELECT trade_id " +
"FROM Trade, Account " +
"WHERE Trade.account_id = Account.account_id " +
"AND bucket_status = 'B' " +
"AND status IN ('RL', 'SB') " +
"AND amended_status = ' ' " +
"AND oasys_send_success IN (' ', 'B') " +
"AND trade_source='OASY' " +
"AND oasys_enabled = 'Y' " +
"AND oasys_mode='ALLOC' ";
This statement is executed from a web service(thread)
"UPDATE Trade " +
"SET ...LOTS OF FIELDS..." +
"WHERE trade_id = ?";
This was called from a struts web application(from a request from a web page)
"select count(*) " +
"FROM trade " +
"WHERE account_id LIKE '" +ledgerId+ "%' AND " +
CRITERIA VARIES;
This was called from a struts web application(from a request from a web page)
"SELECT trade_id, account_id, account_type, reference, " +
"product_code, cancel_status, " +
"side, trade_date, qty, " +
"price, " +
"comm_basis, comm_rate, " +
"settle_amt, comm_amt, " +
"execution_time, " +
"symbol_cusip, status, markup_price " +
"FROM trade WHERE bucket_status = 'B'" +
ADDITONAL CRITERIA VARIES +
" ORDER BY trade_date ASC, account_id ASC, product_code ASC, " +
"symbol_cusip, side ASC, amended_status ASC, receive_time ASC";
This was called from a struts web application(from a request from a web page)
Thanks
Ranch Hand Scavenger Hunt
Number Posts (19/100)
First Post