db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Geoff hendrey <geoff_hend...@yahoo.com>
Subject Re: why are table locks being made?
Date Fri, 11 Jan 2008 04:24:24 GMT
OK, I understand why the deadlock happens, and it is exactly the scenario I outlined in my
previous email. Basically the deadlock is happening due to table locks. The foreign key relationships
to other tables are basically causing this massive table-lock propogation into all the tables
that are foreign-key-connected through an ON DELETE SET NULL. I got a better deadlock dump
by adding these to the derby.properties:


derby.locks.monitor=true
derby.locks.deadlockTrace=true
derby.locks.deadlockTimeout=1
derby.locks.waitTimeout=1

here is the full deadlock dump. You can see all the other tables getting table-locked. Therefore,
the only solution I can see is to synchronize deletes at the application layer. I did this,
and naturally no more deadlocks. But I still feel like I should not have to do that kind of
application-layer synchronization. Anyway, here is the full deadlock dump. Someone who understands
all the flags in the dump might be able to tell if there is a way to avoid the deadlock through
a different configuration of the database or indexes, or foreign keys.


2008-01-11 04:13:42.624 GMT Thread[btpool0-2,5,main] (XID = 16673), (SESSIONID = 0), (DATABASE
= domains/geoff), (DRDAID = null), Cleanup action starting
2008-01-11 04:13:42.624 GMT Thread[btpool0-2,5,main] (XID = 16673), (SESSIONID = 0), (DATABASE
= domains/geoff), (DRDAID = null), Failed Statement is: DELETE FROM GEOFF__BLOG__USER WHERE
"PK"=385
ERROR 40XL2: A lock could not be obtained within the time requested.  The lockTable dump is:

2008-01-11 04:13:42.607 GMT
XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME                                         
                              |STATE|TABLETYPE / LOCKOBJ                   |INDEXNAME / CONTAINER_ID
/ (MODE for LATCH only)  |TABLENAME / CONGLOM_ID                |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*** The following row is the victim ***
16673     |TABLE        |X   |0        |Tablelock                                        
                              |WAIT |T                                     |NULL         
                                    |GEOFF__BLOG__USER                     |
*** The above row is the victim ***
16673     |TABLE        |IX  |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_REMINDERS           |
16673     |TABLE        |X   |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_REMINDERS           |
16673     |TABLE        |IX  |3        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER                     |
16674     |TABLE        |IX  |2        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER                     |
16673     |TABLE        |IX  |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_CONTACT             |
16673     |TABLE        |X   |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_CONTACT             |
16673     |TABLE        |IX  |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_NOTES               |
16673     |TABLE        |X   |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_NOTES               |
16673     |TABLE        |IX  |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_PICS                |
16673     |TABLE        |X   |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_PICS                |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2008-01-11 04:13:42.624 GMT Thread[btpool0-3,5,main] (XID = 16674), (SESSIONID = 3), (DATABASE
= domains/geoff), (DRDAID = null), Cleanup action starting
2008-01-11 04:13:42.624 GMT Thread[btpool0-3,5,main] (XID = 16674), (SESSIONID = 3), (DATABASE
= domains/geoff), (DRDAID = null), Failed Statement is: DELETE FROM GEOFF__BLOG__USER WHERE
"PK"=381
ERROR 40XL2: A lock could not be obtained within the time requested.  The lockTable dump is:

2008-01-11 04:13:42.607 GMT
XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME                                         
                              |STATE|TABLETYPE / LOCKOBJ                   |INDEXNAME / CONTAINER_ID
/ (MODE for LATCH only)  |TABLENAME / CONGLOM_ID                |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*** The following row is the victim ***
16674     |TABLE        |IX  |0        |Tablelock                                        
                              |WAIT |T                                     |NULL         
                                    |GEOFF__BLOG__USER_PICS                |
