Database processing after a lock condition

• US 10,706,019 B2
• Filed: 09/22/2015
• Issued: 07/07/2020
• Est. Priority Date: 09/22/2015
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Tonder, Andreas, Heidel, Martin, Tyercha, Edward-Robert, Lee, Juchang
• Primary Examiner(s): Burke, Jeff A
• Application Number:
  US14/861,438
• Publication Number:
  US 20170083538A1
• Time in Patent Office: 1,750 Days
• CPC Class Codes: G06F 16/1774 Locking methods, e.g. locking methods for file systems allowing shared and concurrent access to files
• CPC Class Codes: G06F 16/2308 Concurrency control transaction processing G06F9/466
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity

Abstract

Disclosed herein are system, method, and computer program product embodiments for performing database processing after a lock condition. An embodiment operates by receiving a release of a lock on data of a database. It is determined whether the data was modified by a processing of a first transaction associated with the first request. The lock is provided to a second transaction associated with a second request to access the data. If the first transaction did not modify the data, a second transaction associated with the second request is retried without refreshing a snapshot of the data. If the first transaction modified the data, the snapshot of the data is refreshed before the second transaction is retried.

Claims

1. A computer implemented method, comprising:

receiving a release of a lock on data of a database, wherein the lock on the data was provided to a first transaction responsive to a first request to access the data, wherein one or more queued transactions are waiting in a lock queue to access the data;

associating, prior to the lock, a snapshot of the data with a second transaction;

determining whether the data was modified by processing of the first transaction associated with the first request based on whether a flag was set prior to the receiving the release of the lock;

providing the lock to access the data to the second transaction from the lock queue;

determining that the flag was set prior to receiving the release of the lock; and

refreshing the snapshot of the data associated with the second transaction prior to an execution of the second transaction and based on a snapshot isolation level;

wherein at least one of the receiving, determining, and providing are performed by one or more computers.

2. The method of claim 1, further comprising:

receiving the first request to access the data;

providing the lock to the first transaction responsive to receiving the first request; and

receiving the second request to access the data before the lock is released by the first transaction; and

adding the second request to a queue for the lock.

3. The method of claim 1, wherein the determining comprises determining whether the first transaction was committed to the database and the data was modified, or that the first transaction was rolled back and the data was not modified.

4. The method of claim 1, wherein the providing comprises providing to the second transaction the lock and a flag indicating whether or not the first transaction modified the data while locked.

5. The method of claim 1, wherein if the first transaction modified the data and the second transaction is retried before refreshing the snapshot, the retry results in a serialization error.

6. The method of claim 1, wherein if the first transaction modified the data, the second transaction is aborted without refreshing the snapshot and without retrying the second transaction if the snapshot isolation level comprises a transaction level snapshot that is used without refresh throughout the second transaction.

7. The method of claim 6, wherein if the first transaction modified the data, the snapshot of the data is refreshed before the second transaction is retried if the snapshot isolation level comprises a statement level snapshot that is updated after the execution of each of the statements of the second transaction.

8. The method of claim 1, further comprising:

determining a first timestamp corresponding to the data of the database;

determining a second timestamp corresponding to a snapshot associated with the second transaction; and

setting the flag, prior to releasing the lock, if the first timestamp is subsequent to the second timestamp.

9. The method of claim 1, further comprising:

executing the second transaction based on the refreshed snapshot, wherein the executing was not performed on the snapshot prior to the refreshing based on the determination that the flag was set.

10. The method of claim 1, further comprising:

comparing, after the release of the lock, the data values of a snapshot associated with the second transaction to at least a portion of the data that was modified by the processing of the first transaction.

11. A system, comprising:

a memory; and

at least one processor coupled to the memory and configured to;

receive a release of a lock on data of a database, wherein the lock on the data was provided to a first transaction responsive to a first request to access the data, wherein one or more queued transactions are waiting in a lock queue to access the data;

associate, prior to the lock, a snapshot of the data with a second transaction;

determine whether the data was modified by processing of the first transaction associated with the first request based on whether a flag was set prior to the receiving the release of the lock;

provide the lock to the second transaction, from the lock queue, associated with a second request to access the data;

determine that the flag was set prior to receiving the release of the lock; and

refresh the snapshot of the data associated with the second transaction prior to an execution of the second transaction and based on a snapshot isolation level.

12. The system of claim 11, the at least one processor further configured to:

receive the first request to access the data;

provide the lock to the first transaction responsive to receiving the first request; and

receive the second request to access the data before the lock is released by the first transaction; and

add the second request to a queue for the lock.

13. The system of claim 11, wherein the processor is configured to determine by determining whether the first transaction was committed to the database and the data was modified, or that the first transaction was rolled back and the data was not modified.

14. The system of claim 11, wherein the processor is configured to provide by providing the lock and a flag to the second transaction, wherein the flag indicates whether or not the first transaction modified the data.

15. The system of claim 11, wherein if the first transaction modified the data and the second transaction is retried before refreshing the snapshot, the retry results in a serialization error.

16. The system of claim 11, wherein if the first transaction modified the data, the second transaction is aborted without refreshing the snapshot and without retrying the second transaction if the snapshot isolation level comprises a transaction level snapshot used without refresh throughout the second transaction.

17. The system of claim 16, wherein if the first transaction modified the data, the snapshot of the data is refreshed before the second transaction is retried if the snapshot isolation level comprises a statement level snapshot that is updated after the execution of statements of the second transaction.

18. The system of claim 11, wherein the database is partitioned and the data is stored across a plurality of database servers.

19. A non-transitory computer-readable device having instructions stored thereon that, when executed by at least one computing device, causes the at least one computing device to perform operations comprising:

receiving a release of a lock on data of a database, wherein the lock on the data was provided to a first transaction responsive to a first request to access the data, wherein one or more queued transactions are waiting in a lock queue to access the data;

associating, prior to the lock, a snapshot of the data with a second transaction;

determining whether the data was modified by processing of the first transaction associated with the first request based on whether a flag was set prior to the receiving the release of the lock;

providing the lock to the second transaction, from the lock queue, associated with a second request to access the data;

determining that the flag was set prior to receiving the release of the lock; and

refreshing the snapshot of the data associated with the second transaction prior to an execution of the second transaction and based on a snapshot isolation level.

20. The device of claim 19, wherein the data was modified by the first transaction before the release of the lock, and wherein the determination whether the data was modified is performed after the release of the lock.