Hi Dunbar,

Dne 3.6.2011 9:14, Dunbar, Norman (Capgemini) napsal(a):

>
> Morning Milan,
>
> >> I'm having troubles with deadlocks in very strange behaviour.
> I hate to say this, but almost always deadlocks are caused by the
> application. I see this all the time in my line of work. All databases
> will deadlock if the application gets it wrong. :-(
>
> >> Situation - one or more connections to a database, one
> >> running update query per connection (long term query - more than
> hour) but
> >> every query use some records which another query doesn't.
> Transactions
> >> are set to wait, but I don't think this is a problem.
> Waiting transactions *are* a problem. If the transaction is set to not
> wait, it will raise an exception if the resources it needs to lock are
> currently locked. They will wait forever for the resource it needs and
> if "this" transaction holds resources that "that" transaction needs, and
> vice versa, then both will wait "forever".
>
> Have you tried setting the transactions to wait for a limited time?
>

Not yet

>
> >> There is a firebird.log entry that says database name and "deadlock"
> >> message, but application freeze.
> Well, if you have the application in a deadlock state, it will most
> likely freeze. Those waiting transactions will wait forever. (Subject to
> any engine handling of deadlocks of course!)
>

I can understand why application freeze and I'm not solving this
freezing but a deadlock state of Firebird.
Imagine situation, that you have only one query running against DB with
simple update of 1 table (no other tables are involved) and it sometimes
goes into deadlock too. That's what I can't understand to. And in this
case - I think - doesn't wait or no_wait matter. I'm I right?
Another situation is, that I'm running multiple queries at one time
against 1 table, but every of the query is updating records that are not
a subject to update for other queries (and again - no other tables are
involved) and deadlock is sometime happening again.

>
> >> Any idea what to do to avoid this?
> NO WAIT or WAIT n transactions. Then check the application logic for
> resource acquisition. A "rule" used to be get everything in alphabetic
> order and if everything does this, then deadlock possibilities are
> reduced.
>
> So session 1 gets tables a, b, c and when session 2 tries to get a, it
> finds it locked. Had session 2 already locked C, then there would be a
> deadlock.
>
> Other advice, commit "frequently" - however, it has to be done where
> appropriate. Don't just commit for the sake of it. The transaction has
> to work fully or not at all. I've seen too many (oracle) programs which
> commit in a loop to "avoid holding locks" - hopeless and a right
> nightmare when it all falls over - with half completed, but committed,
> transactions.
>

Unfortunatelly - in this case it's impossible, because this is just one
update query and it's run just once. Transaction is committed right
after query is executed (but it can take a few hours).

>
> Up the frequency of the DeadlockTimeout scanner in firebird.conf *might*
> help detect them, but can also reduce efficiency of the database. The
> default is 10 seconds.
>
> However, when a deadlock is detected by the engine, it picks one of the
> two transactions and sends it a deadlock exception. It is up to the
> application to trap that and handle it appropriately - rolling back and
> trying the transaction again perhaps.
>

Another strange behaviour - no exception is raised.

>
> I'm sure there are other methods of deadlock prevention, but I've not
> had enough coffee yet! Part 6 of Helen's Firebird Book has a good
> write-up on deadlocks, livelocks and deadly embraces. Plus, a note in
> Chapter 40 page 858, to the effect that the lock manager may report as
> deadlocks, things that are not quite deadlocks.
>

Just for you info - I'm attaching 2 dumps of lock manager dumps and it
seems that it doesn't contain any deadlock. But I don't understand this
dump too much so I can be wrong.

>
> Good hunting!
>
> Cheers,
> Norm. (At work!)
>
> Norman Dunbar
> Contract Senior Oracle DBA
> Capgemini Database Team (EA)
> Internal : 7 28 2051
> External : 0113 231 2051
>

Thanks a lot for your answer

Milan

>
>

[Non-text portions of this message have been removed]