Subject Re: [firebird-support] Transaction isolation and server type
Author Helen Borrie
At 10:28 AM 19/08/2005 +1000, Ryan Thomas wrote:
>Hi All,
>I'm not sure if this is more for the IBO list or the fb-support list.
>We are running a system that uses two different transactions on the client
>application - one for read-only and one for read-write operations. Our
>transaction isolation settings were set to tiCommitted for both of them.
>This works fine on the vast majority of our customers - which are all
>running super-server.
>Last night we ran into a problem with our only customer who is running
>classic server -

>whenever nearly any query is run we get a lock conflict on
>no wait transaction (as if one of the transactions is set to tiConsistency).

What exactly do you mean by "nearly any query"? Are you getting lock
conflicts on the SELECT that is in the read-only transaction, or only on
submitting the DML statements in the read-write transaction?

>Setting the read-only transaction to tiConcurrency resolves the lock
>conflict but brings in a bunch of refresh issues (i.e. to display the
>changes made by the rw transaction).

Of course. The read-only transaction must be in Read Committed, so as not
to make that transaction "interesting", from a garbage-collection POV.

What is of concern for running two Read Committed transactions side-by-side
is the visibility to one transaction of uncommitted work from the other.
The transaction attribute of interest here is RECORD VERSION/NO RECORD
VERSION. Since you are using IBO, pick up the documentation in the Help
file for the RecVersion property. It gives a very telling account of what
you have described here, if RecVersion on your read-only transaction is
false and LockWait is false. It will prevent any other transaction from
making changes, including the dummy change that occurs when you use IBO's
statement-level PessimisticLocking feature.

Needless to say, similar conflict will occur when other users attempt to
add or update records from *their* transactions.

>Is there some sort of configuration setting that needs to be set on classic

There's no difference as far as transaction-by-transaction control is
concerned - it's the same server in different outerwear. If the
application code is identical then the "different behaviour" is
coincidental, attributable to variations in usage.

>Also, I have google'd for a build of SS for FreeBSD but can't seem to find
>one - are there any issues with building SS under FreeBSD that anyone knows

Yes. Building from source isn't in the Firebird-support bailiwick but, as
far as I can tell, there has never been a released build of SS for FreeBSD
because of threading issues that couldn't be resolved. You might like to
search the firebird-devel archives for recent activity by the FreeBSD
afficionados, regarding attempts to build Fb 2 on FreeBSD 5.x. On the
whole, they are the same people who attempted the Fb 1.5 SS builds, so it
would be appropriate to ask the pertinent questions in that forum.