Transaction protocol for reading database values
Techniques are provided for more efficient multi-row atomic, consistent, isolated and durable (ACID)-compliant transactions with snapshot isolation semantics (or just “multi-row transactions” for short). In some embodiments, the techniques are implemented in a computing system that includes a client application, a lightweight in-memory lease-based lock service, a multi-row transaction orchestrator, and an underlying database system. The transaction orchestrator implements a read protocol and a write protocol that provides support to the client application for carrying out multi-row transactions against the underlying database system irrespective of whether the database system itself supports multi-row transactions. The transaction orchestrator explicitly maintains transaction-level locks obtained from the lease-based lock service as part of the transaction protocol. Further, the transaction orchestrator is tolerant to lock service failure and unavailability without compromising ACID-compliance and snapshot isolation guarantees to the client application.
1. A method comprising the steps of:
receiving a request specifying a database table cell in context of a first transaction, wherein the database table cell stores a plurality of database values, and wherein each database value of the plurality of database values is associated with a respective write timestamp value;
in response to receiving the request:
reading a first database value from the plurality of database values;
validating the first database value based on the respective write timestamp value associated with the first database value and a transaction table, wherein the transaction table associates write timestamp values with transaction commit timestamp values;
wherein validating the first database value based on the respective write timestamp value associated with the first database value and the transaction table is based on:
acquiring a lock on a row of the transaction table from a lock service, the row comprising the respective write timestamp value associated with the first database value,
obtaining from the row a transaction commit timestamp value associated with the respective write timestamp value associated with the first database value while the lock is acquired, and
validating the first database value based on the transaction commit timestamp value read from the row; and
in response to validating the first database value, providing the first database value in context of the first transaction;
wherein the method is performed by one or more computing devices.
2. The method of claim 1 wherein validating the first database value comprises determining whether a second transaction that wrote the first database value to the database table cell committed before the first transaction started.
3. The method of claim 1 wherein validating the first database value comprises determining whether a second transaction that wrote the first database value to the database table cell has committed.
4. The method of claim 1 wherein validating the first database value comprises determining whether the respective write timestamp value associated with the first database value is associated with a valid transaction commit timestamp value.
5. The method of claim 1 wherein validating the first database value comprises determining whether a second transaction that wrote the first database value to the database table cell started before the first transaction started.
6. The method of claim 1 further comprising obtaining a timestamp value associated with a start of the first transaction, and wherein validating the first database value is further based on the timestamp value associated with the start of the first transaction.
7. The method of claim 1 , wherein the first database value is associated with the respective write timestamp value in the database table cell, and wherein obtaining the respective write timestamp value associated with the first database value includes reading the respective write timestamp value from the database table cell.
8. A system comprising:
one or more processors;
one or more non-transitory computer-readable storage media coupled to the one or more processors and storing one or more sequences of instructions which, when executed by the one or more processors, cause performing the steps of:
receiving a request specifying a database table cell in context of a first transaction, wherein the database table cell stores a plurality of database values, and wherein each database value of the plurality of database values is associated with a respective write timestamp value;
in response to receiving the request:
reading a first database value from the plurality of database values;
validating the first database value based on the respective write timestamp value associated with the first database value and a transaction table, wherein the transaction table associates write timestamps with transaction commit timestamp values;
wherein validating the first database value based on the respective write timestamp value associated with the first database value and the transaction table is based on:
acquiring a lock on a row of the transaction table from a lock service, the row comprising the respective write timestamp value associated with the first database value,
reading from the row a transaction commit timestamp value associated with the respective write timestamp value associated with the first database value while the lock is acquired, and
validating the first database value based on the transaction commit timestamp value read from the row; and
in response to validating the first database value, providing the first database value in context of the first transaction.
9. The system of claim 8 wherein validating the first database value comprises determining whether a second transaction that wrote the first database value to the database table cell committed before the first transaction started.
10. The system of claim 8 wherein validating the first database value comprises determining whether a second transaction that wrote the first database value to the database table cell has committed.
11. The system of claim 8 wherein validating the first database value comprises determining whether the respective write timestamp value associated with the first database value is associated with a valid commit timestamp value.
12. The system of claim 8 wherein validating the first database value comprises determining whether a second transaction that wrote the first database value to the database table cell started before the first transaction started.
13. The system of claim 8 wherein the one or more sequences of instructions, when executed by the one or more processors, further cause performing the step of: obtaining a timestamp value associated with a start of the first transaction, and wherein validating the first database value is further based on the timestamp value associated with the start of the first transaction.
14. The system of claim 8 , wherein the first database value is associated with the respective write timestamp value in the database table cell, and wherein obtaining the respective write timestamp value associated with the first database value includes reading the respective write timestamp value from the database table cell.
15. A non-transitory storage media storing one or more sequences of instructions which, when executed by one or more processors, cause:
receiving a request specifying a database table cell in context of a first transaction, wherein the database table cell stores a plurality of database values, and wherein each database value of the plurality of database values is associated with a respective write timestamp value;
in response to receiving the request:
reading a first database value from the plurality of database values;
validating the first database value based on the respective write timestamp value associated with the first database value and a transaction table, wherein the transaction table associates write timestamps with transaction commit timestamp values;
wherein validating the first database value based on the respective write timestamp value associated with the first database value and the transaction table is based on:
acquiring a lock on a row of the transaction table from an in-memory lease-based lock service, the row comprising the respective write timestamp value associated with the first database value,
reading from the row a transaction commit timestamp value associated with the respective write timestamp value associated with the first database value while the lock is acquired, and
validating the first database value based on the transaction commit timestamp value read from the row; and
in response to validating the first database value, providing the first database value in context of the first transaction.
16. The non-transitory storage media of claim 15 wherein validating the first database value comprises determining whether a second transaction that wrote the first database value to the database table cell committed before the first transaction started.
17. The non-transitory storage media of claim 15 wherein validating the first database value comprises determining whether a second transaction that wrote the first database value to the database table cell has committed.
18. The non-transitory storage media of claim 15 wherein validating the first database value comprises determining whether the respective write timestamp value associated with the first database value is associated with a valid commit timestamp value.
19. The non-transitory storage media of claim 15 wherein validating the first database value comprises determining whether a second transaction that wrote the first database value to the database table cell started before the first transaction started.
20. The non-transitory storage media of claim 15 wherein the one or more sequences of instructions, when executed by the one or more processors, further cause performing the step of: obtaining a timestamp value associated with a start of the first transaction, and wherein validating the first database value is further based on the timestamp value associated with the start of the first transaction.