Handling lock contention for leaf pages in SQL indexes

Abstract

Handling lock contention on leaf pages of an SQL index A computer-implemented method, system, and computer program product for handling lock contention on an index (e.g., an SQL index). Leaf pages of an index are monitored for lock contention during an insert operation of an index key by a transaction. Upon detecting a lock contention on a leaf page, the next index key to be entered on such leaf page is routed to a queue of buffers. The index key stored in the queue of buffers is then mapped to the particular leaf page experiencing the lock contention on which the transaction originally attempted to store such index key, and such mapping is stored in a data structure. When such leaf page is no longer experiencing such lock contention, the appropriate index key is then removed from the buffer and stored in the appropriate leaf page based on the mapping identified in the data structure.

Description

Technical Field

[0001] The present disclosure generally relates to relational database management systems, and more specifically to handling lock contention on leaf pages of indexes (e.g., structured query language (SQL) indexes).

Background Art

[0002] A relational database is a digital database based on the relational model of data. The system used to maintain a relational database is a relational database management system (RDBMS). Many relational database systems have the option of using structured query language (SQL) to query and maintain the database.

Summary of the Invention

[0003] In one embodiment of the present disclosure, a computer-implemented method for handling index lock contention comprises monitoring for index leaf page lock contention during an index key insertion operation. The method further comprises routing a first index key to a buffer queue in response to detecting an index leaf page lock contention, where the first index key corresponds to the index key of a transaction that is to be inserted into the index leaf page. The method additionally comprises storing, in a data structure, a mapping of the first index key stored in the buffer queue to the leaf page experiencing the lock contention, where the data structure stores a mapping of index keys stored in the buffer queue to index leaf pages.

[0004] Other embodiments of the computer implementation methods described above include the form of a system and the form of a computer program product.

[0005] In one embodiment, a computer program product for handling lock contention in an index is provided, the computer program product comprising one or more computer-readable storage media on which program code is embodied, the program code having programming instructions for: a procedure for monitoring for lock contention in a leaf page of an index during an index key insertion operation; a procedure for routing a first index key to a buffer queue in response to detecting such lock contention in a leaf page of the index, where the first index key corresponds to the index key of a transaction to be inserted into that leaf page of the index; and a procedure for storing in a data structure a mapping of the first index key stored in the buffer queue to the leaf page experiencing the lock contention, where the data structure stores the mapping of the index key stored in the buffer queue to the leaf page of the index.

[0006] In another embodiment, a system is provided comprising: memory for storing a computer program for handling lock contention in an index; and a processor connected to the memory, the processor comprising: a procedure for monitoring for lock contention in a leaf page of an index during an index key insertion operation; a procedure for routing a first index key to a buffer queue in response to detecting such lock contention in a leaf page of the index, the first index key corresponding to the index key of a transaction to be inserted into that leaf page of the index; and a procedure for storing in a data structure a mapping of the first index key stored in the buffer queue to the leaf page experiencing the lock contention, the data structure being configured to execute program instructions for a computer program having:

[0007] The above provides a fairly general overview of the features and technical advantages of one or more embodiments of this disclosure so that the detailed description of this disclosure that follows can be better understood. Additional features and advantages of this disclosure will be described hereafter and may form the subject matter of the claims of this disclosure. [Brief explanation of the drawing]

[0008] Herein, one or more preferred embodiments of the present invention will be described, by reference only to the following drawings.

[0009] [Figure 1] This document describes a communication system that puts the principles of this disclosure into practice according to an embodiment of the disclosure.

[0010] [Figure 2] This figure shows a software component of a relational database management system for effectively handling lock contention of index leaf pages, according to an embodiment of the present disclosure.

[0011] [Figure 3] This document describes an embodiment of the hardware configuration of a relational database management system that represents a hardware environment for implementing this disclosure.

[0012] [Figure 4] This is a flowchart of a method for handling lock contention of leaf pages in an index according to an embodiment of the present disclosure.

[0013] [Figure 5] This embodiment of the disclosure demonstrates monitoring for lock contention on leaf pages of an index during the insertion of an index key into a leaf page.

[0014] [Figure 6] Embodiments of this disclosure demonstrate the routing of an index key from a transaction that attempts to store an index key on a leaf page experiencing a lock contention to a queue in an index distribution queue buffer.

[0015] [Figure 7] This is a flowchart of a method for handling the storage of additional index keys according to embodiments of the present disclosure.

[0016] [Figure 8] This is a flowchart of a method for asynchronously inserting an index key into a suitable leaf page that has previously experienced a lock contention, according to an embodiment of the present disclosure.

[0017] [Figure 9]A flowchart of a method for synchronously inserting index keys into a leaf page in response to detecting the reception of an index split of a leaf page, a system checkpoint with leaf pages, or a SELECT, UPDATE, or DELETE statement with leaf pages, according to an embodiment of the present disclosure.

[0018] [Figure 10] A flowchart of a method for pre-assigning a leaf page during index splitting when a potential future lock conflict is detected, according to an embodiment of the present disclosure. [Figure 11] Indicates pre-assigning a leaf page during index splitting, according to an embodiment of the present disclosure.

MODE FOR CARRYING OUT THE INVENTION

[0019] As described in the Background section, a relational database is a digital database based on the relational model of data. The system used to maintain a relational database is a relational database management system (RDBMS). Many relational database systems have the option of using the Structured Query Language (SQL) to query and maintain the database.

[0020] SQL queries such as the SQL INSERT INTO statement can be received and processed by a relational database management system (e.g., SQL Server). Such a query (e.g., the SQL INSERT INTO statement) can be used to add a new data row to a table in the database. When such a scenario occurs, the related index defined on this table needs to be updated.

[0021] An index is an on-disk structure associated with a table or view that speeds up the retrieval of rows from the table or view. An index typically contains keys built from one or more columns within a table or view. These keys are stored in a structure (B-tree) that enables a relational database management system to quickly and efficiently find the single or multiple rows associated with the key values. As a result, when a new data row is added to a table in the database, the index needs to be updated to include the key values associated with such a data row. Storing such key values in the index is referred to herein as a "transaction".

[0022] A B-tree index creates a multi-level tree structure that divides the database into fixed-size blocks or pages. child of the tree Each level to link those pages through the location of the address, and a page (known as a node or internal page) can another be able to reference the page of And Leaf Page is at the lowest level. it. A page is usually the starting point of the tree, i.e., the "root". Here, the search for a specific key begins and a path is traced to the leaf as the end point. That is, the leaf page of the index is the lowest level of the index where all the keys for that index are displayed in sorted order. Most pages within such a structure are ultimately leaf pages that reference specific table rows.

[0023] Occasionally, multiple different transactions attempt to simultaneously insert key values into the leaf page of an index (e.g., an SQL index), thereby causing a "lock contention" that negatively impacts performance. "Lock contention" always occurs when one process (transaction) attempts to acquire a "lock" held by another process (transaction).

[0024] In attempts to address lock contention, such transactions may be handled in a randomized manner. However, processing queries, such as SQL queries, associated with such transactions will degrade performance.

[0025] Furthermore, in another attempt to address lock contention, the minimum page size of the index's leaf pages may be increased. However, this will only slightly reduce lock contention. Moreover, increasing the minimum page size of the index's leaf pages will result in more database input / output operations being required during index access.

[0026] As a result, there is currently no effective way to handle lock contention in the leaf pages of an index (e.g., an SQL index).

[0027] Embodiments of this disclosure provide a means for effectively handling lock contention on leaf pages of an index, such as an SQL index, by utilizing a buffer (referred to herein as the “index distribution queue buffer”) for storing such index keys in a buffer queue, as opposed to storing the index keys on leaf pages experiencing lock contention. When such a leaf page no longer experiences such lock contention, the appropriate index key is then removed from the index distribution queue buffer and stored on the leaf page based on a data structure (referred to herein as the “index leaf router map”) that maps the index keys stored in the index distribution queue buffer to the appropriate leaf pages of the index. A more detailed description of these and other features is provided below.

[0028] In some embodiments of this disclosure, the disclosure provides computer implementations, systems, and computer program products for handling lock contention in an index (e.g., an SQL index). In one embodiment of this disclosure, leaf pages of an index (e.g., an SQL index) are monitored for lock contention during a transactional insertion operation of an index key. As used herein, “lock contention” refers to any time when one process (transaction) attempts to acquire a “lock” on a leaf page of an index held by another process (transaction). As used herein, “index” refers to an on-disk structure associated with a table or view that speeds up the retrieval of rows from the table or view. In one embodiment, such an index stores a “key” or “index key” constructed from one or more columns in the table or view. As used herein, “index key” refers to a key in an index (e.g., an SQL index) that enables a relational database management system to quickly and efficiently find one or more rows associated with a key value. Storing such key values ​​in an index is referred to herein as a “transaction.” Furthermore, the “leaf pages” of an index are the lowest level of the index, where all keys for that index are displayed in sorted order. When a lock conflict is detected on a leaf page, the next index key that would be entered into that leaf page is routed to a queue in a buffer (referred to herein as the “index distribution queue buffer”). The index key stored in the queue of the index distribution queue buffer is then mapped to the specific leaf page experiencing the lock conflict from which the transaction initially attempted to store such an index key.In one embodiment, such mappings are stored in a data structure referred to herein as an “index leaf router map,” which stores the mapping of index keys stored in the queue of an index distribution queue buffer to leaf pages of an index. When such leaf pages no longer experience such lock contention, the appropriate index key is then removed from the index distribution queue buffer and stored in the appropriate leaf page based on the mapping in the index leaf router map. In this way, lock contention on leaf pages of an index, such as an SQL index, is effectively handled.

[0029] The following description includes many specific details to provide a thorough understanding of the disclosure. However, it will be apparent to those skilled in the art that the disclosure can be put into practice without such specific details. In other cases, well-known circuits are shown in block diagram form to avoid obscuring the disclosure with unnecessary details. In most places, details considering timing considerations, etc., are omitted unless such details are necessary for a complete understanding of the disclosure and are within the scope of the skills of those skilled in the art.

[0030] Referring in detail to the drawings, Figure 1 shows an embodiment of the present disclosure of a communication system 100 for putting the principles of the present disclosure into practice. The communication system 100 includes a computing device 101 connected to a relational database management system 102 (e.g., a structured query language (SQL server)) via a network 103. Furthermore, as shown in Figure 1, the relational database management system 102 is connected to a database 104.

