Data versioning is needed not only to speed up, but also to resolve some types of conflicts that may arise.
✓ Everything is intuitively clear – in order not to wait for a lock, we take the previous version.
If a resource is blocked, we can look at its old version and start working with it. If, for example, the lock was such that the transaction that locked did not change anything on this resource, then we can continue executing the transaction. If there was a change and a new, more recent version of the data appeared, our transaction will have to re-read them.
In any case, this is usually faster than waiting a long time for a lock. If you remember the old MS SQL Server and old versions of DB2, it’s a terrible thing, then, if there were a lot of locks there, then their escalation began – everything worked badly and it was hard to live with it.
✓ All modern DBMS to one degree or another are “versioned”
Oracle, PostgreSQL, MySQL – all “versioners” are honest. DB2 is a little more original on this topic, it has its own mechanism – they store only one previous version.
This is the timetable I drew before, but a little more complex. There are more transactions (3), more resources (there are more z) and 2 commits. That is, both transactions end with a commit.
As mathematicians say in such cases: “It’s easy to notice …” – I really love it, especially when there is a formula on half of the board. Indeed, one thing is easy to notice here. As a homework assignment, try to understand why this is easy to spot.
I’ll tell you. This schedule is never serialized for the simple reason that the operation r1 (y) would cause a conflict, possibly even a deadlock, if a previous version of y is not available.
That is, if the previous version y is available here, then the transaction will complete normally, there will be no problems. If this version of y does not exist, then the operations will conflict.