What is serializable transaction model?

With serializable=false, if there is a conflict at the record level there is no expectation that the “youngest” transaction will fail. Whoever got to the record later will fail with “Concurrent update or delete of same row “.

There are other things to be careful of when setting serializable=false. See below (I suppose this ought to be saved somewhere where people can look it up).
Sunil

NPS transaction management makes certain guarantees. A transaction sees a consistent database state reflecting just those transactions that committed before it started, regardless of which transactions commit after it starts. (This we accomplish using “invisibility lists”.) This is true regardless of whether serializable is false or true.

With serializable=false, we disallow concurrent updates or deletes of the same row. (Where “concurrent” means neither transaction committed before the other started.) We do not, however, guarantee that if the application is trying to preserve inter-row constraints (such as uniqueness or referential integrity) they will actually be preserved. Consider for example the following two transactions, one entering an order for a part, one deleting a part that currently has no orders:

— T1:
begin;
select * from parts where name=’widget’;
— if the part exists:
insert into orders (‘widget’, …);
commit;

— T2:
begin;
select count(*) from orders where part=’widget’;
— if the count is zero:
delete from parts where name=’widget’;
commit;

If these two run concurrently (neither one can see what the other is doing) with serializable=false, you wind up with a dangling order for a part (‘widget’) that doesn’t exist any more. Note that the transactions do not conflict at the level of the records they insert/delete/update, so both changes go through.

With serializable=true (the default), one or the other of the above will fail with a Could not serialize error.
This is because NPS will detect two read-write serialization edges causing a cycle:
* T2 must precede T1 because T2 is reading orders which T1 modifies (and T2 can’t see T1’s changes).
* T1 must precede T2 because T1 is reading parts which T2 modifies (and T1 can’t see T2’s changes).
To break the cycle, one or the other must be aborted.

Note that NPS detects serialization edges at the table level, which means transactions can
form a cycle even if they’re reading/modifying completely different data.
For example, if T2 above were dealing with ‘socks’ instead of ‘widget’ one or the other
transaction will still fail (if serializable=true).

Related:

Leave a Reply