We think we have managed to get a snapshot view in the auditing
program by using a custom Serializable isolation level of
FirebirdConnection.TPB_CONCURRENCY, FirebirdConnection.TPB_WAIT,
FirebirdConnection.TPB_READ

ie Concurrency instead of consistency.


This now seems to temporarily crash the database and cause a "Resource
Exception. Unable to complete network request" on the auditing and the
transaction program.

Does anyone know of any reason why this should crash the database?

Also, we are finding unusual behaviour in our transactions. We have a
transaction in the transaction program thusly:

Set autoCommit false;
UPDATE member SET balance=balance-purchaseValue;
INSERT INTO purchases (id, username, pruchaseValue);
commit;

But our snapshot in the auditing program shows the old balance but
with the new purchase. We thought surely this should be an all or
nothing transaction. Any ideas?

Many thanks again,

Alex.

--- In Firebird-Java@yahoogroups.com, "imoffskating" <alexn@x> wrote:
> Thank you for reply.
>
> We have tried this and repeatable read allows phantom reads which
> means that money that has been moved is counted twice because we are
> summing a log of transfers.
>
> we are happy with read committed on the transfer transactions as all
> the updates are inline. Strangely though repeatable read caused
> deadlocks whereas read committed does not.
>
> As far as we see a read only serializable connection should not
> deadlock for the auditing program. But we have found this to be the
case.
>
> Many thanks again,
> Alex.
>
>
>
> --- In Firebird-Java@yahoogroups.com, David Jencks <david_jencks@y...>
> wrote:
> > I think you want your auditing transaction to be what results from
> > setting repeatable read via jdbc. In firebird that will give you a
> > permanently consistent view of the data. I believe the
serializable tx
> > mode introduces some locks to actually serialize the transactions.
> >
> > Why aren't your transfer transactions also repeatable read? I would
> > think that using read committed could easily introduce
inconsistencies
> > if there is any overlap between the accounts in two transactions.
IIUC
> > you will not lose any speed by using repeatable read instead of read
> > committed.
> >
> > thanks
> > david jencks
> >
> > On Sep 2, 2005, at 8:56 AM, imoffskating wrote:
> >
> > > Hello,
> > >
> > > We are running Firebird 1.5.2 SS on linux with jaybird jdbc.
> > >
> > > We are writing a program that incorporates a money transfer system.
> > > The money is "transferred" between tables by the main program. The
> > > transfer is within a transaction (i.e. 2 updates - deduct some
money
> > > from one table, increase money in other table).
> > >
> > > Our transaction isolation is set to read_committed.
> > >
> > >
> > > Another program needs to take a snapshot of all committed
> transactions
> > > at one time to make sure all the money "adds up" and no money has
> gone
> > > "missing" by our faulty code etc.
> > >
> > > As far as we understand this checking program should be in
> > > serializable mode, read only and function correctly.
> > >
> > > What we've found is the check program gets a GDS deadlock exception
> > > which we thought would not happen as serializable uses a
snapshot of
> > > the db.
> > >
> > > Does anyone have any advice on how to properly set up our isolation
> > > level correctly for the check program to work without deadlocks and
> > > only see data committed at its start time?
> > >
> > > Many thanks,
> > >
> > > alex.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > SPONSORED LINKS
> > > Basic programming language
> > > Computer programming languages
> > > Programming languages
> > > Java programming language
> > > The history of computer programming language
> > >
> > > YAHOO! GROUPS LINKS
> > >
> > > ▪  Visit your group "Firebird-Java" on the web.
> > > Â
> > > ▪  To unsubscribe from this group, send an email to:
> > > Â Firebird-Java-unsubscribe@yahoogroups.com
> > > Â
> > > ▪  Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> > > Service.
> > >
> > >