[0031] Computing device 101 may be any type of computing device (e.g., portable computing unit, Personal Digital Assistant (PDA®), laptop computer, mobile device, tablet personal computer, smartphone, mobile phone, navigation device, game unit, desktop computer system, workstation, internet appliance, etc.) configured to connect to network 103 and, as a result, have the ability to communicate with other computing devices 101 and relational database management system 102. Note that both computing device 101 and the user of computing device 101 may be identified by element number 101.

[0032] Network 103 may be, for example, a local area network, a wide area network, a wireless wide area network, a circuit-switched telephone network, a Global System for Mobile Communications (GSM®) network, a Wireless Application Protocol (WAP) network, a WiFi network, an IEEE 802.11 standard network, or various combinations thereof. Other networks (whose descriptions are omitted here for brevity) may also be used in conjunction with System 100 in Figure 1 without departing from the scope of this disclosure.

[0033] In one embodiment, a user of the computing device 101 issues queries (e.g., SQL queries) to a relational database management system 102 (e.g., an SQL server) to update, delete, and request information from the database 104. For example, the user may issue an INSERT INTO query to add a new data row to a table in the database 104. Such queries are processed by the relational database management system 102, including storing and retrieving data in response to user requests.

[0034] In one embodiment, the relational database management system 102 is configured to maintain a database 104, such as a relational database. In one embodiment, the relational database management system 102 corresponds to an SQL server configured to use a structured query language (SQL) to query and maintain the database 104.

[0035] In one embodiment, the relational database management system 102 is configured to effectively handle lock contention on leaf pages of an index, such as an SQL index, by utilizing a buffer (referred to herein as the “index distribution queue buffer”) for storing such index keys in a buffer queue, as opposed to storing the index keys on leaf pages experiencing lock contention. When such a leaf page no longer experiences such lock contention, the appropriate index key is then removed from the index distribution queue buffer and stored on the appropriate leaf page based on a data structure (referred to herein as the “index leaf router map”) that maps the index keys stored in the index distribution queue buffer to the appropriate leaf pages of the index. A more detailed description of these and other features is provided below. Furthermore, a description of the software components of the relational database management system 102 is provided below in relation to Figure 2, and a description of the hardware configuration of the relational database management system 102 is provided below in relation to Figure 3.

[0036] System 100 is not limited to any one specific network architecture. System 100 may include any number of computing devices 101, a relational database management system 102, a network 103, and a database 104.

[0037] A discussion of the software components used by the relational database management system 102 to effectively handle lock contention in leaf pages of indexes such as SQL indexes is provided below in relation to Figure 2.

[0038] Figure 2 shows a diagram of software components of a relational database management system 102 for effectively handling lock contention of leaf pages of indexes, such as SQL indexes, according to an embodiment of the present disclosure.

[0039] Referring to Figure 2 in conjunction with Figure 1, the relational database management system 102 includes a monitoring engine 201 configured to monitor the lock state of the leaf pages of an index (e.g., an SQL index) during an index key insertion operation. As previously discussed, an index key insertion operation into the leaf pages of an index occurs when a query (e.g., an SQL query) requests to add data to the database 104, such as a new data row into a table in the database 104. As used herein, “index” refers to an on-disk structure associated with a table or view that speeds up the retrieval of rows from the table or view. In one embodiment, such an index stores a “key” or “index key” constructed from one or more columns in the table or view. As used herein, “index key” refers to the key of an index (e.g., an SQL index), which enables the relational database management system 102 to quickly and efficiently find one or more rows associated with the key value. Such an index key corresponds to a value (e.g., 123242), a variable character (e.g., “Smith1”), etc. Storing such key values ​​in an index is referred to as a "transaction" in this specification.

[0040] Furthermore, as previously discussed, in one embodiment, the index key is stored in a B-tree structure, where such a B-tree index includes a multi-level tree structure that divides the database into fixed-size blocks or pages. child tree Each level You can use this to link those pages through the location of the address, and a certain page (known as a node or internal page) another It is possible to refer to the page. And Leaf Page is at the lowest level.A page is typically the starting point of the tree, or the "root." The search for a specific key begins here, following a path that ends at a leaf. In other words, the "leaf pages" of an index are the lowest levels of the index, displaying all keys related to that index in sorted order.

[0041] In one embodiment, the monitoring engine 201 monitors for lock contention for such leaf pages in the index during an operation to insert an index key into a leaf page. As used herein, “lock contention” refers to a situation where transactions attempt to simultaneously insert key values ​​into leaf pages of an index. That is, “lock contention” always occurs when one process (transaction) attempts to acquire a “lock” held by another process (transaction). In one embodiment, a “lock” on a leaf page to prevent other processes (transactions) from using the leaf page can be achieved by a “page-level lock.” As used herein, a “page-level lock” refers to locking the entire leaf page. Alternatively, a process (transaction) may lock a row of a leaf page, for example, via a “row lock + page latch.” As used herein, “row lock” refers to locking a specific row on a leaf page, and as used herein, “page latch” refers to a mechanism managed not by the user but by the relational database management system 102 (e.g., SQL Server), in which the relational database management system 102 imposes a “latch” or “hold” on a leaf page to prevent access. As used herein, “row lock + page latch” refers to a combination of “row lock” and “page latch”.

[0042] In one embodiment, the monitoring engine 201 detects lock contention based on the length of a page-level lock waiting queue. As used herein, “page-level lock waiting queue” refers to a queue that stores various page-level locks (locks placed for various leaf pages) that will be implemented. For example, such lock contention may be based on a threshold percentage of the total length of the queue. In one embodiment, such a threshold percentage may be user-specified.

[0043] In one embodiment, the monitoring engine 201 detects lock contention based on the length of a row-level lock waiting queue. As used herein, “row-level lock waiting queue” refers to a queue that stores various row-level locks (locks placed for various rows within a leaf page) that will be implemented. For example, such lock contention may be based on a threshold percentage of the total length of the queue. In one embodiment, such a threshold percentage may be user-specified.

[0044] In one embodiment, the monitoring engine 201 detects lock contention based on the length of the latch waiting queue. As used herein, “latch waiting queue” refers to a queue that stores various page latches (latches placed for various leaf pages) that will be implemented. For example, such lock contention may be based on a threshold percentage of the total length of the queue. In one embodiment, such a threshold percentage may be user-specified.

[0045] Examples of software tools used by the monitoring engine 201 to perform such monitoring include, but are not limited to, SolarWinds® Database Performance Analyzer, Paessler® PRG Network Monitor, SQL Power Tools, Redgate® SQL Monitor, Nagios®, and Opsview®.

[0046] Furthermore, the monitoring engine 201 is configured to detect the level of lock contention in the leaf pages of an index, for example, determining whether it is below a threshold level that may be user-specified. In one embodiment, the degree or level of lock contention in the leaf pages of an index is determined by the monitoring engine 201 based on the number of transactions attempting to store index keys in the leaf pages, and based on page free size information. As used herein, “page free size information” indicates the amount of memory space available in the leaf pages to store index keys. In one embodiment, each leaf page of an index (e.g., an SQL index) is allocated a specific size (e.g., 3KB) by an expert or the like. In one embodiment, the monitoring engine 201 is configured to track the amount of memory used by the storage of index keys, and thus can determine the amount of memory space remaining to store additional index keys. Examples of software tools used by the monitoring engine 201 to track memory usage of leaf pages of an index (e.g., an SQL index) include, but are not limited to, SQLShack and ApexSQL by Quest®.

[0047] In one embodiment, the degree or level of lock contention in a leaf page can be determined based on the memory usage of the leaf page and the number of index keys to be written to the leaf page. For example, if the number of index keys to be written to the leaf page requires more than 50% of the remaining available memory space in the leaf page, the leaf page can be said to be experiencing lock contention. On the other hand, if the number of index keys to be written to the leaf page requires less than 25% of the remaining available memory space in the leaf page, the leaf page can be said to be experiencing low-level lock contention. As used herein, “low-level” lock contention refers to a leaf page that is not considered to be experiencing lock contention.

[0048] The relational database management system 102 further comprises a buffer engine 202 configured to create a buffer referred to herein as an “index distribution queue buffer”. In one embodiment, the index distribution queue buffer is configured to include one or more queues, each queue storing one or more index keys that are mapped to a particular leaf page, as further discussed below. In one embodiment, the index keys stored in the buffer’s queues are synchronized via compare-and-swap. As used herein, “compare-and-swap” refers to an atomic instruction used in multithreading to achieve synchronization. It compares the contents of a memory location with a given value and modifies the contents of that memory location to the new given value only if they are the same.

[0049] In one embodiment, the buffer engine 202 adds or removes queues in the index distribution queue buffer based on the degree of lock contention on the leaf pages of an index (e.g., an SQL index). For example, in one embodiment, the buffer engine 202 creates a new queue in the index distribution queue buffer if a queue reaches a threshold percentage (e.g., 90%) of its maximum queue length while other queues in the index distribution queue buffer are unable to handle the storage of additional index keys that are being attempted to be stored on the leaf pages of an index experiencing lock contention.

[0050] In one embodiment, the number of index keys stored in a queue can be tracked via a "queue count" maintained by a buffer engine 202. When the queue count reaches zero, indicating that the queue no longer stores any index keys, such a queue can be recycled. That is, the data structure of the queue in the index distribution queue buffer is deleted, and the memory previously used by the deleted data structure of the queue can now be freely used later, for example, for new queues added to the index distribution queue buffer.

[0051] Examples of software tools used by buffer engine 202 to add or remove queues in the index distribution queue buffer include, but are not limited to, ManageEngine® OpManager, SolarWinds® Network Performance Monitor, Redis, and Amazon® SQS.

[0052] Furthermore, the relational database management system 102 includes a routing engine 203 configured to route index keys to a queue in an index distribution queue buffer in response to the monitoring engine 201 detecting a locked state in a leaf page.

[0053] In one embodiment, the routing engine 203 is configured to route index keys to queues of an index distribution queue buffer in terms of certain rules, such as modular hashing. In one embodiment, the routing engine 203 is configured to store index keys only in queues of an index distribution queue buffer that have at least a threshold percentage (e.g., 5%) of its available capacity for storing index keys. In one embodiment, such a threshold percentage may be user-specified.

[0054] In one embodiment, the routing engine 203 is configured to, in response to detection that the level of lock contention on a leaf page is below a threshold level, remove index keys from the index distribution queue buffer and asynchronously insert them into the appropriate leaf pages, based on a data structure (mapping of index keys stored in the index distribution queue buffer to the appropriate leaf pages of the index). Such detection is performed by the monitoring engine 201, as discussed above.

