You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Bogdan Calmac <bc...@gmail.com> on 2007/08/01 05:13:52 UTC
lock escalation and deadlocks
First of all hello to all derby users,
Now, I have a pretty simple unit test that results in a deadlock:
- one thread is inserting records in a table (in transactions of N=200 records)
- another thread comes from behind and reads the same records (select
* where id > $lastReadID)
After examining the deadlock trace the explanation is could be this:
- the insert thread has a bunch of X row locks and tries to escalate
to an X table lock
- the select thread has a pending S row lock and tries to escalate to
an S table lock
It looks logical but to me it seems too limiting. What this means is
that you cannot insert and select from the same table concurrently.
It think the logic of escalation should avoid requesting a table lock
where there is another transaction that holds row locks and is waiting
for a table lock. At that point you know that escalation will result
for sure in a deadlock.
The problem is of course fixed if I force table level locking, but I
would have liked to be able to insert at the end of the table in the
same time somebody else select from the beginning.
What do you think?
I use derby 10.2.2.0, transaction isolation is READ_COMMITTED and the
table in question has primary key(id).
Here is the trace I was talking about:
XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE
-----------------------------------------------------------------------
*** The following row is the victim ***
226 |ROW |S |0 |(1,7) |WAIT |T
*** The above row is the victim ***
183 |ROW |X |1 |(1,62) |GRANT|T
...
183 |ROW |X |1 |(3,7) |GRANT|T
183 |TABLE |IX |476 |Tablelock |GRANT|T
226 |TABLE |IS |2 |Tablelock |GRANT|T
...
183 |ROW |X |1 |(2,66) |GRANT|T
183 |ROW |X |1 |(1,65) |GRANT|T
Re: lock escalation and deadlocks
Posted by Bogdan Calmac <bc...@gmail.com>.
Hi Bryan (sorry for the misspelling lat time),
The insert transaction is really simple (see below) so I am pretty
sure it doesn't do anything else else. In support of that, I want to
say that the deadlock trace does not reflect the LOCKCOUNT. There are
literally about 150 lines in the trace even though LOCKCOUNT=476.
Here is the code related one transaction of the insert thread (really
nothing fancy):
while(j-- > 0) {
stmt.setLong(1, agentSessionId);
stmt.setInt(2, 1);
// ... other param setters
stmt.addBatch();
} // while (tracks in batch)
stmt.executeBatch();
conn.commit();
And I would like to come back to the main question: How would you read
the deadlock trace from my previous email (the 3rd in the thread)?
What do you think could be the misterious 3rd connection that causes
the deadlock?
I will also cleanup my unit test (which actually is a benchmark) and
post it on the list so that you can reproduce the bahaviour. It
happens consistently, so this should not be a problem.
thanks,
Bogdan.
On 8/1/07, Bryan Pendleton <bp...@amberpoint.com> wrote:
> > Oh, and one more observation. The IX table lock for the insert thread
> > mentions LOCKCOUNT=476. I can only infer the meaning of the column (so
> > this might perfectly normal), but the number of row locks of that
> > connection is about 150 (it could not exceed 200, the number of
> > inserts I do per transaction).
>
> It sounds like the insert thread is doing more work than you expect
> it to be doing. You could investigate this using the logStatementText
> property to figure out what actual SQL is getting executed for each
> transaction in your application:
> http://db.apache.org/derby/docs/10.2/tuning/rtunproper43517.html
>
> thanks,
>
> bryan
>
>
Re: lock escalation and deadlocks
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> Oh, and one more observation. The IX table lock for the insert thread
> mentions LOCKCOUNT=476. I can only infer the meaning of the column (so
> this might perfectly normal), but the number of row locks of that
> connection is about 150 (it could not exceed 200, the number of
> inserts I do per transaction).
It sounds like the insert thread is doing more work than you expect
it to be doing. You could investigate this using the logStatementText
property to figure out what actual SQL is getting executed for each
transaction in your application:
http://db.apache.org/derby/docs/10.2/tuning/rtunproper43517.html
thanks,
bryan
Re: lock escalation and deadlocks
Posted by Bogdan Calmac <bc...@gmail.com>.
Oh, and one more observation. The IX table lock for the insert thread
mentions LOCKCOUNT=476. I can only infer the meaning of the column (so
this might perfectly normal), but the number of row locks of that
connection is about 150 (it could not exceed 200, the number of
inserts I do per transaction).
On 8/1/07, Bogdan Calmac <bc...@gmail.com> wrote:
> Hi Brian,
>
> OK, I see. What I said about escalation is wrong. I looked with new
> eyes at the trace (see a more relevant summary below) and here's what
> appears to happen:
>
> - 216 (the select thread) holds a lock on 1,1 and waits for a lock on 1,7
> - 183 (the insert thread) holds many locks, including 1,7
> - 226 (maybe some db internal) waits for a lock on 1,1
>
> 226 must probably be forked from from 183 (the insert thread), that's
> how the deadlock would make sense.
>
> So the original question remains: Is it expected behaviour for insert
> and select on the same table to deadlock each other? Can you figure
> out the relationship between 183 and 226? As a comparison, the same
> test works fine on other databases with a similar locking approach.
>
> XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE
> ------------------------------------------------------------------
> *** The following row is the victim ***
> 216 |ROW |S |0 |(1,7) |WAIT |T
> *** The above row is the victim ***
> 216 |ROW |S |1 |(1,1) |GRANT|T
> 226 |ROW |X |0 |(1,1) |WAIT |T
> 183 |ROW |X |1 |(1,7) |GRANT|T
> 183 |TABLE |IX |476 |Tablelock |GRANT|T
> 216 |TABLE |IS |2 |Tablelock |GRANT|T
> ... and many more like the row below
> 183 |ROW |X |1 |(1,65) |GRANT|T
>
> Another observation that might be useful for the interpretation is
> that the trace is the same on every run. The record of contention is
> always (1,7)
>
> Thanks,
>
> Bogdan.
>
Re: lock escalation and deadlocks
Posted by Bogdan Calmac <bc...@gmail.com>.
Hi Brian,
OK, I see. What I said about escalation is wrong. I looked with new
eyes at the trace (see a more relevant summary below) and here's what
appears to happen:
- 216 (the select thread) holds a lock on 1,1 and waits for a lock on 1,7
- 183 (the insert thread) holds many locks, including 1,7
- 226 (maybe some db internal) waits for a lock on 1,1
226 must probably be forked from from 183 (the insert thread), that's
how the deadlock would make sense.
So the original question remains: Is it expected behaviour for insert
and select on the same table to deadlock each other? Can you figure
out the relationship between 183 and 226? As a comparison, the same
test works fine on other databases with a similar locking approach.
XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE
------------------------------------------------------------------
*** The following row is the victim ***
216 |ROW |S |0 |(1,7) |WAIT |T
*** The above row is the victim ***
216 |ROW |S |1 |(1,1) |GRANT|T
226 |ROW |X |0 |(1,1) |WAIT |T
183 |ROW |X |1 |(1,7) |GRANT|T
183 |TABLE |IX |476 |Tablelock |GRANT|T
216 |TABLE |IS |2 |Tablelock |GRANT|T
... and many more like the row below
183 |ROW |X |1 |(1,65) |GRANT|T
Another observation that might be useful for the interpretation is
that the trace is the same on every run. The record of contention is
always (1,7)
Thanks,
Bogdan.
Re: lock escalation and deadlocks
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> Now, I have a pretty simple unit test that results in a deadlock:
> - one thread is inserting records in a table (in transactions of N=200 records)
> - another thread comes from behind and reads the same records (select
> * where id > $lastReadID)
>
> After examining the deadlock trace the explanation is could be this:
> - the insert thread has a bunch of X row locks and tries to escalate
> to an X table lock
> - the select thread has a pending S row lock and tries to escalate to
> an S table lock
Are you sure you are escalating to table level locks? The table level
escalation threshold is configurable, but should default to 5,000 rows:
http://db.apache.org/derby/docs/10.2/tuning/ctunoptimz26019.html#ctunoptimz26019
If your transaction(s) are only touching 200 records at a time, it doesn't
seem like you ought to be escalating to table level locks.
> Here is the trace I was talking about:
In the trace that you posted, I don't see any evidence of a transaction
holding or requesting a table level lock. The "IX" and "IS" locks are
*intent* locks, which is not the same thing as a table level lock.
thanks,
bryan