*** The above row is the victim ***
16673     |TABLE        |IX  |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_REMINDERS           |
16673     |TABLE        |X   |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_REMINDERS           |
16673     |TABLE        |IX  |3        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER                     |
16674     |TABLE        |IX  |2        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER                     |
16673     |TABLE        |IX  |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_CONTACT             |
16673     |TABLE        |X   |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_CONTACT             |
16673     |TABLE        |IX  |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_NOTES               |
16673     |TABLE        |X   |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_NOTES               |
16673     |TABLE        |IX  |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_PICS                |
16673     |TABLE        |X   |1        |Tablelock                                        
                              |GRANT|T                                     |NULL         
                                    |GEOFF__BLOG__USER_PICS                |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Cleanup action completed
Cleanup action completed


----- Original Message ----
From: Geoff hendrey <geoff_hendrey@yahoo.com>
To: Derby Discussion <derby-user@db.apache.org>
Sent: Thursday, January 10, 2008 6:23:43 PM
Subject: Re: why are table locks being made?


Thanks 
for 
the 
useful 
links. 
I 
really 
appreciate 
your 
help.

Let's 
assume 
that 
even 
though 
the 
table 
is 
indexed 
on 
the 
PK 
field, 
that 
the 
entire 
table 
is 
locked 
by 
the 
tx 
that 
executes 
the 
delete. 
That 
doesn't 
explain 
why 
a 
*deadlock* 
occurs. 
The 
only 
way 
a 
deadlock 
could 
occur 
with 
table-locking 
is 
if 
transaction 
A 
has 
TABLE1 
locked 
and 
requests 
a 
table 
lock 
on 
TABLE2. 
Concurrently, 
transaction 
B 
has 
Table 
2 
locked, 
and 
requests 
a 
table-lock 
on 
TABLE1. 
That 
would 
cause 
a 
deadlock. 

But 
as 
you 
can 
see 
from 
the 
deadlock 
dump 
below, 
I 
am 
only 
executing 
a 
simple 
DELETE 
FROM 
<TABLE> 
WHERE 
PK=<_PK>.

I 
just 
can't 
understand 
why 
that 
would 
lead 
to 
a 
*deadlock*. 
I 
can 
(grudgingly) 
accept 
that 
the 
transaction 
would 
lock 
the 
table, 
but 
why 
would 
lead 
to 
a 
deadlock? 

Also, 
if 
you 
could 
point 
me 
to 
docmentation 
that 
explains 
the 
meaning 
of 
the 
deadlock 
dump, 
that 
could 
be 
helpful. 
For 
example, 
what 
does 
this 
mean: 
"Granted 
XID 
: 
{7249, 
IX} 
, 
{7250, 
IX}"

thanks!
 
-geoff



----- 
Original 
Message 
----
From: 
Oystein 
Grovlen 
- 
Sun 
Norway 
<Oystein.Grovlen@Sun.COM>
To: 
Derby 
Discussion 
<derby-user@db.apache.org>
Sent: 
Thursday, 
January 
10, 
2008 
12:24:57 
AM
Subject: 
Re: 
why 
are 
table 
locks 
being 
made?