[0055] In one embodiment, the routing engine 203 is configured to remove index keys from the index distribution queue buffer and synchronously insert them into the appropriate leaf pages, based on a data structure (mapping of index keys stored in the index distribution queue buffer to the appropriate leaf pages of the index), in response to detection of leaf page splitting, triggering of system checkpoints for leaf pages, and receiving SELECT, UPDATE, or DELETE statements involving leaf pages. Such detection is discussed further below in relation to the detector engine 205.

[0056] As used herein, “splitting” a leaf page refers to a situation where there is insufficient memory space to add new data (e.g., new rows) that needs to be on a particular leaf page, and as a result, the leaf page must be split. When a split occurs, the leaf page may be divided into two pages, each having approximately half the number of rows of the original leaf page.

[0057] As used herein, a “system checkpoint” for a leaf page refers to a test operation that verifies data such as index keys obtained from a leaf page by comparing that data with a baseline copy.

[0058] SELECT statements, such as SQL SELECT statements, are used to select data from database 104. UPDATE statements, such as SQL UPDATE statements, are used to modify existing records in tables in database 104. DELETE statements, such as SQL DELETE statements, are used to delete existing records in tables in database 104.

[0059] Examples of software tools used by routing engine 203 to route index keys from the index distribution queue buffer to the appropriate leaf pages of an index (e.g., an SQL index) include, but are not limited to, ApexSQL by Quest®, PostgreSQL®, and Snowflake®.

[0060] In addition, the relational database management system 102 includes a mapping engine 204 configured to construct data structures (e.g., tables) referred to herein as “index leaf router maps”. In one embodiment, such data structures are stored in the storage devices (e.g., memory, disk units) of the relational database management system 102.

[0061] As discussed above, the "index leaf router map" maps index keys stored in the index distribution queue buffer to the appropriate leaf pages of the index. In one embodiment, the mapping engine 204 maps index keys stored in various memory locations in the queue of the index distribution queue buffer to various leaf pages based on those memory locations. For example, a key index may be stored in memory location Q11 of queue #1. Such a memory location may then be stored in the index leaf router map associated with a particular leaf page (e.g., leaf page #5). In one embodiment, a particular leaf page is based on a query received by the relational database management system 102, in which the query results in the addition of a new data row to a table in database 104, thereby providing an index key to be stored in such a leaf page. Instead of storing such an index key in this leaf page, it is temporarily stored in the index distribution queue buffer until the leaf page has enough capacity to store the index key. To track which leaf page should receive which index key, such information is maintained by the mapping engine 204 in the index leaf router map.

[0062] In one embodiment, the mapping between the memory locations of index keys in the queue of the index distribution queue buffer and the identifiers of the various leaf pages of the index (e.g., an SQL index) is synchronized via compare and swap.

[0063] Examples of software tools used by the mapping engine 204 to map index keys stored in the index distribution queue buffer to the appropriate leaf pages of the index include, but are not limited to, IBM® Db2 and ApexSQL by Quest®.

[0064] Furthermore, the relational database management system 102 includes a detector engine 205 configured to detect leaf page splitting, triggering system checkpoints for leaf pages, and receiving SELECT, UPDATE, or DELETE statements involving leaf pages.

[0065] As discussed above, as used herein, "splitting" of a leaf page refers to a situation where there is insufficient memory space to add new data (e.g., new rows) that needs to be on a certain leaf page, and as a result, the leaf page must be split. When a split occurs, the leaf page may be split into two pages, each having approximately half the rows of the original leaf page. As a result, the detector engine 205 detects a leaf page split when creating a new leaf page to store half of the data stored on the original leaf page.

[0066] Furthermore, as discussed above, as used herein, a “system checkpoint” for a leaf page refers to a test operation that verifies data such as index keys obtained from a leaf page by comparing the data with a baseline copy. In one embodiment, the detector engine 205 detects such system checkpoints based on the detection of checkpoint issuance by the database engine 206 of the relational database management system 102 (e.g., the SQL Server database engine).

[0067] In one embodiment, the database engine 206 is configured to periodically issue checkpoints (e.g., automatic, indirect, manual, and internal type checkpoints) to validate the data in the leaf pages. In one embodiment, the issuance of such checkpoints is detected by the detector engine 205 based on checkpoint commands issued by the database engine 206.

[0068] In addition, as discussed above, SELECT statements, such as SQL SELECT statements, are used to select data from database 104. UPDATE statements, such as SQL UPDATE statements, are used to modify existing records in tables in database 104. DELETE statements, such as SQL DELETE statements, are used to delete existing records in tables in database 104. In one embodiment, the detector engine 205 is configured to detect the reception of such statements by identifying such statements in queries received from computing device 101 by relational database management system 102. In one embodiment, the detector engine 205 uses natural language processing to identify such statements in queries, and such terms are stored in a data structure stored by an expert. In one embodiment, such a data structure is stored in a storage device (e.g., memory, disk unit) of the relational database management system 102.

[0069] In one embodiment, the detector engine 205 is configured to detect sequential insertion patterns of index keys within leaf pages during index splitting. As discussed above, when index splitting occurs, a leaf page may be split into two pages, each having approximately half the number of rows in the original leaf page. In one embodiment, if a sequential pattern of index keys (a continuous movement pattern) is entered into such a leaf page, the detector engine 205 asynchronously pre-allocates multiple leaf pages of the index to reduce possible lock contention scenarios. Since a sequential pattern of index keys can continue within such leaf pages, it can be said that lock contention is more likely to occur in such situations. In one embodiment, such a continuous movement pattern is detected by the detector engine 205 by predicting key values ​​as non-leaf key values ​​for those newly pre-allocated leaf pages using high / low key values ​​from previous leaf pages.

[0070] Further explanations of these and other features are provided below in connection with the discussion of methods for handling lock contention in leaf pages of indexes (SQL indexes).

[0071] Prior to discussing methods for handling lock contention in the leaf pages of an index (SQL index), a description of the hardware configuration of the relational database management system 102 (Figure 1) is provided below in relation to Figure 3.

[0072] Referring now to Figure 3, Figure 3 shows an embodiment of the hardware configuration of the relational database management system 102 (Figure 1), which represents a hardware environment for implementing the present disclosure.

[0073] The relational database management system 102 has a processor 301 connected to various other components by a system bus 302. An operating system 303 runs on the processor 301 and provides control over the various components shown in Figure 3, and makes their functions cooperate. An application 304 according to the principles of this disclosure runs in conjunction with the operating system 303 and provides calls to the operating system 303, where the calls implement various functions or services to be performed by the application 304. The application 304 may include, for example, a monitoring engine 201 (Figure 2), a buffer engine 202 (Figure 2), a routing engine 203 (Figure 2), a mapping engine 204 (Figure 2), a detector engine 205 (Figure 2), and a database engine 206 (Figure 2). Furthermore, the application 304 may include a program for handling lock contention of leaf pages of an index (e.g., an SQL index), for example, as further discussed below in relation to Figures 4 to 11.

[0074] Referring again to Figure 3, the Read-Only Memory ("ROM") 305 is connected to the system bus 302 and includes a Basic Input / Output System ("BIOS") that controls certain basic functions of the relational database management system 102. The Random Access Memory ("RAM") 306 and disk adapter 307 are also connected to the system bus 302. Note that software components, including the operating system 303 and application 304, may be loaded into the RAM 306, which may be the main memory of the relational database management system 102 for execution. The disk adapter 307 may be an Integrated Drive Electronics ("IDE") adapter that communicates with a disk unit 308, for example, a disk drive. Note that, as will be further discussed below in relation to Figures 4 to 11, a program for handling lock contention of leaf pages of an index (e.g., an SQL index) may reside in the disk unit 308 or in application 304.

[0075] The relational database management system 102 may further include a communication adapter 309 connected to the bus 302. The communication adapter 309 interconnects the bus 302 with an external network (e.g., network 103 in Figure 1) to communicate with other devices such as the computing device 101 (Figure 1).

[0076] In one embodiment, the application 304 of the relational database management system 102 includes software components of a monitoring engine 201, a buffer engine 202, a routing engine 203, a mapping engine 204, a detector engine 205, and a database engine 206. In one embodiment, such components may be implemented in hardware, in which case such hardware components would be connected to the bus 302. The functions performed by such components, as discussed above, are not general-purpose computer functions. As a result, the relational database management system 102 is a specific machine that is the result of implementing specific, non-general-purpose computer functions.

[0077] In one embodiment, the functionality of such software components of the relational database management system 102 (e.g., the monitoring engine 201, the buffer engine 202, the routing engine 203, the mapping engine 204, the detector engine 205, and the database engine 206), including the functionality for handling lock contention of index leaf pages, may be embodied in an application-specific integrated circuit.

[0078] The present invention may be a system, method, and / or computer program product in any possible level of technical detail integration. The computer program product may include one or more computer-readable storage media having computer-readable program instructions for causing a processor to execute an aspect of the present invention.

[0079] A computer-readable storage medium can be a tangible device capable of holding and storing instructions for use by an instruction execution device. A computer-readable storage medium may be, but is not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any preferred combination of those described above. A non-exclusive list of more specific examples of computer-readable storage media includes: portable computer diskettes, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable compact disk read-only memory (CD-ROM), digital multipurpose disks (DVDs), memory sticks, floppy disks, mechanically encoded devices such as punch cards or grooved raised structures on which instructions are recorded, and any preferred combination of those described above. As used herein, computer-readable storage media should not be construed as transient signals themselves, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through waveguides or other transmission media (e.g., light pulses passing through optical fiber cables), or electrical signals transmitted through wires.

[0080] The computer-readable program instructions described herein may be downloaded from a computer-readable storage medium to each computing / processing device, or to an external computer or external storage device via a network such as the Internet, a local area network, a wide area network, and / or a wireless network. The network may include copper transmission cables, optical transmission fibers, wireless transmissions, routers, firewalls, switches, gateway computers, and / or edge servers. A network adapter card or network interface within each computing / processing device receives computer-readable program instructions from the network and transfers them for storage on a computer-readable storage medium within the respective computing / processing device.

[0081] The computer-readable program instructions that perform the operation of the present invention may be assembler instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages, including object-oriented programming languages ​​such as Smalltalk® or C++, and procedural programming languages ​​such as the C programming language or similar programming languages. The computer-readable program instructions may be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or wide area network (WAN), or this connection may be to an external computer (for example, through the Internet using an Internet service provider). In some embodiments, for example, an electronic circuit including a programmable logic circuit, a field-programmable gate array (FPGA), or a programmable logic array (PLA) may be personalized by executing computer-readable program instructions by utilizing state information of computer-readable program instructions in order to perform an aspect of the present invention.

