Re: Theoretical Basis for SELECT FOR UPDATE

Roy Hann wrote:
> "vc" <boston103_at_hotmail.com> wrote in message
> news:1128422806.479135.69910_at_g49g2000cwa.googlegroups.com...
> > > It just
> > > happens, and you have to know it happened and know what to do about it.
> And
> > > I say I *can't* know what to do about it.
> >
> > See above.
>
> I did, but I still can't see how I am supposed to know what to do about an
> automatic rollback. Please humour me: suppose the DBMS tells me that it has
> automatically rolled back my transaction. What can I reasonably do about
> that?

In the first place, the application should be designed with a proper transactional model in mind. One among many other approaches is to avoid loops in the 'wait-for' graph which practically means accessing(locking) resources by concurrent transactions in the same order. There is ample literature on deadlock avoidance you might find interesting to read.

If one deals with spaghetti code, then short of redesigning your application properly so that it avoided deadlocks, there is not much choice but to restart the failed transaction. What's unreasonable about restarting a transaction that failed due to a deadlock ?

>
> > > > > A bigger problem however is that by default you are entitled to
> > > > > read-consistency within a transaction.
> > > >
> > > > Apparently, you've meant the READ COMMITTED i.l., the default i.l.
> > >
> > > I am entitled to read-consistency with an isolation level of REPEATABLE
> READ
> > > and SERIALIZABLE.
> >
> > What's that supposed to mean ? As I said before, by default, the
> > transaction starts in RC not in RR or SERIALIZABLE, unless the
> > programmer set the level explicitely. Are you saying that the implicit
> > transaction starts with the i.l. other than RC ?
>
> A transaction may start with something other than SERIALIZABLE isolation,
> but the SQL92 standard says SERIALIZABLE is the default. I grant you that
> the DBMS software may have been installed with a different local default, or
> even that the product may deviate from the standard in this respect. But
> unless we are going to confine ourselves to a specific product (which would
> be uninteresting) I would like to assume standard-compliant SQL92 behaviour
> for this conversation.

Whilst SERIALIZABLE is indeed the standard '92 isolation level, it's a strange attitude to ignore the specific database pecularities. Oracle,  Sybase and MS SQL Server all use READ COMMITTED as the default i.l., DB2 uses REPEATABLE READ, etc. Unfortunately, database 'independence', beyond simplistic SQL statements, is a myth, especially with respect to their dramatically different concurrency control mechanisms. One can of course write portable code that would perform equally abysmally on all the databases, no doubt about that.

>
> > Besides, what do you mean by "read consistency" ? The RR il does not
> > prevent all the anomalies.
>
> No, but AFAIK the only anomaly RR doesn't prevent is phantom reads.

Yes.

> It
> certainly does guarantee one kind of read consistency (i.e. REPEATABLE
> READ), which I understand to be the following: I am entitled to expect that
> using the same WHERE clause a second time *in the same transaction* will
> match at least all the rows it matched the first time, and that the value of
> any attributes I looked at the first time will be unchanged the second time.

You'll get repeatable rows allright, but not repeatable aggregates (sum/avg/etc) with the RR i.l.

> For doing just SELECTs that need not be a problem, but if I SELECT and then
> UPDATE within the same transaction there is a problem because many (?most)
> products have to use locking to prevent the conflict.

Please elaborate.

>
> Roy
Received on Tue Oct 04 2005 - 15:48:22 CEST