Task $4(3$ marks$)$

Processing database transactions at READ COMMITTED isolation level

Consider the following anonymous $P\frac{L}{p}gSQL$ block implementing a database transaction T$.(1)1$ mark

Show a sample concurrent execution of a database transaction $T$ together with another database transaction such that a transaction T displays an incorrect result. When visualizing a concurrent execution use a technique of two$-$dimensional diagrams presented to you during the lecture classes, for example, see a presentation $14$ Transaction Processing in PostgreSQL slide $8.$

$(2)1$ mark

Explain all details on why a concurrent execution of a transaction T with another transaction may contribute to incorrect results displayed by a transaction $T.$

$(3)1$ mark

Explain how the anonymous $P\frac{L}{p}gSQL$ block listed above and processed as a transaction T at READ COMMITTED isolation level should be changed to always get the correct results.

Remember that after your modifications a transaction T still must be processed at READ COMMITTED isolation level and locking of entire relational table is unacceptable solution.

When visualizing a concurrent execution use a technique of two$-$dimensional diagrams presented to you during the lecture classes, for example, see a presentation $14$ Transaction Processing in PostgreSQL slide $8.$

Deliverables

A file solution$4.$pdf with the solutions of the problems $(1),(2)$ and $(3)$ included in Task $4.$