[0082] Aspects of the present invention are described herein with reference to flowcharts and / or block diagrams of methods, apparatus (systems) and computer program products according to embodiments of the present invention. It will be understood that each block in the flowcharts and / or block diagrams, and combinations of blocks in the flowcharts and / or block diagrams, can be implemented by computer-readable program instructions.

[0083] These computer-readable program instructions may be provided to a computer processor or other programmable data processing device to generate a machine that creates means for instructions executed via the processor of the computer or other programmable data processing device to implement functions / operations specified in one or more blocks of a flowchart and / or block diagram. These computer-readable program instructions may also be stored on a computer-readable storage medium on which the instructions are stored, which can instruct a computer, a programmable data processing device, and / or other device to function in a particular manner, such that the storage medium containing the instructions has a product containing instructions that implements the modes of functions / operations specified in one or more blocks of a flowchart and / or block diagram.

[0084] Furthermore, computer-readable program instructions may be loaded into a computer, other programmable data processing device, or other device to execute a series of operational steps on the computer, other programmable device, or other device, thereby generating a computer implementation process in which the instructions executed on the computer, other programmable device, or other device implement the functions / operations specified in one or more blocks of a flowchart and / or block diagram.

[0085] The flowcharts and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of instructions comprising one or more executable instructions for implementing a specified logical function. In some alternative implementations, the functions described in a block may occur in an order different from the order shown in the drawings. For example, two blocks shown consecutively may actually be implemented as a single stage, executed simultaneously, substantially simultaneously, partially or entirely, with overlapping timelines, or blocks may, in some cases, be executed in reverse order depending on the functions involved. It should also be noted that each block in a block diagram and / or flowchart, and combinations of blocks in a block diagram and / or flowchart, may be implemented by a special-purpose hardware-based system that performs a specified function or operation, or executes a combination of special-purpose hardware and computer instructions.

[0086] As described above, SQL queries, such as SQL INSERT INTO statements, can be received and processed by a relational database management system (e.g., SQL Server). Such queries (e.g., SQL INSERT INTO statements) can be used to add new data rows to a table in a database. When such a scenario occurs, the associated indexes defined on this table need to be updated. An index is an on-disk structure associated with a table or view that speeds up the retrieval of rows from that table or view. An index typically contains keys constructed from one or more columns in the table or view. These keys are stored in a structure (B-tree) that allows the relational database management system to quickly and efficiently find one or more rows associated with the key values. Consequently, when new data rows are added to a table in the database, the index needs to be updated to include the key values ​​associated with such data rows. Storing such key values ​​in an index is referred to herein as a "transaction". A B-tree index creates a multi-level tree structure that divides the database into fixed-size blocks or pages. child tree Each level You can use this to link those pages through the location of the address, and a certain page (known as a node or internal page) another It is possible to refer to the page. And Leaf Page is at the lowest level.A certain page is typically the starting point of the tree, or the "root." Here, the search for a specific key begins, following a path that ends at the leaf. That is, the leaf pages of an index are the lowest level of the index, displaying all keys for that index in sorted order. Most pages in such a structure are leaf pages that ultimately refer to specific table rows. Occasionally, multiple different transactions attempt to simultaneously insert key values ​​into the leaf pages of an index (e.g., an SQL index), resulting in a "lock contention" that negatively impacts performance. A "lock contention" inevitably occurs when one process (transaction) attempts to acquire a "lock" held by another process (transaction). In an attempt to address lock contention, such transactions may be handled in a randomized manner. However, processing queries, such as SQL queries, associated with such transactions will still suffer performance degradation. Furthermore, another attempt to address lock contention is to increase the minimum page size of the index's leaf pages. However, this only slightly reduces lock contention. Furthermore, increasing the minimum page size of index leaf pages results in more database input / output operations being required during index access. Consequently, there is currently no effective means to handle lock contention on leaf pages of indexes (e.g., SQL indexes).

[0087] Embodiments of this disclosure provide a means for effectively handling lock contention on leaf pages of an index, such as an SQL index, by utilizing a buffer (referred to herein as the “index distribution queue buffer”) for storing such index keys in a queue of buffers, as opposed to storing the index keys on leaf pages experiencing lock contention. When such leaf pages no longer experience lock contention, the appropriate index keys are then removed from the index distribution queue buffer and stored on the appropriate leaf pages based on a data structure (referred to herein as the “index leaf router map”) that maps the index keys stored in the index distribution queue buffer to the appropriate leaf pages of the index. A description of these and other features is discussed below in relation to Figures 4 to 11. Figure 4 is a flowchart of a method for handling lock contention on leaf pages of an index. Figure 5 shows monitoring for lock contention on leaf pages of an index during an index key insertion operation on a leaf page. Figure 6 shows the routing of index keys from a transaction involving an attempt to store the index keys on a leaf page experiencing lock contention to the queue of the index distribution queue buffer. Figure 7 is a flowchart of a method for handling the storage of additional index keys. Figure 8 is a flowchart of a method for asynchronously inserting an index key into an appropriate leaf page that has previously experienced a lock contention. Figure 9 is a flowchart of a method for synchronously inserting an index key into a leaf page in response to detecting an index split of the leaf page, a system checkpoint involving the leaf page, or the receipt of a SELECT, UPDATE, or DELETE statement involving the leaf page. Figure 10 is a flowchart of a method for pre-allocating a leaf page during index splitting if a potential future lock contention is detected. Figure 11 shows pre-allocating a leaf page during index splitting.

[0088] As described above, Figure 4 is a flowchart of Method 400 for handling lock contention on leaf pages of an index (e.g., an SQL index) according to an embodiment of the present disclosure.

[0089] Referring to Figure 4 in conjunction with Figures 1 to 3, in operation 401, the monitoring engine 201 of the relational database management system 102 monitors for lock contention on leaf pages of an index (e.g., an SQL index) during a transactional index key insertion operation.

[0090] In operation 402, the monitoring engine 201 of the relational database management system 102 determines whether a lock conflict has been detected.

[0091] As previously discussed, the insertion of an index key into the leaf pages of an index occurs when a query (e.g., an SQL query) requests the addition of data within database 104, such as a new data row into a table within database 104. As used herein, “index” refers to an on-disk structure associated with a table or view that speeds up the retrieval of rows from the table or view. In one embodiment, such an index stores a “key” or “index key” constructed from one or more columns in the table or view. As used herein, “index key” refers to the key of an index (e.g., an SQL index) that enables the relational database management system 102 to quickly and efficiently find one or more rows associated with the key value. Such an index key corresponds to a value (e.g., 123242), a variable character (e.g., “Smith1”), etc. Storing such key values ​​in an index is referred to herein as a “transaction.”

[0092] Furthermore, as previously discussed, in one embodiment, the index key is stored in a B-tree structure, where such a B-tree index includes a multi-level tree structure that divides the database into fixed-size blocks or pages. child tree Each level You can use this to link those pages through the location of the address, and a certain page (known as a node or internal page) another It is possible to refer to the page. And Leaf Page is at the lowest level. A page is typically the starting point of the tree, or the "root." The search for a specific key begins here, following a path that ends at a leaf. In other words, the "leaf pages" of an index are the lowest levels of the index, displaying all keys related to that index in sorted order.

[0093] In one embodiment, the monitoring engine 201 monitors for lock contention for such leaf pages in the index during an operation to insert an index key into a leaf page. As used herein, “lock contention” refers to a situation where transactions attempt to simultaneously insert key values ​​into leaf pages of an index. That is, “lock contention” always occurs when one process (transaction) attempts to acquire a “lock” held by another process (transaction). In one embodiment, a “lock” on a leaf page to prevent other processes (transactions) from using the leaf page can be achieved by a “page-level lock.” As used herein, a “page-level lock” refers to locking the entire leaf page. Alternatively, a process (transaction) may lock a row of a leaf page, for example, via a “row lock + page latch.” As used herein, “row lock” refers to locking a specific row on a leaf page, and as used herein, “page latch” refers to a mechanism managed not by the user but by the relational database management system 102 (e.g., SQL Server), in which the relational database management system 102 imposes a “latch” or “hold” on a leaf page to prevent access. As used herein, “row lock + page latch” refers to a combination of “row lock” and “page latch”.

[0094] Examples of software tools used by the monitoring engine 201 to perform such monitoring include, but are not limited to, SolarWinds® Database Performance Analyzer, Paessler® PRG Network Monitor, SQL Power Tools, Redgate® SQL Monitor, Nagios®, and Opsview®.

[0095] An example of monitoring by the monitoring engine 201 regarding lock contention for index leaf pages during the insertion of index keys into leaf pages is discussed below in relation to Figure 5.

[0096] Referring to Figure 5, Figure 5 illustrates how an embodiment of the present disclosure monitors for lock contention on leaf pages of an index during the insertion of an index key into a leaf page.

[0097] As shown in Figure 5, multiple transactions 501A to 501C (identified in Figure 5 as "Transaction #X", "Transaction #Y", and "Transaction #Z", respectively) attempt to store index keys in leaf pages 502 of an index that already stores a certain number of index keys 503 (identified in Figure 5 as "Key #1", "Key #2", "Key #3", ... "Key #N", where N is a positive integer). Transactions 501A to 501C may be referred to collectively or individually as multiple transactions 501 or a single transaction 501. Index keys 503 may be referred to collectively or individually as multiple index keys 503 or a single index key 503.

[0098] When each transaction 501 attempts to store an index key 503 on a leaf page such as leaf page 502, transaction 501 applies either a page lock or a row lock + page latch. The page or row lock to be implemented is stored in a "page / row level lock wait queue" 504 containing a list of the page / row locks to be implemented (identified as "lock #n", "lock #m", "lock #x", "lock #y", and "lock #z" in Figure 5). Note that, as used herein, the symbol " / " means "or". Therefore, the "page / row" level lock wait queue refers to either a page level lock wait queue or a row level lock wait queue.

[0099] In one embodiment, the monitoring engine 201 detects lock contention based on the length of the page / row level lock waiting queue 504. In one embodiment, such lock contention may be based on a threshold percentage of the total length of the queue 504. In one embodiment, such a threshold percentage may be user-specified.

[0100] Furthermore, as shown in Figure 5, in one embodiment, the monitoring engine 201 detects lock contention based on the length of a latch waiting queue 505 that stores various page latches (latches placed for various leaf pages) to be implemented (for example, identified in Figure 5 as "latch#x", "latch#y", "latch#z", ... "latch#y"). For example, such lock contention may be based on a threshold percentage of the total length of the queue 505. In one embodiment, such a threshold percentage may be user-specified.

