How to avoid deadlocks?
riv
I'm getting intermittent deadlocking when running multi-threaded. I
understand that table locking needs to be done in a consistent order
to avoid deadlocks, but h2 is subverting my attempts at consistent
locking order. When doing an insert, I'm performing a "select null
from table1, table2 where null = null for update" to make sure it
locks table1 and then table2. On queries, however, I don't want to do
that since I don't want to force an exclusive lock. My query looks
like this: "select table1.id, count(*) from table1 natural join table2
group by table1.id". Stepping through the h2 code reveals that when it
chooses an optimized plan, it reorders the table table filters such
that table2 always gets locked first, causing deadlock.
Is there a way to force it to lock the tables in the right order with
a shared lock, or do I have to get an exclusive lock?
Thomas Mueller
I will document that:
To avoid deadlocks, ensure that all transactions lock the tables in
the same order (for example in alphabetical order), and avoid
upgrading read locks to write locks. Both can be achieved using
explicitly locking tables using SELECT ... FOR UPDATE.
See also http://en.wikipedia.org/wiki/Deadlock#Prevention
Regards,
Thomas
riv
(shared) lock in an explicit order? I have to get a write (exclusive)
lock every time I want to do a join?
On Mar 11, 12:31 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:
Thomas Mueller
> So am I to understand that H2 provides no way to request a read
> (shared) lock in an explicit order? I have to get a write (exclusive)
> lock every time I want to do a join?
Do you mean you want to lock multiple tables in a specific order using
one SQL statement? No, currently this is not supported. You need to
use multiple SQL statements. In theory you could combine them into one
statement as follows, but this is a hack, and I'm not sure if it will
be supported in future versions:
Statement stat = ...
stat.execute("select * from a for update; select * from b for update");
Regards,
Thomas
riv
specific order using a single SQL command, but with a shared lock, not
an exclusive lock.
Using "select ... for update" gets an exclusive lock, and with
exclusive locks it isn't a problem to use multiple SQL commands since
the locks persist until the transaction completes.
My problem is with shared locks. If I just do a "select" without the
"for update", I'll get a shared lock on the tables in the join, but
the order that the locks are obtained is undefined. As you've said,
the way to avoiding deadlocks is to always lock in the same order.
Since the order of the locks is undefined, I can't always lock in the
same order, so I get deadlocks.
On Mar 16, 1:21 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:
Thomas Mueller
> Well, actually what I want is the ability to lock multiple tables in a
> specific order using a single SQL command, but with a shared lock, not
> an exclusive lock.
I don't know how other databases do that. For H2, you could use:
Statement stat = ...
stat.execute("select * from a where 1=0; select * from b where 1=0");
Regards,
Thomas
Maaartin
wrote:
> > specific order using a single SQL command, but with a shared lock, not
> > an exclusive lock.
>
> I don't know how other databases do that. For H2, you could use:
LOCK TABLES a READ, b READ;
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
Thomas Mueller
I will add a feature request for LOCK TABLES. If you need it, please
submit a patch.
Regards,
Thomas