I am reading "Database Systems" by Kifer, Bernstein, and Lewis. In chapter 19 on transaction models, it discusses how a flat transaction can adhere perfectly to ACID, but other transaction models are needed that sacrifice some of the ACID properties (such as isolation) for gains in performance, flexibility, etc...

The archetypal example given is a flat transaction for a multiple segment international flight reservation: London/NY/Chicago/DesMoines with oversea and domestic trip segments where if a domestic segment (NY/Chic/DM) needs to be rerouted (NY/St Louis/DM) a flat transaction has to abort completely and loses a successful subtransaction (London/NY), but with savepoints after each segment the transaction can roll back to NY and reroute from there.

I have 2 questions:

If adding savepoints is the only change made to the flat transaction, does it still belong to the flat transaction model category or is it called something else (it is not Distributed, Nested, or Chained, but is there a category name for it or is it just called flat transaction with savepoints?)

Which of the ACID properties, if any, are sacrificed by adding the savepoints to a flat transaction? It seems to me even with savepoints/rollbacks it is still atomic, consistent, isolated, and durable. Are all ACID properties maintained and is the only drawback the cost of creating the savepoints?

I agree with you: the use of savepoints makes no difference to the ACID properties of a transaction. The key point: once you finally commit or roll back the entire transaction, no one will be able to tell from the database state whether you set and/or rolled back to any savepoints during the transaction.

I am not much in favour of save points. Considering the entire business MST, it is inherently not consistent and isolated since other as far as other users are concerned, your transaction is visible after the save point (but before the entire MST is done).

A better option would be to have a single begin work and commit without any intermediate save points.

End

Is that so, for some DBMSs? In that case I'd agree that the ACID nature of the transaction is broken by savepoints on those DBMSs. I am used to Oracle, where this certainly is not the case, and where ACID properties are maintained over savepoints, so they can be safely used.