[0101] Furthermore, as shown in Figure 5, based on such monitoring by the monitoring engine 201, the index key 503 will be routed to the index distribution queue buffer 506, as will be discussed in more detail below.

[0102] In addition, as shown in Figure 5, the index distribution queue buffer 506 includes queues 507A to 507N (identified in Figure 5 as "Queue #1", "Queue #2", ... "Queue #N", where N is a positive integer). Queues 507A to 507N may be referred to collectively or individually as a plurality of queues 507 or a single queue 507. Each queue 507 stores one or more index keys 503. Further discussion regarding the storage of index keys 503 in the queues 507 of the index distribution queue buffer 506 is provided below.

[0103] Figure 5 shows a specific number of entries in queues 504 and 505, but note that Figure 5 can contain any number of entries in queues 504 and 505. Similarly, buffer 506 can contain any number of queues 507, and each queue 507 can contain any number of entries for storing any number of index keys 503. Furthermore, leaf page 502 can store any number of index keys 503. Note that element number 503 refers to those index keys stored in leaf pages such as leaf page 502, as well as those index keys stored in queues 507 of buffer 506.

[0104] Returning to operation 402 in Figure 4, in conjunction with Figures 1-3 and 5, if no lock contention is detected, the monitoring engine 201 of the relational database management system 102 continues to monitor for lock contention on the leaf pages of the index (e.g., an SQL index) during the index key insertion operation by transaction 501 in operation 401.

[0105] However, if a lock contention is detected, in operation 403, the routing engine 203 of the relational database management system 102 routes the next index key 503 (intended to be stored in the leaf page identified as exhibiting a lock contention) to queue 507 of the index distribution queue buffer 506, as shown in Figure 6, thereby moving the lock contention to a different leaf page.

[0106] Referring to Figure 6, Figure 6 illustrates the routing of index key 503 from a transaction that attempts to store the index key 503 on a leaf page experiencing a lock contention, to queue 507 (Figure 5) of the index distribution queue buffer 506 (Figure 5), according to an embodiment of the present disclosure, where the stored index key 503 is mapped to the leaf page experiencing the lock contention.

[0107] As shown in Figure 6, transactions 601A to 601D (labeled "Transaction #1", "Transaction #2", "Transaction #3", and "Transaction #4" respectively in Figure 6) attempt to store index key 503 (labeled "K51", "K52", and "K61" for transaction 601A, "K53", "K54", and "K62" for transaction 601B, "K55", "K63", and "K65" for transaction 601C, and "K56", "K64", and "K66" for transaction 601D) in leaf pages 602A to 602B (identified as "Leaf #5" and "Leaf #6" respectively in Figure 6), which are experiencing lock contention. As a result, as shown in Figure 6, the routing engine 203 routes such index keys 503 to various queues 507 of the index distribution queue buffer 506. Furthermore, as shown in Figure 6, by routing such index keys 503 to various queues 507 of the index distribution queue buffer 506, the area of ​​lock contention may be moved to another leaf page, such as leaf page 602C (identified as "leaf #7" in Figure 6). For completeness, it should be noted that in an index (e.g., an SQL index), each of the leaf pages shown in Figure 6 (602A-602D, where leaf page 602D is identified as "leaf #4" in Figure 6) is a child of a non-leaf page 603 (identified as "non-leaf #2" in Figure 6) which is one or more levels below the root node (not shown in Figure 6) (located at an intermediate level in the index's (B-tree) structure). Transactions 601A to 601D may be referred to collectively or individually as multiple transactions 601 or a single transaction 601. Leaf pages 602A to 602D may be referred to collectively or individually as multiple leaf pages 602 or a single leaf page 602.Figure 6 shows four transactions 601, but note that any number of transactions 601 may be attempting to store the index key 503 in any number of leaf pages 602. Furthermore, an index (e.g., an SQL index) can contain any number of leaf pages 602 and non-leaf pages 603.

[0108] In one embodiment, the routing engine 203 uses a specific rule (for example, hashing such as modular hashing, as shown in Figure 6, where hash value = key value) to perform hashing. mod From the perspective of 16), such index key 503 is routed to queue 507 of the index distribution queue buffer 506. For example, based on applying such a specific rule, as shown in Figure 6, index key K51 is stored at queue position Q11 of queue 507A, index key K61 is stored at queue position Q12 of queue 507A, index key K54 is stored at queue position Q13 of queue 507A, and index key K63 is stored at queue position Q14 of queue 507A. Similarly, as shown in Figure 6, index key K62 is stored at queue position Q21 of queue 507B, index key K52 is stored at queue position Q22 of queue 507B, index key K66 is stored at queue position Q23 of queue 507B, and index key K53 is stored at queue position Q24 of queue 507B. Furthermore, as shown in Figure 6, index keys K55, K64, K65, and K56 are stored in queue 507N.

[0109] In one embodiment, the index keys 503 stored in the queue 507 of the index distribution queue buffer 506 are synchronized via compare-and-swap. As used herein, “compare-and-swap” refers to an atomic instruction used in multiple threads to achieve synchronization. It compares the contents of a memory location with a given value and corrects the contents of that memory location to the new given value only if they are the same. For example, in one embodiment, a queue count, the queue space used, and a queue tail pointer may be used to provide information relating to the structure of the queue 507 in the index distribution queue buffer 506 used in the “compare-and-swap” operation.

[0110] Returning to Figure 4 in conjunction with Figures 1-3 and 5-6, in operation 404, the mapping engine 204 of the relational database management system 102 maps an index key 503 (e.g., "K51") stored in queue 507 (e.g., queue 507A) of the index distribution queue buffer 506 to a specific leaf page 602 (e.g., leaf page 602A) that is experiencing a lock contention, which transaction 601 (e.g., transaction 601A) initially attempted to store the index key 503 (e.g., "K51"). In one embodiment, such a mapping is stored by the mapping engine 204 in a data structure referred to herein as the "index leaf router map" 604, as shown in Figure 6.

[0111] Referring again to Figure 6, the index leaf router map 604 stores the mapping of index keys 503 stored in queue 507 of the index distribution queue buffer 506 to specific leaf pages 602 of the index (e.g., an SQL index). For example, the location identifiers of the index key 503 (e.g., queue locations Q11, Q13, Q22, Q24, Q16_1, Q16_4) are mapped to leaf page 602A (identified as "leaf #5" in Figure 6), as shown in Figure 6. Similarly, the location identifiers of the index key 503 (e.g., queue locations Q12, Q14, Q21, Q23, Q16_2, Q16_3) are mapped to leaf page 602B (identified as "leaf #6" in Figure 6), as shown in Figure 6.

[0112] In one embodiment, the index leaf router map 604 also stores page free size information 605A to 605B for such leaf pages 602 (e.g., leaf pages 602A to 602B, respectively), as shown in Figure 6. In this specification, “page free size information” 605A to 605B may be referred to collectively or individually as page free size information 605. As used herein, such page free size information 605 refers to the amount of memory space available in the leaf pages 602 for storing index keys 503. As previously discussed, in one embodiment, the monitoring engine 201 is configured to track the amount of memory used by the storage of index keys 503, and thus can determine the amount of memory space remaining for storing additional index keys 503.

[0113] Furthermore, as discussed above, in one embodiment, the mapping between the memory location of the index key 503 in the queue 507 of the index distribution queue buffer 506 and the identifiers of the various leaf pages 602 of the index (e.g., an SQL index) is synchronized via compare-and-swap by the mapping engine 204.

[0114] In contrast to storing the index key 503 in leaf page 602 which is experiencing lock contention, after routing such index key 503 to queue 507 of index distribution queue buffer 506, queue 507 of index distribution queue buffer 506 can be dynamically added / removed based on the degree of lock contention experienced by the leaf page 602 of the index.

[0115] For example, in one embodiment, the buffer engine 202 dynamically adds or removes queues 507 in the index distribution queue buffer 506 based on the degree of lock contention on the leaf pages 602 of the index (e.g., an SQL index). As discussed above, in one embodiment, the degree or level of lock contention on the leaf pages of the index is determined by the monitoring engine 201 based on the number of transactions attempting to store index keys 503 on the leaf pages 602, and based on page free size information 605. If it is necessary to increase the number of queues 507 in the index distribution queue buffer 506 to store additional new index keys 503, the buffer engine 202 dynamically adds queues 507 to the index distribution queue buffer 506. Conversely, if the number of queues 507 in the index distribution queue buffer 506 is excessive for handling the current degree of lock contention on the leaf pages 602 of the index, the buffer engine 202 dynamically removes or removes queues 507 from the index distribution queue buffer 506.

[0116] For example, when queue 507 of the index distribution queue buffer 506 reaches a threshold percentage of the maximum queue length, the buffer engine 202 of the relational database management system 102 may create a new queue 507 to handle the storage of any additional index keys 503, as discussed below in relation to Figure 7, if there are no other queues 507 to handle the storage of any additional index keys 503.

[0117] Figure 7 is a flowchart of a method 700 for handling the storage of additional index keys 503 by creating a new queue 507 in the index distribution queue buffer 506 when no other queue 507 exists for handling the storage of these additional index keys 503 according to an embodiment of the present disclosure.

[0118] Referring to Figure 7 in conjunction with Figures 1 to 3 and Figures 5 to 6, in operation 701, the buffer engine 202 of the relational database management system 102 determines whether a queue 507 (for example, queue 507A) in the index distribution queue buffer 506 has reached a threshold percentage of the maximum queue length, for example, when other queues 507 in the index distribution queue buffer 506 lack the capacity to store additional index keys 503.

[0119] In one embodiment, such a threshold percentage may be user-specified. In one embodiment, information regarding the structure of the index distribution queue buffer 506, including its queue 507, may be stored in a data structure that may reside in a storage device of the relational database management system 102 (e.g., memory 305, disk unit 308). In one embodiment, such information includes the maximum length of the queue 507, including a defined threshold percentage of its maximum queue length, which is the maximum length for which additional queues 507 should be created by the buffer engine 202.

[0120] If no queue 507 that has reached the threshold percentage of the maximum queue length exists in the index distribution queue buffer 506, the buffer engine 202 continues to determine in operation 701 whether queue 507 in the index distribution queue buffer 506 has reached the threshold percentage of the maximum queue length.

[0121] However, if there is a queue 507 (e.g., queue 507A) in the index distribution queue buffer 506 that has reached the maximum queue length threshold percentage, such as when other queues 507 in the index distribution queue buffer 506 lack the capacity to store additional index keys 503, then in operation 702, the buffer engine 202 of the relational database management system 102 creates a new queue 507 in the index distribution queue buffer 506.

