Avoiding lock contention by using a wait for completion mechanism
Techniques for avoiding lock contention when processing data definition language (DDL) statements are provided. Some DDL statements modify metadata of a table and then require access to the table. After such a DDL statement modifies the metadata of a table and the updated metadata is made available (i.e., published) to other database statements, execution of the DDL statement is suspended. Data manipulation language (DML) statements acquire locks on the table. In one approach, the locks, of any DML statement, that are granted after the DDL statement is issued are timestamped. The DDL statement uses the timestamps to determine when to access the table. The timestamps are used to determine when the last DML statement (that was pending at the time the modified metadata was published) commits and releases its lock on the table.
1. A method for processing data definition language (DDL) statements, comprising:
receiving a first database transaction that includes a DDL statement that specifies a modification to metadata of a database object;
based on the DDL statement:
modifying the metadata;
determining a particular time at which the modified metadata is available for one or more processes, that execute one or more database transactions other than the first database transaction, to read; and
after determining the particular time, waiting to read at least a portion of the database object until each database transaction in a set of one or more database transactions that were pending at the particular time has committed.
2. The method of claim 1 , wherein each database transaction in the set of one or more database transactions specifies a modification to the database object.
3. The method of claim 1 , further comprising:
for each database transaction of the set, associating a timestamp with a lock that said each database transaction acquires on at least a portion of the database object; and
for each lock, determining whether the timestamp associated with said each lock is less than the particular time.
4. The method of claim 1 , wherein:
the modification to the metadata specifies a constraint that is to be associated with certain values of the database object; and
reading at least a portion of the database object includes reading the database object to verify whether the certain values of the database object satisfy the constraint.
5. The method of claim 1 , wherein the modification to the metadata specifies an instruction to create a snapshot of the database object.
6. The method of claim 1 , wherein the database object is one of a table or an index.
7. The method of claim 1 , further comprising:
receiving a second database transaction before receiving the first database transaction, wherein the second database transaction specifies a modification to the database object, wherein the second database transaction has not committed when the first database transaction is received; and
executing the first database transaction without waiting for the second database transaction to commit.
8. The method of claim 1 , further comprising:
receiving a second database transaction after receiving the first database transaction, wherein the second database transaction specifies a modification to the database object, wherein the first database transaction has not committed when the second database transaction is received; and
executing the second database transaction without waiting for the first database transaction to commit.
9. One or more storage media storing instructions for processing data definition language (DDL) statements, wherein the instructions, when executed by one or more processors, cause:
receiving a first database transaction that includes a DDL statement that specifies a modification to metadata of a database object;
based on the DDL statement:
modifying the metadata;
determining a particular time at which the modified metadata is available for one or more processes, that execute one or more database transactions other than the first database transaction, to read; and
after determining the particular time, waiting to read at least a portion of the database object until after each database transaction in a set of one or more database transactions that were pending at the particular time has committed.
10. The one or more storage media of claim 9 , wherein each database transaction in the set of one or more database transactions specifies a modification to the database object.
11. The one or more storage media of claim 9 , wherein the instructions, when executed by the one or more processors, further cause:
for each database transaction of the set, associating a timestamp with a lock that said each database transaction acquires on at least a portion of the database object; and
for each lock, determining whether the timestamp associated with said each lock is less than the particular time.
12. The one or more storage media of claim 9 , wherein:
the modification to the metadata specifies a constraint that is to be associated with certain values of the database object; and
reading at least a portion of the database object includes reading the database object to verify whether the certain values of the database object satisfy the constraint.
13. The one or more storage media of claim 9 , wherein the modification to the metadata specifies an instruction to create a snapshot of the database object.
14. The one or more storage media of claim 9 , wherein the database object is one of a table or an index.
15. The one or more storage media of claim 9 , wherein the instructions, when executed by the one or more processors, further cause:
receiving a second database transaction before receiving the first database transaction, wherein the second database transaction specifies a modification to the database object, wherein the second database transaction has not committed when the first database transaction is received; and
executing the first database transaction without waiting for the second database transaction to commit.
16. The one or more storage media of claim 9 , wherein the instructions, when executed by the one or more processors, further cause:
receiving a second database transaction after receiving the first database transaction, wherein the second database transaction specifies a modification to the database object, wherein the first database transaction has not committed when the second database transaction is received; and
executing the second database transaction without waiting for the first database transaction to commit.