Some 
things 
to 
consider:

  
* 
Isolation 
level 
is 
only 
relevant 
when 
locking 
for 
reads.  
For
  
  
insert/update/delete 
locking 
is 
needed 
for 
the 
duration 
of 
the
  
  
transaction 
in 
order 
to 
guarantee 
the 
recoverability 
of 
the
  
  
database.

  
* 
If 
your 
table 
has 
a 
referential 
constraint 
to 
other 
tables, 
a
  
  
delete 
may 
cause 
cascading 
deletes 
in 
referred 
tables. 
(Ref.
  
  
http://db.apache.org/derby/docs/10.3/ref/rrefsqlj13590.html)

  
* 
The 
optimizer 
may 
choose 
to 
use 
table 
locking 
instead 
of 
row
  
  
locking, 
if 
it 
thinks 
that 
will 
be 
more 
efficient.  
If 
a 
table 
scan
  
  
is 
used, 
table 
locking 
will 
certainly 
be 
used.  
If 
the 
table 
is
  
  
very 
small, 
the 
optimizer 
my 
choose 
to 
use 
a 
table 
scan 
instead 
of
  
  
an 
index 
look-up.  
(E.g, 
If 
all 
records 
fit 
in 
one 
page, 
a 
table
  
  
scan 
will 
only 
access 
one 
page, 
while 
an 
index 
lookup 
will 
access
  
  
two 
pages.)  
See
  
  
http://db.apache.org/derby/docs/10.3/tuning/ctunoptimzoverride.html
  
  
for 
advice 
on 
how 
to 
force 
a 
query 
to 
use 
index 
lookup.

Hope 
this 
clears 
up 
a 
few 
things.  
Please, 
do 
not 
hesitate 
to 
ask 
more
questions 
if 
you 
are 
still 
not 
able 
to 
figure 
out 
how 
to 
solve 
your
problem.  
 
Posting 
your 
DDL 
and 
the 
query 
plans 
will 
also 
make 
it
easier 
to 
pinpoint 
your 
problem.  
(See
http://wiki.apache.org/db-derby/PerformanceDiagnosisTips 
for 
advice 
on
how 
to 
generate 
query 
plans).

--
Øystein

Geoff 
hendrey 
wrote:
 
> 
I 
execute 
10 
DELETE 
operations. 
Each 
DELETE 
is 
executed 
from 
its 
own 
thread 
and 
deletes 
a 
different 
row 
from 
the 
same 
table. 
I 
get 
the 
same 
behavior 
with 
both 
the 
embedded 
and 
network 
driver, 
on 
derby 
10.3.14.
 
>
 
>
 
>
 
> 
I 
am 
seeing 
deadlocks. 
Here 
is 
the 
deadlock 
dump:
 
>
 
> 
A 
lock 
could 
not 
be 
obtained 
due 
to 
a 
deadlock, 
cycle 
of 
locks 
and 
waiters 
is:
 
>
 
> 
Lock 
: 
TABLE, 
GEOFF__BLOG__USER_MESSAGES, 
Tablelock
 
>
 
>  
 
Waiting 
XID 
: 
{7250, 
IX} 
, 
BLOG, 
DELETE 
FROM 
GEOFF__BLOG__USER 
WHERE 
"PK"=822
 
>
 
>  
 
Granted 
XID 
: 
{7249, 
IX}
 
>
 
> 
Lock 
: 
TABLE, 
GEOFF__BLOG__USER, 
Tablelock
 
>
 
>  
 
Waiting 
XID 
: 
{7249, 
X} 
, 
BLOG, 
DELETE 
FROM 
GEOFF__BLOG__USER 
WHERE 
"PK"=830
 
>
 
>  
 
Granted 
XID 
: 
{7249, 
IX} 
, 
{7250, 
IX}
 
>
 
> 
. 
The 
selected 
victim 
is 
XID 
: 
7250.
 
>
 
>
 
>
 
> 
My 
question 
is, 
why 
are 
TABLE 
locks 
being 
issued? 
Why 
are 
any 
locks 
at 
all 
being 
issued? 
I 
have 
set 
the 
transaction 
isolation 
level 
to 
READ_UNCOMMITTED. 
My 
JDBC 
transactions 
have 
autocommit 
false. 
I 
am 
not 
issuing 
a 
LOCK 
TABLE 
or 
anything 
else 
that 
should 
cause 
the 
Tablelock.
 
>
 
>
 
>
 
> 
Also, 
since 
I 
never 
execute 
any 
delete 
against 
"GEOFF__BLOG__USER_MESSAGES", 
I 
am 
confused 
as 
to 
why 
this 
table 
appears 
in 
the 
dump. 
You 
can 
see 
that 
the 
delete 
statements 
both 
delete 
a 
different 
row 
from 
GEOFF__BLOG__USER, 
*not* 
from 
GEOFF__BLOG__USER_MESSAGES. 
Is 
it 
possible 
that 
there 
is 
some 
bug 
that 
causes 
derby 
to 
get 
confused 
about 
the 
long 
table 
names, 
one 
of 
which 
starts 
with 
the 
other?
 
>
 
>
 
>
 
> 
Any 
suggestions?
 
>
 
>
 
>








Mime
View raw message