[0122] In operation 703, the routing engine 203 of the relational database management system 102 routes the newly incoming index key 503 to the newly created queue 507, as discussed above.

[0123] Furthermore, as discussed below in relation to Figure 8, in a situation where a low level or degree of lock contention exists in a leaf page 602 that has previously experienced lock contention, the index key 503 that transaction 601 previously attempted to store in such a leaf page 602 is then removed from the index distribution queue buffer 506 and stored in the appropriate leaf page 602 using the index leaf router map 604.

[0124] Figure 8 is a flowchart of a method 800 for asynchronously inserting an index key 503 into a suitable leaf page 602 that has previously experienced a lock contention, in which transaction 601 had previously attempted to store such an index key 503 in such a leaf page 602.

[0125] Referring to Figure 8 in conjunction with Figures 1-3 and 5-6, in operation 801, the monitoring engine 201 of the relational database management system 102 measures the degree of lock contention on the leaf page 602 (e.g., leaf page 602A) of the index that was previously identified as experiencing lock contention.

[0126] In operation 802, the monitoring engine 201 of the relational database management system 102 determines whether the level of lock contention in such leaf page 602, which had previously experienced lock contention, is below a threshold level that may be user-specified. If such a situation occurs, it can be said that leaf page 602 is now safe to store additional index keys 503 because it has experienced a low level of lock contention. As used herein, “low level” lock contention refers to a leaf page that is no longer considered to have experienced lock contention.

[0127] As discussed above, in one embodiment, the monitoring engine 201 is configured to detect the level of lock contention in the leaf pages 602 of the index, for example, by determining whether it is below a threshold level that may be user-specified. In one embodiment, the degree or level of lock contention in the leaf pages of the index is determined by the monitoring engine 201 based on the number of transactions attempting to store index keys 503 in the leaf pages 602, and based on page free size information 605. As used herein, “page free size information” indicates the amount of memory space available in the leaf pages 602 to store index keys 503. In one embodiment, each leaf page 602 of the index (e.g., an SQL index) is allocated a specific size (e.g., 3KB) by an expert or the like. In one embodiment, the monitoring engine 201 is configured to track the amount of memory used by the storage of index keys 503, and thus can determine the amount of memory space remaining to store additional index keys 503. Examples of software tools used by the monitoring engine 201 to track memory usage for leaf page 602 of an index (e.g., an SQL index) include, but are not limited to, SQLShack and ApexSQL by Quest®.

[0128] In one embodiment, the degree or level of lock contention in a leaf page 602 can be determined based on the memory usage of the leaf page 602 of the index and the number of index keys 503 to be written to the leaf page 602. For example, if the number of index keys 503 to be written to the leaf page 602 requires more than 50% of the remaining available memory space in the leaf page 602, then the leaf page 602 can be said to be experiencing lock contention. On the other hand, if the number of index keys 503 to be written to the leaf page 602 requires less than 25% of the remaining available memory space in the leaf page 602, then the leaf page 602 can be said to be experiencing a low level of lock contention.

[0129] If the level of lock contention in leaf page 602 that has previously experienced lock contention is not below a threshold level, the detector engine 205 continues to measure the degree of lock contention in leaf page 602 of the index previously identified as experiencing lock contention during operation 801.

[0130] However, if the level of lock contention in leaf page 602, which had previously experienced lock contention, is below a threshold level, in operation 803, the routing engine 203 of the relational database management system 102 removes the appropriate index key 503 from the index distribution queue buffer 506, which will be asynchronously inserted into leaf page 602 that is currently experiencing low-level lock contention, based on the index leaf router map 604, as shown in Figure 6.

[0131] Referring to Figure 6, for example, if it is determined that leaf page 602A ("Leaf #5") is currently experiencing a low level of lock contention, the routing engine 203 identifies which index keys 503 will be excluded from the index distribution queue buffer 506 and inserted into leaf page 602A, based on identifying the queue locations that store such index keys 503 which are mapped to such leaf page 602A in the index leaf router map 604. For example, the identifiers of the storage locations of index keys 503 (queue locations Q11, Q13, Q22, Q24, Q16_1, and Q16_4) are mapped to leaf page 602A. As a result, the routing engine 203 excludes such index keys 503 which will be stored in leaf page 602A in batches, etc., from queue 507 in the index distribution queue buffer 506.

[0132] In one embodiment, the routing engine 203 retrieves from the index distribution queue buffer 506 only the number of index keys 503 that a leaf page 602 (e.g., leaf page 602A) can currently store without reaching a “lock contention” state (i.e., a high-level lock contention as opposed to a low-level lock contention). In one embodiment, the lock contention state of leaf page 602 (e.g., leaf page 602A) is continuously monitored by the monitoring engine 201, and as a result, the number of index keys 503 stored in the index distribution queue buffer 506 as opposed to those stored in leaf page 602 (e.g., leaf page 602A), or the number of index keys 503 that are excluded from the index distribution queue buffer 506 and inserted into leaf page 602 (e.g., leaf page 602A) by the routing engine 203 is dynamically executed.

[0133] In addition, as discussed below in relation to Figure 9, in one embodiment, the index key 503 may be removed from the index distribution queue buffer 506 and synchronously inserted into the leaf page 602 (e.g., leaf page 602A) in response to the detection of the splitting of the leaf page 602 (e.g., leaf page 602A), the triggering of a system checkpoint on the leaf page 602 (e.g., leaf page 602A), or the reception of a SELECT, UPDATE, or DELETE statement (e.g., a SELECT SQL statement, an UPDATE SQL statement, or a DELETE SQL statement) involving such a leaf page 602 (e.g., leaf page 602A).

[0134] Figure 9 is a flowchart of a method 900 for synchronously inserting an index key 503 into leaf page 602 (e.g., leaf page 602A) in response to detecting an index partition of leaf page 602 (e.g., leaf page 602A), a system checkpoint involving leaf page 602 (e.g., leaf page 602A), or the reception of a SELECT, UPDATE, or DELETE statement involving leaf page 602 (e.g., leaf page 602A).

[0135] Referring to Figure 9 in conjunction with Figures 1 to 3 and Figures 5 to 6, in operation 901, the detector engine 205 of the relational database management system 102 determines whether leaf page splitting, triggering a system checkpoint for leaf page 602, or receiving a SELECT, UPDATE, or DELETE statement involving leaf page 602 has been detected.

[0136] As discussed above, in one embodiment, the detector engine 205 is configured to detect leaf page splitting, triggering a system checkpoint for leaf page 602, and receiving a SELECT, UPDATE, or DELETE statement involving leaf page 602.

[0137] As discussed above, as used herein, “splitting” of a leaf page 602 refers to a situation where there is insufficient memory space to add new data (e.g., new rows) that needs to be on a certain leaf page, and as a result, the leaf page 602 must be split. When a split occurs, the leaf page 602 may be split into two pages, each having approximately half the rows of the original leaf page 602. As a result, the detector engine 205 detects a leaf page split when creating a new leaf page 602 to store half of the data stored on the other leaf page 602.

[0138] Furthermore, as discussed above, as used herein, a “system checkpoint” for leaf page 602 refers to a test operation that verifies data such as the index key 503 obtained from leaf page 602 by comparing the data with a baseline copy. In one embodiment, the detector engine 205 detects such a system checkpoint based on the detection of checkpoint issuance by the database engine 206 of the relational database management system 102 (e.g., the SQL Server database engine).

[0139] In addition, as discussed above, SELECT statements, such as SQL SELECT statements, are used to select data from database 104. UPDATE statements, such as SQL UPDATE statements, are used to modify existing records in tables in database 104. DELETE statements, such as SQL DELETE statements, are used to delete existing records in tables in database 104. In one embodiment, the detector engine 205 is configured to detect the reception of such statements by identifying such statements in queries received from computing device 101 by relational database management system 102. In one embodiment, the detector engine 205 uses natural language processing to identify such statements in queries, and such terms are stored in a data structure stored by an expert. In one embodiment, such a data structure is stored in a storage device of relational database management system 102 (e.g., memory 305, disk unit 308).

[0140] If no leaf page splitting, triggering a system checkpoint for leaf page 602, or receiving a SELECT, UPDATE, or DELETE statement involving leaf page 602 is detected, the detector engine 205 continues in operation 901 to determine whether a leaf page splitting, triggering a system checkpoint for leaf page 602, or receiving a SELECT, UPDATE, or DELETE statement involving leaf page 602 is detected.

[0141] However, as shown in Figure 6, if a leaf page split, a system checkpoint trigger for leaf page 602, or the reception of a SELECT, UPDATE, or DELETE statement involving leaf page 602 is detected, in operation 902, the routing engine 203 of the relational database management system 102 removes the index key 503 that would be synchronously inserted into leaf page 602 (e.g., leaf page 602A) from the index distribution queue buffer 506 in response to detecting a leaf page split for such leaf page 602 (e.g., leaf page 602A), a system checkpoint trigger for such leaf page 602 (e.g., leaf page 602A), or the reception of a SELECT, UPDATE, or DELETE statement involving such leaf page 602 (e.g., leaf page 602A).

[0142] Referring to Figure 6, for example, when a leaf page split of leaf page 602A, a trigger for a system checkpoint involving leaf page 602A, or the reception of a SELECT, UPDATE, or DELETE statement involving leaf page 602A is detected, the routing engine 203 identifies which index keys 503 will be excluded from the index distribution queue buffer 506 and inserted into leaf page 602A, based on identifying the location of the queue storing such index keys 503 mapped to such leaf page 602A in the index leaf router map 604. For example, identifiers of the storage locations of index keys 503 mapped to leaf page 602A (queue locations Q11, Q13, Q22, Q24, Q16_1, and Q16_4) are stored in the index leaf router map 604. As a result, the routing engine 203 excludes such index keys that will be stored in leaf page 602A in batches, etc., from queue 507 in the index distribution queue buffer 506.

[0143] In one embodiment, the routing engine 203 retrieves from the index distribution queue buffer 506 only the number of index keys 503 that a leaf page 602 (e.g., leaf page 602A) can currently store without reaching a “lock contention” state (i.e., a high-level lock contention as opposed to a low-level lock contention). In one embodiment, the lock contention state of leaf page 602 (e.g., leaf page 602A) is continuously monitored by the monitoring engine 201, and as a result, the number of index keys 503 stored in the index distribution queue buffer 506 as opposed to those stored in leaf page 602 (e.g., leaf page 602A), or the number of index keys 503 that are excluded from the index distribution queue buffer 506 and inserted into leaf page 602 (e.g., leaf page 602A) by the routing engine 203 is dynamically executed.

