Hi Simon,
Simon Slavin writes:
> On 28 Mar 2011, at 8:45pm, Boris Kolpackov wrote:
>
> > As expected, I periodically get deadlocks (SQLITE_LOCKED return code from
> > unlock_notify()) for the second transaction due to the read to write
> > lock upgrade. But I also get deadlocks reported for the first transaction
> > and this is something that I didn't expect to happen. My reasoning is
> > that this transaction tries to get the write lock right away so I don't
> > see how it can be blocking some other transaction. Can someone explain
> > this?
>
> You're not using BEGIN IMMEDIATE, so your lock isn't happening when you
> start the transaction, it's happening on the first command which makes
> a change. By that time another thread may have already done something.
>
> Although it will theoretically increase the amount of contention it
> might be worth using BEGIN IMMEDIATE instead of just BEGIN. Try this
> just for testing purposes: it's a trivial change to your code and
> simple to reverse. If your deadlocks go away then you can make it
> permanent. Since you apparently have the INSERT and UPDATE commands
> planned before you start your transaction won't lock up the database
> for long.
I tried this. If I add IMMEDIATE (or EXCLUSIVE) to the first transaction
(three INSERTs), nothing changes, I still get deadlocks reported for
this transaction and the second. This lack of any difference is what I
would expect since the first statement that this transaction executes
(INSERT) is a write. So by adding IMMEDIATE/EXCLUSIVE all I did is
changed the time when the write lock is acquired, namely from the
first INSERT to BEGIN.
If I add IMMEDIATE to the second transaction (SELECT then UPDATE), then
the deadlocks go away for both transaction as one would expect. But that
doesn't help me much. My problem is not that I get deadlocks -- I expect
to get them for the second transaction (read to write upgrade). My problem
is that I get deadlocks in the first transaction (goes straight to the
write lock). This I don't understand.
Thanks,
Boris
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
