On 10/15/07, Syan Tan <kittylitter@xxxxxxxxxxxxx> wrote: > thanks. I'll try it out, but sounds true enough; so there is no > isolation level where transaction state keeps track of all the read versions > of any item read so far by the transaction ? Right. That would be a whole different set of semantics. > The main question is whether there's a way of avoiding implementing > either those patterns of offline optimistic locking (version checking) > or offline pessimistic locking ( lock table checking) , when > you've got interactive client applications ? Those are the only two methods I'm aware of. If anyone else knows of others, I'm sure they'll speak up. > If there was an isolation level that kept track of the versions of items > a transaction has read, and there was a "commit with continue" command, then > you could avoid that extra work in clients - or maybe warping transactions > for this purpose makes it more difficult to write transactions for > the usual batch processing purposes. Transactions are governed by the SQL standard, so changing that would not really be an option anyway. Even so, trying to implement that behavior (in any form) would get really complicated when you consider situations other than single identifiable rows. For instance, if you do "SELECT * FROM foo LIMIT 5" you essentially get 5 arbitrary rows back. If they don't actually have a primary key, you can't identify them individually. What would "previously read" mean in such a situation? Or what about rows constructed via joins from other tables? There is more than one way to join data; if you read the same rows from the underlying tables, but join them in a different way to get the final result, does that count as being previously read or not? What about aggregate results; is that one previously read row, or all of the rows that went into the aggregate? What if all of this is happening through a VIEW, and the application doesn't know it's coming from the same tables underneath? Using transactions as a unit of isolation works well because they don't have any semantics that rely on specific rows; they cover what data you are able to see and that's about it. An application can understand that a row is individually identifiable because that's how it was designed, but in general terms there's no such guarantee. Trying to define that kind of isolation level in the general case would be really hard. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