[0144] Furthermore, as discussed below in relation to Figure 10, in one embodiment, the relational database management system 102 may asynchronously pre-allocate multiple leaf pages 602 to reduce the possibility of future lock contention after index partitioning when a sequential insertion pattern is detected. As used herein, “pre-allocate” ensures that memory space in such leaf pages 602 is available when the routing engine 203 needs to store an index key 503 in such a leaf page 602.

[0145] Figure 10 is a flowchart of Method 1000 for pre-allocating leaf page 602 during index splitting when a potential future lock contention is detected, according to an embodiment of the present disclosure.

[0146] Referring to Figure 10 in conjunction with Figures 1 to 3 and Figures 5 to 6, in operation 1001, the detector engine 205 of the relational database management system 102 determines whether a sequential pattern of index key 503 has been detected as being inserted into leaf page 602 during index partitioning.

[0147] If the sequential pattern of index key 503 is not detected as being inserted into leaf page 602 during index splitting, the detector engine 205 continues to determine in operation 1001 whether the sequential pattern of index key 503 is detected as being inserted into leaf page 602 during index splitting.

[0148] However, if a sequential pattern of index key 503 is detected as being inserted into leaf page 602 during index partitioning, in operation 1002, the detector engine 205 of the relational database management system 102 asynchronously pre-allocates multiple leaf pages 602 of the index to reduce possible lock contention scenarios.

[0149] As discussed above, if an index split occurs, a leaf page 602 may be split into two pages, each having approximately half the rows of the original leaf page. In one embodiment, if a sequential pattern (continuous movement pattern) of index key 503 is input to such a leaf page 602, the detector engine 205 asynchronously pre-allocates multiple leaf pages 602 of the index to reduce possible lock contention scenarios. Since a sequential pattern of index key 503 can continue within such a leaf page 602, it can be said that in such a situation, lock contention is more likely to occur. In one embodiment, such a continuous movement pattern is detected by the detector engine 205 by predicting the key values ​​as non-leaf key values ​​for those newly pre-allocated leaf pages 602 using high / low key values ​​from previous leaf pages 602. An example of such pre-allocation according to embodiments of the present disclosure is shown in Figure 11.

[0150] Figure 11 shows pre-allocating leaf pages during index partitioning according to an embodiment of the present disclosure.

[0151] Referring to Figure 11, leaf pages 602E to 602G (identified as "Leaf #8," "Leaf #9," and "Leaf #10," respectively, in Figure 11) are pre-allocated asynchronously to reduce potential lock contention scenarios resulting from the detection of sequential patterns (continuous movement patterns) of index keys 503 being input to leaf pages 602 (e.g., leaf pages 602A to 602D) during index partitioning. As a result of this pre-allocation, memory space within leaf pages 602E to 602G is available when the routing engine 203 needs to store the index key 503 in such leaf pages 602 based on the detected sequential pattern of the index key 503.

[0152] Furthermore, in one embodiment, the mapping engine 204 pre-assigns the mapping of such pre-assigned leaf pages 602 within the index leaf router map 604, as shown in Figure 11. For example, the index leaf router map 604 stores the mapping of index keys 503 stored in leaf pages 602A, 602B, and 602C (identified as "leaf #5", "leaf #6", and "leaf #7", respectively, in Figure 11). In addition, the index leaf router map 604 stores page free size information 605A to 605C for leaf pages 602A to 602C, respectively. With respect to pre-assigned leaf pages 602E to 602G, the mapping engine 204 pre-assigns the mapping of such pre-assigned leaf pages 602 to the index leaf router map 604, which also stores page free size information 605D to 605F for such pre-assigned leaf pages 602E to 602G, respectively.

[0153] As a result of the foregoing, embodiments of the present disclosure provide means for effectively handling lock contention on leaf pages of an index, such as an SQL index, by utilizing a buffer (referred to herein as the “index distribution queue buffer”) for storing such index keys in a buffer queue, as opposed to storing the index keys on leaf pages experiencing lock contention. When such a leaf page no longer experiences such lock contention, the appropriate index key is then removed from the index distribution queue buffer and stored on the appropriate leaf page based on a data structure (referred to herein as the “index leaf router map”) that maps the index keys stored in the index distribution queue buffer to the appropriate leaf pages of the index.

[0154] Furthermore, the principles of this disclosure improve the technology or technical field relating to relational database management systems. As discussed above, SQL queries, such as SQL INSERT INTO statements, can be received and processed by a relational database management system (e.g., SQL Server). Such queries (e.g., SQL INSERT INTO statements) may be used to add new data rows to a table in a database. When such a scenario occurs, the associated indexes defined on this table need to be updated. An index is an on-disk structure associated with a table or view that speeds up the retrieval of rows from that table or view. An index typically contains keys constructed from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables the relational database management system to quickly and efficiently find one or more rows associated with the key values. Consequently, when new data rows are added to a table in a database, the index needs to be updated to include the key values ​​associated with such data rows. Storing such key values ​​in an index is referred to herein as a “transaction.” A B-tree index creates a multi-level tree structure that divides the database into fixed-size blocks or pages. child tree Each level You can use this to link those pages through the location of the address, and a certain page (known as a node or internal page) another It is possible to refer to the page. And Leaf Page is at the lowest level.A certain page is typically the starting point of the tree, or the "root." Here, the search for a specific key begins, and the path is followed, ending at the leaf. That is, the leaf pages of an index are the lowest level of the index, where all keys for that index are displayed in sorted order. Most pages in such a structure are leaf pages that ultimately refer to a specific table row. Occasionally, multiple different transactions attempt to insert key values ​​simultaneously into the leaf pages of an index (e.g., an SQL index), resulting in a "lock contention" that negatively impacts performance. A "lock contention" always occurs when one process (transaction) attempts to acquire a "lock" held by another process (transaction). In an attempt to address lock contention, such transactions may be handled in a randomized manner. However, processing queries such as SQL queries associated with such transactions will still suffer performance degradation. Furthermore, in another attempt to address lock contention, the minimum page size of the index's leaf pages may be increased. However, this only slightly reduces lock contention. Furthermore, increasing the minimum page size of index leaf pages results in more database input / output operations being required during index access. Consequently, there is currently no effective means to handle lock contention on leaf pages of indexes (e.g., SQL indexes).

[0155] Embodiments of this disclosure improve such techniques by monitoring leaf pages of an index (e.g., an SQL index) for lock contention during transactional index key insertion operations. As used herein, “lock contention” refers to any time when one process (transaction) attempts to acquire a “lock” on a leaf page of an index held by another process (transaction). As used herein, “index” refers to an on-disk structure associated with a table or view that speeds up the retrieval of rows from the table or view. In one embodiment, such an index stores “keys” or “index keys” constructed from one or more columns in the table or view. As used herein, “index keys” refers to the keys of an index (e.g., an SQL index) that enable a relational database management system to quickly and efficiently find one or more rows associated with key values. Storing such key values ​​in an index is referred herein to as a “transaction.” Furthermore, the “leaf pages” of an index are the lowest level of the index, where all keys for that index are displayed in sorted order. When a lock conflict is detected on a leaf page, the next index key to be entered into that leaf page is routed to a queue in a buffer (referred to herein as the “index distribution queue buffer”). The index key stored in the queue of the index distribution queue buffer is then mapped to the specific leaf page experiencing the lock conflict, from which the transaction initially attempted to store such an index key. In one embodiment, such mappings are stored in a data structure referred herein as the “index leaf router map”, which stores the mappings of index keys stored in the queue of the index distribution queue buffer to leaf pages of the index.When such leaf pages no longer experience such lock contention, the appropriate index key is then removed from the index distribution queue buffer and stored in the appropriate leaf page based on the mapping in the index leaf router map. In this way, lock contention in leaf pages of indexes such as SQL indexes is effectively handled. Furthermore, this method brings about improvements in the technical field related to relational database management systems.

[0156] The technical solutions provided by this disclosure cannot be implemented in the human mind or by a person using pen and paper. That is, the technical solutions provided by this disclosure cannot be implemented in the human mind or by a person using pen and paper without the use of a computer, in any reasonable amount of time, and with any reasonable expectation of accuracy.

