Re: Theoretical Basis for SELECT FOR UPDATE

"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?

> > > > 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.

> 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. 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. 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.

Roy Received on Tue Oct 04 2005 - 13:18:19 CEST