Concurrency control for transactions in database systems

Patent metadata

Field	Value
Publication number	US-11899648-B2
Application number	US-202217821581-A
Country	US
Kind code	B2
Filing date	Aug 23, 2022
Priority date	Sep 22, 2020
Publication date	Feb 13, 2024
Grant date	Feb 13, 2024

Abstract

The subject technology inserts, by a first transaction, a new version of an object, the first transaction including a first statement to perform an update operation to a row in a first table, the object corresponding to data in the row to be updated, the first statement including information comprising an object key associated with the object. The subject technology performs, by a second transaction, a range read, the range read including information indicating the object key. The subject technology receives a set of conflicting transactions from the range read. The subject technology determines that a conflict occurred between the first transaction and a third transaction from the set of conflicting transactions. The subject technology performs a restart of the first transaction in response to determining that the conflict occurred.

First claim

What is claimed is:

1. A system comprising:
at least one hardware processor; and
a memory storing instructions that cause the at least one hardware processor to perform operations comprising:
inserting, by a first transaction, a new version of an object, the first transaction including a first statement to perform an update operation to a row in a first table, the object corresponding to data in the row to be updated, the first statement including information comprising an object key associated with the object, the object key comprising additional information being appended as a suffix to the object key, the additional information comprising a first transaction identifier associated with the first transaction and a first statement number associated with the first statement;
performing, by a second transaction, a range read, the range read including information indicating the object key;
receiving a set of conflicting transactions from the range read;
determining that a conflict occurred between the first transaction and a third transaction from the set of conflicting transactions; and
performing a restart of the first transaction in response to determining that the conflict occurred.

2. The system of claim 1 , wherein the operations further comprise:
storing, in a transaction status table, a first set of committed transactions and a second set of aborted transactions, wherein a particular transaction that is currently in progress is not included in the transaction status table.

3. The system of claim 1 , wherein the update operation corresponds to the new version of the object, and the additional information further comprises a first restart count indicating a number of times that the first statement has been attempted.

4. The system of claim 1 , wherein the operations further comprise:
determining that a fourth transaction from the set of conflicting transactions has committed before a read timestamp of the first transaction, and
determining that the first transaction and the fourth transaction do not have a write-write conflict.

5. The system of claim 1 , wherein determining that the conflict occurred between the first transaction and the third transaction comprises:
determining that the third transaction from the set of conflicting transactions has committed after a read timestamp of the first transaction.

6. The system of claim 1 , wherein determining that the conflict occurred between the first transaction and the third transaction comprises:
determining that the third transaction is still active.

7. The system of claim 1 , wherein performing the restart of the first transaction comprises:
re-executing a statement from the first transaction as a subsequent execution of the statement from a previous execution of the statement that did not result in a transaction commit.

8. The system of claim 7 , wherein the operations further comprise:
prior to re-executing the statement, waiting for a particular conflicting transaction, from the set of conflicting transactions, to complete a commit operation.

9. The system of claim 7 , wherein re-executing the statement from the first transaction occurs without waiting for a particular conflicting transaction, from the set of conflicting transactions, to complete a commit operation.

10. The system of claim 1 , wherein the set of conflicting transactions comprises a list of committed or active transactions that performed an update operation in a row of a source table prior to the first transaction.

11. A machine-implemented method comprising:
inserting, using one or more hardware processors, by a first transaction, a new version of an object, the first transaction including a first statement to perform an update operation to a row in a first table, the object corresponding to data in the row to be updated, the first statement including information comprising an object key associated with the object, the object key comprising additional information being appended as a suffix to the object key, the additional information comprising a first transaction identifier associated with the first transaction and a first statement number associated with the first statement;
performing, using the one or more hardware processors, by a second transaction, a range read, the range read including information indicating the object key;
receiving, using the one or more hardware processors, a set of conflicting transactions from the range read;
determining, using the one or more hardware processors, that a conflict occurred between the first transaction and a third transaction from the set of conflicting transactions; and
performing, using the one or more hardware processors, a restart of the first transaction in response to determining that the conflict occurred.

12. The machine-implemented method of claim 11 , further comprising:
storing, in a transaction status table, a first set of committed transactions and a second set of aborted transactions, wherein a particular transaction that is currently in progress is not included in the transaction status table.

13. The machine-implemented method of claim 11 , wherein the update operation corresponds to the new version of the object, and the additional information further comprises a first restart count indicating a number of times that the first statement has been attempted.

14. The machine-implemented method of claim 11 , further comprising:
determining that a fourth transaction from the set of conflicting transactions has committed before a read timestamp of the first transaction, and
determining that the first transaction and the fourth transaction do not have a write-write conflict.

15. The machine-implemented method of claim 11 , wherein determining that the conflict occurred between the first transaction and the third transaction comprises:
determining that the third transaction from the set of conflicting transactions has committed after a read timestamp of the first transaction.

16. The machine-implemented method of claim 11 , wherein determining that the conflict occurred between the first transaction and the third transaction comprises:
determining that the third transaction is still active.

17. The machine-implemented method of claim 11 , wherein performing the restart of the first transaction comprises:
re-executing a statement from the first transaction as a subsequent execution of the statement from a previous execution of the statement that did not result in a transaction commit.

18. The machine-implemented method of claim 17 , further comprising:
prior to re-executing the statement, waiting for a particular conflicting transaction, from the set of conflicting transactions, to complete a commit operation.

19. The machine-implemented method of claim 17 , wherein re-executing the statement from the first transaction occurs without waiting for a particular conflicting transaction, from the set of conflicting transactions, to complete a commit operation.

20. A non-transitory computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine to perform operations comprising:
inserting, by a first transaction, a new version of an object, the first transaction including a first statement to perform an update operation to a row in a first table, the object corresponding to data in the row to be updated, the first statement including information comprising an object key associated with the object, the object key comprising additional information

Assignees

• Snowflake Inc

Inventors

• Luo Chen
• Miller Alexander
• Waddington William
• Zhang Zhaohui

Classifications

• G06F16/2322 Primary

  using timestamps · CPC title

• G06F16/2365

  Ensuring data consistency and integrity · CPC title

• G06F16/2379

  Updates performed during online database operations; commit processing · CPC title