[0157] The descriptions of the various embodiments of this disclosure are presented for illustrative purposes only and are not intended to be comprehensive or limitless. Many modifications and variations that do not deviate from the scope and spirit of the described embodiments will be apparent to those skilled in the art. The terminology used herein has been selected to best describe the principles of the embodiments, their practical applications, or technical improvements to the technology available on the market, or to enable other persons skilled in the art to understand the embodiments disclosed herein. [Item 1] A computer implementation method for handling index lock contention: A step in which lock conflicts for the leaf pages of the index are monitored during the insertion of an index key; In response to detecting the lock contention on the leaf page of the index, the first index key is routed to a buffer queue, where the first index key corresponds to the index key of the transaction to be inserted into the leaf page of the index; and A step of storing in a data structure the mapping of the first index key stored in the queue of the buffer to the leaf page experiencing the lock contention, wherein the data structure stores the mapping of the index key stored in the queue of the buffer to the leaf page of the index. A computer implementation method comprising the following features. [Item 2] The computer implementation method according to item 1, wherein the lock contention is detected based on the length of the page or row-level lock wait queue, or based on the length of the latch wait queue. [Item 3] The computer implementation method according to item 1 or 2, further comprising the step of routing a second index key following the first index key to the queue of the buffer, wherein the index keys stored in the queue of the buffer are synchronized via compare and swap. [Item 4] The computer implementation method described in any of the preceding items, wherein the data structure includes an identifier for the storage location of the first index key in the queue of the buffer, along with an identifier for the leaf page experiencing the lock contention, and the identifier for the storage location of the index key in the data structure is synchronized via compare and swap. [Item 5] A computer implementation method according to any of the above items, further comprising the step of removing from the buffer an index key that will be asynchronously inserted into the leaf page based on the data structure in response to detecting that the level of lock contention in the leaf page is below a threshold level. [Item 6] The computer implementation method according to any of the above items, further comprising the steps of: splitting the leaf page, triggering a system checkpoint for the leaf page, and detecting one of the group consisting of SELECT, UPDATE, or DELETE statements associated with the leaf page, and removing from the buffer an index key that will be synchronously inserted into the leaf page based on the data structure. [Item 7] The step of creating a new queue in the buffer in response to the queue reaching a threshold percentage of the maximum queue length; and The stage of routing newly incoming index keys to the new queue. A computer implementation method described in any of the above items, further comprising the above. [Item 8] A computer program product for handling index lock contention, comprising one or more computer-readable storage media on which the program code is embodied, wherein the program code is: A procedure for monitoring lock conflicts for leaf pages of the index during the insertion of an index key; A procedure for routing a first index key to a buffer queue in response to detecting the lock contention on the leaf page of the index, wherein the first index key corresponds to the index key of a transaction to be inserted into the leaf page of the index; and A procedure for storing in a data structure the mapping of the first index key stored in the queue of the buffer to the leaf page experiencing the lock contention, wherein the data structure stores the mapping of the index key stored in the queue of the buffer to the leaf page of the index. A computer program product having programming instructions for [a specific purpose]. [Item 9] The aforementioned lock contention is detected based on the length of a page or row-level lock wait queue, or based on the length of a latch wait queue, as described in item 8 of the computer program product. [Item 10] The aforementioned program code is: A procedure for routing a second index key following the first index key to the queue of the buffer, wherein the index keys stored in the queue of the buffer are synchronized via compare and swap. A computer program product according to item 8 or 9, further comprising the aforementioned programming instructions for the computer program. [Item 11] The data structure includes an identifier for the storage location of the first index key in the queue of the buffer, along with an identifier for the leaf page experiencing the lock contention, and the identifier for the storage location of the index key in the data structure is synchronized via compare and swap, as described in any of items 8 to 10 of the Computer Program Product. [Item 12] The aforementioned program code is: A procedure to remove from the buffer an index key that would be asynchronously inserted into the leaf page based on the data structure, in response to detecting that the level of lock contention in the leaf page is below a threshold level. A computer program product according to any one of items 8 to 11, further comprising the aforementioned programming instructions for the computer program. [Item 13] The aforementioned program code is: The following is a procedure for removing from the buffer an index key that will be synchronously inserted into the leaf page based on the data structure, in response to detecting the splitting of the leaf page, the triggering of a system checkpoint for the leaf page, and the detection of one of the group consisting of a SELECT statement, an UPDATE statement, or a DELETE statement associated with the leaf page. A computer program product according to any one of items 8 to 12, further comprising the aforementioned programming instructions for the computer program. [Item 14] The aforementioned program code is: A procedure for creating a new queue in the buffer in response to the queue reaching a threshold percentage of the maximum queue length; and Procedure for routing newly incoming index keys to the new queue. A computer program product according to any one of items 8 to 13, further comprising the aforementioned programming instructions for the computer program. [Item 15] Memory for storing computer programs for handling index lock contention; and A processor connected to the aforementioned memory, wherein the processor is: A procedure for monitoring lock conflicts for leaf pages of the index during the insertion of an index key; A procedure for routing a first index key to a buffer queue in response to detecting the lock contention on the leaf page of the index, wherein the first index key corresponds to the index key of a transaction to be inserted into the leaf page of the index; and A procedure for storing in a data structure the mapping of the first index key stored in the queue of the buffer to the leaf page experiencing the lock contention, wherein the data structure stores the mapping of the index key stored in the queue of the buffer to the leaf page of the index. The computer program is configured to execute program instructions of the computer program having the above-mentioned features. A system equipped with these features. [Item 16] The lock contention is detected based on the length of the page or row-level lock wait queue, or based on the length of the latch wait queue, as described in item 15. [Item 17] The program instructions of the aforementioned computer program are: A procedure for routing a second index key following the first index key to the queue of the buffer, wherein the index keys stored in the queue of the buffer are synchronized via compare and swap. The system described in item 15 or 16, further comprising: [Item 18] The data structure includes an identifier for the storage location of the first index key in the queue of the buffer, along with an identifier for the leaf page experiencing the lock contention, and the identifier for the storage location of the index key in the data structure is synchronized via compare and swap, as described in any of items 15 to 17. [Item 19] The program instructions of the aforementioned computer program are: A procedure to remove from the buffer an index key that would be asynchronously inserted into the leaf page based on the data structure, in response to detecting that the level of lock contention in the leaf page is below a threshold level. A system further comprising any of the items 15-18. [Item 20] The program instructions of the aforementioned computer program are: The following is a procedure for removing from the buffer an index key that will be synchronously inserted into the leaf page based on the data structure, in response to detecting the splitting of the leaf page, the triggering of a system checkpoint for the leaf page, and the detection of one of the group consisting of a SELECT statement, an UPDATE statement, or a DELETE statement associated with the leaf page. A system further comprising any of the items 15-19. [Item 21] A computer program comprising program code means adapted to perform any of the computer implementation methods described in items 1 to 7 when the computer program is executed on a computer.

Claims

1. A computer-based method for handling index lock contention: A step in which lock conflicts for the leaf pages of the index are monitored during the insertion of the index key; In response to detecting the lock contention on the leaf page of the index, the first index key is routed to a buffer queue, where the first index key corresponds to the index key of a transaction to be inserted into the leaf page of the index; and A step of storing in a data structure the mapping of the first index key stored in the queue of the buffer to the leaf page experiencing the lock contention, wherein the data structure stores the mapping of the index key stored in the queue of the buffer to the leaf page of the index. A method performed by a computer, comprising the following:

2. The method performed by a computer according to claim 1, wherein the lock contention is detected based on the length of a page or row-level lock wait queue, or based on the length of a latch wait queue.

3. A method performed by a computer according to claim 1 or 2, further comprising the step of routing a second index key following the first index key to the queue of the buffer, wherein the index keys stored in the queue of the buffer are synchronized via compare and swap.

4. The method performed by a computer according to claim 1, wherein the data structure includes an identifier for the storage location of the first index key in the queue of the buffer, along with an identifier for the leaf page experiencing the lock contention, and the identifier for the storage location of the index key in the data structure is synchronized via compare and swap.

5. The method performed by a computer according to claim 1, further comprising the step of removing from the buffer an index key that will be asynchronously inserted into the leaf page based on the data structure in response to detecting that the level of lock contention in the leaf page is below a threshold level.

6. The method performed by a computer according to claim 1, further comprising the steps of: detecting the division of the leaf page, triggering a system checkpoint for the leaf page, and detecting one selected from the group consisting of a SELECT statement, an UPDATE statement, or a DELETE statement associated with the leaf page, and removing from the buffer an index key that will be synchronously inserted into the leaf page based on the data structure.

7. The step of creating a new queue in the buffer in response to the queue reaching a threshold percentage of the maximum queue length; and The stage of routing newly incoming index keys to the new queue. A computer-based method according to claim 1, further comprising:

8. A computer program for handling index lock contention, which allows a computer to: A procedure for monitoring lock conflicts for the leaf pages of the index during the insertion of an index key; A procedure for routing a first index key to a buffer queue in response to detecting the lock contention on the leaf page of the index, wherein the first index key corresponds to the index key of a transaction to be inserted into the leaf page of the index; and A procedure for storing in a data structure the mapping of the first index key stored in the queue of the buffer to the leaf page experiencing the lock contention, wherein the data structure stores the mapping of the index key stored in the queue of the buffer to the leaf page of the index. A computer program designed to execute something.

9. The computer program according to claim 8, wherein the lock contention is detected based on the length of a page or row-level lock wait queue, or based on the length of a latch wait queue.

10. To the aforementioned computer: A procedure for routing a second index key following the first index key to the queue of the buffer, wherein the index keys stored in the queue of the buffer are synchronized via compare and swap. A computer program according to claim 8 for further execution of the above.

11. The computer program according to claim 8, wherein the data structure includes an identifier for the storage location of the first index key in the queue of the buffer, along with an identifier for the leaf page experiencing the lock contention, and the identifier for the storage location of the index key in the data structure is synchronized via compare and swap.

12. To the aforementioned computer: A procedure to remove from the buffer an index key that would be asynchronously inserted into the leaf page based on the data structure, in response to detecting that the level of lock contention in the leaf page is below a threshold level. A computer program according to claim 8 for further execution of the above.

13. To the aforementioned computer: The following is a procedure for removing from the buffer an index key that will be synchronously inserted into the leaf page based on the data structure, in response to detecting the splitting of the leaf page, the triggering of a system checkpoint for the leaf page, and one of the group consisting of a SELECT statement, an UPDATE statement, or a DELETE statement associated with the leaf page. A computer program according to claim 8 for further execution of the above.

14. To the aforementioned computer: A procedure for creating a new queue in the buffer in response to the queue reaching a threshold percentage of the maximum queue length; and Procedure for routing newly incoming index keys to the new queue. A computer program according to claim 8 for further execution of the above.

15. Memory for storing computer programs for handling index lock contention; and A processor connected to the aforementioned memory, wherein the processor is: A procedure for monitoring lock conflicts for the leaf pages of the index during the insertion of an index key; A procedure for routing a first index key to a buffer queue in response to detecting the lock contention on the leaf page of the index, wherein the first index key corresponds to the index key of a transaction to be inserted into the leaf page of the index; and A procedure for storing in a data structure the mapping of the first index key stored in the queue of the buffer to the leaf page experiencing the lock contention, wherein the data structure stores the mapping of the index key stored in the queue of the buffer to the leaf page of the index. The computer program is configured to execute program instructions of the computer program having the above-mentioned features. A system equipped with these features.

16. The system according to claim 15, wherein the lock contention is detected based on the length of a page or row-level lock wait queue, or based on the length of a latch wait queue.

17. The program instructions of the aforementioned computer program are: A procedure for routing a second index key following the first index key to the queue of the buffer, wherein the index keys stored in the queue of the buffer are synchronized via compare and swap. The system according to claim 15, further comprising the above.

18. The system according to claim 15, wherein the data structure includes an identifier for the storage location of the first index key in the queue of the buffer, along with an identifier for the leaf page experiencing the lock contention, and the identifier for the storage location of the index key in the data structure is synchronized via compare and swap.

19. The program instructions of the aforementioned computer program are: A procedure to remove from the buffer an index key that would be asynchronously inserted into the leaf page based on the data structure, in response to detecting that the level of lock contention in the leaf page is below a threshold level. The system according to claim 15, further comprising the above.

20. The program instructions of the aforementioned computer program are: The following is a procedure for removing from the buffer an index key that will be synchronously inserted into the leaf page based on the data structure, in response to detecting the splitting of the leaf page, the triggering of a system checkpoint for the leaf page, and one of the group consisting of a SELECT statement, an UPDATE statement, or a DELETE statement associated with the leaf page. The system according to claim 15, further comprising the above.

Images