On 2019-09-26 10:26, procar informatik AG - Sascha Horn s.horn@...
[Firebird-Java] wrote:

> Hi Mark, hi all,
>
> Since a view months we have detected a strange database behaviour in
> our
> product, which we do not understand.
>
> In some cases (we are not sure what exactly is the cause) something is
> locking a table in a manner, that also the select statements on this
> table will be locked.
>
> Even a "SELECT COUNT(*) FROM TABLE" gives no response until (we guess)
> transaction reaper kills the locking transaction.

I'm not sure what you mean with transaction reaper.

> After that all waiting selects answers and everything is working.
>
> Some backgrounds:
>
> Jaybird V3.0.5
>
> Our Connection settings use
> TRANSACTION_READ_COMMITTED=isc_tpb_read_committed,isc_tpb_no_rec_version,isc_tpb_write,isc_tpb_wait
>
> org.firebirdsql.ds.FBXADataSource in JBoss Application Server
>
>
> We need help / ideas how to analyse / solve this issue, because we have
> no idea why a normal select will be locked.

The problem is likely your use of isc_tpb_no_rec_version combined with
isc_tpb_wait. In that combination, uncommitted updates (or deletes) to
records cause a select to wait for the update to be committed or rolled
back instead of reading the latest committed version. It is probably
better to switch to rec_version (which is the default), or use no_wait
and retry your transaction. Finding out exactly what is locking is hard,
but I'd suggest using the monitoring tables to look for long running
transactions.

Mark