1

Suppose, I have a table product with integer column price.

Here are two transactions that I begin at the same time (almost):

T1:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE "product"
SET price = 13;

T2:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

insert into "product" (id, price) values ('three', 10);

Now suppose I commit T2 first. And then T1.

My result after both transactions finish is:

| id  |  price
---------------
| one | 13   |
| two | 13   |
| three| 10  |

This is perplexing to me as I would have expected the serialized order to be T2 -> T1 (since T2 finished much befor) but the result set implies the serialized order is T1 -> T2.

Also, I might also have been okay with T1 getting aborted because of T2 committing.

  • Possible duplicate. It all has to do with MVCC and the transaction level. To blow your mind more, commit one transaction then run select in the other. – Michael Kutz May 27 at 10:42
  • The other transaction does not recognize the effect of the committed transaction which is what I expected. – tselvan May 27 at 12:16
1

The serializable isolation says that the result of concurrent transactions has to be consistent with one serial order, no matter which one.

Here there are two possible serial (sequential) orders, T1 followed by T2 and T2 followed by T1.

The resut of T1 followed by T2 would be:

| id  |  price
---------------
| one | 13   |
| two | 13   |
| three| 10  |

It happens to be exactly the result you got in the concurrent execution of T1 and T2.

This is why there is no reason for the engine to raise a serialization exception. This particular concurrent execution meets the condition of success of the serializable isolation level.

I would have expected the serialized order to be T2 -> T1 (since T2 finished much befor) but the result set implies the serialized order is T1 -> T2.

Here you are confused about how it works. The isolation level does not influence the order of execution. It merely detects serialization anomalies to raise an error when they happen (and also in fact, when they might happen, because false positives are possible).

  • @a_horse_with_no_name: isn't it the other side of the same coin? If an event might happen, it might also not happen. – Daniel Vérité May 27 at 11:40
  • Thanks. Is there an isolation level which will give us a well ordered serialization? Even strict serializability seems to be partial order. – tselvan May 27 at 12:09
  • @tselvan: No, serializable is the more isolated. See postgresql.org/docs/current/transaction-iso.html. Maybe what you're after is transaction synchronization (manual, through locks) as opposed to isolation. – Daniel Vérité May 27 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.