Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Question on locks and AutoCommit

 
Pat Villa
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We are currently experiencing some deadlocks on our system:
com.ibm.db2.jcc.a.SqlException: The current transaction has been rolled back because of a deadlock or timeout. Reason code "2".

We also get a "68" reason code on other occasions.

Reason codes:
2 - deadlock
68 - lock timeout

We have a system that has more than 1 thread accessing/updating the DB(A scheduler thread, methods triggered by a Web Service, and those triggered from a struts web application). We do not make use of jdbc transactions(using setAutoCommit(false)). I've read that one way to avoid deadlocks is to commit often. Since a connection's autocommit property is set to true by default, and commits are supposed to release locks for the after each execute/statement, doesn't that mean that deadlocks are not supposed to occur?

DB: DB2 v8.1
App Server: WAS 5
OS: IBM AIX/Win2000

Any insight would be appreciated, thanks
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Deadlock:

A situation in which two or more processes are prevented from continuing while each waits for resources to be freed by the continuation of the other.

Yes frequent commits can save you from deadlocks. Because unlikely there could be a situation where two or more processes cannot proceed because they are both waiting for the other to release the resource.

Lock Time-out:

A situation where one process cannot proceed because it is waiting for the other to release the resource. And that resource is not happened to be released quite long.

Frequent commits can save you from this as well.

cheers
[ January 13, 2005: Message edited by: Adeel Ansari ]
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If commit happens after every transaction then definitely deadlock never happens.

set auto-commit to false, when you really need to do it. But commit the transaction when done. Say if you are running 100 transaction with auto-commit off then atleast do the commit after every 10 or 20 transactions, or before.
 
Pat Villa
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply!

That's why I'm at a loss. I never set auto commit to false in my code. Could it be possible then that the autocommit setting was overridden elsewhere? say the app server or database itself?
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No I dont think so.

Are you sure about your settings. Just check it once by getAutoCommit(). Or are several developers working on the same project? if yes then may be some folk set it to false and place it back into pool, assuming a pool there.
 
Pat Villa
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Adeel Ansari:
No I dont think so.

Are you sure about your settings. Just check it once by getAutoCommit(). Or are several developers working on the same project? if yes then may be some folk set it to false and place it back into pool, assuming a pool there.


Nope, I've searched the code for "setAutoCommit". I've also tried to output getAutoCommit and it came out as "true". BTW, we're using EJB session beans that use DAOs to connect to a datasource on Websphere App Server 5.

Time to use a graemlin

[ January 13, 2005: Message edited by: Pat Villa ]
[ January 13, 2005: Message edited by: Pat Villa ]
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not familiar with DB2, mostly Oracle, but it's possible two connections are executing queries that lock multiple resources. For example, say both update the same two rows, A and B, but do so in reverse order.
  • Conn 1 locks row A
  • Conn 2 locks row B
  • Conn 1 waits to lock row B
  • Conn 2 waits to lock row A

  • Now both connections are waiting for the other to release a lock -- deadlock occurs.

    If you have a tool that allows you to view the currently executing queries and/or the held and waiting locks, that would help a ton. Otherwise, you need to examine all of your queries to find out which ones could cause the trouble.
     
    Adeel Ansari
    Ranch Hand
    Posts: 2874
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    David,

    Auto commit is on

    1 locks the row A and release
    2 locks the row B and release

    now if

    1 wants to lock row B, 1 can
    2 wants to lock row A, 2 can.

    But yes i think it is the case of "select for update".

    thanks.
     
    David Harkness
    Ranch Hand
    Posts: 1646
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    In my example, both connections are executing a single query that updates the same two rows -- one query, one transaction. Both row updates for each connection must either be committed together or rolled back together. If DB2 does row level locking, will it not do the following?
  • lock each row until all rows to be updated are locked
  • perform the update
  • unlock each row (transaction is committed)

  • Again, I don't know how DB2 does it, but I believe this is what Oracle would do as it does have row-level locking. Of course, this is just conjecture. If Pat can find the queries that are deadlocked, it will be much easier to solve.
     
    Adeel Ansari
    Ranch Hand
    Posts: 2874
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Yes, now i got you. hm it might be the case.
    And there must be some row level locking mechanism there in DB2.
    [ January 14, 2005: Message edited by: Adeel Ansari ]
     
    Pat Villa
    Greenhorn
    Posts: 19
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
     
    Pat Villa
    Greenhorn
    Posts: 19
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    The 1st statement I posted above has corresponding errors similar to this in db2diag.log:
    2005-01-19-03.56.49.429000-300 E584200H495 LEVEL: Error

    PID : 1696 TID : 1952 PROC : db2syscs.exe

    INSTANCE: DB2 NODE : 000 DB : ACCS

    APPHDL : 0-303 APPID: J070FE3D.FB58.01018A2287E6

    FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:4

    MESSAGE : ADM5503E The escalation of "5385" locks on table "PACCS

    .ACCS_TRADECURTB" to lock intent "X" has failed. The SQLCODE is

    "-911".

    The rest of the statements didn't have any error show up in db2diag.log. This is probably a log level issue
     
    Ben Keeping
    Greenhorn
    Posts: 10
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Perhaps you should consider synchronizing your db-interaction methods ? This would guard against any deadlocks db side ...
     
    David Harkness
    Ranch Hand
    Posts: 1646
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Wow, you've got some fun work ahead of you. The other thing you need to map out is how many threads/applications can be executing these queries. Clearly, the one on the 30 second interval timer is a key one.

    The other thing to consider which I didn't even think about, and admit I know even less about (and nothing when it comes to DB2), is that when updating indexed fields, I believe that the index itself is locked. Imagine two queries where query 1 queries column A and updates column B and query 2 queries column B and updates column A (A and B are indexed).Query 1 read-locks index A and performs query.
    Query 2 read-locks index B performs query.
    Query 1 write-locks index B -- waits for lock.
    Query 2 write-locks index A -- waits for lock -- deadlock.I can't say I'm positive that's how it would work, but I think it does. This is very similar to the row-locking I laid out above.

    To track down which queries are causing this, you might want to try isolating them. Put two of the likeliest queries into timed threads to run as often as possible and see if they lock up. Then introduce a third one. I know, this isn't easy since you need to pick values that are realistic, but that's where I'd start.

    I'm pretty slammed here with porting to Hibernate. If I have some free time later I'll look over those queries a bit more. For now I'd look at the index-locking issue I raised above. I'm surprised DB2 isn't more helpful in this regard, but perhaps there's a different tool or logging level yoou can turn on. In Oracle you can see the query execution plan with all of the locks that it would acquire, IIRC.
    Originally posted by Pat Villa:
    The SQLCODE is "-911".
    Brilliant!
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic