Subject Re: read only deadlocks
Author imoffskating
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.
> >
> >