[Dbi-interbase-devel] Deadlocks galore

[Flemming F. <di...@sw...>]

Hiyall, I've been using DBD::InterBase in ny mod_perl application, it
seems to work nicely, but when the server is loaded many apache processes
start getting sigv and/or spouting "Deadlock detected" type errors.

This happens both when using DBI ans well as Apache::DBI.

I use InterBase to store the session data, so I start each request with a
select and end it with an update.

I end off my script with $dbh->commit; $dbh->disconnect;

Changing the transaction isolation mode from concurrent to read_committed
fixes the deadlocks right up.
This workaround is wrong as I really want my transactions to be atomic.

I suspect that this problem stems from the fact that all connections
always have an active transcation, which IMHO is wrong, once a transaction
is committed there should be *no* transaction until the next time one is
needed.

Commit-retaining should IMHO only be used in autocommit mode (it only
matters in autocommit mode as commits are more infrequent in normal mode, 
so the performance hit from starting a new transcation is much smaller)

Not having an active transcation after a commit is important, or you will
have active transactions across requests (this may be a very long
time) and a newly started reuest will be working with a really
(really) old snapshot in stead of a new one.

I would suggest that:
* commit_retaining should only be used for autocommit after queries.
* Explicit commits/rollbacks always commit_transcation /
rollback_transaction and then end the transaction, completely.
* any operation that needs a query should check to see if one is active
and start one if not.

I don't know if these changes would fix my immediate problem, but they are
correct and I expect they will fix my problem.