Data redundancy is often introduced into enterprise systems as a method of backup (e.g., to prevent data loss in the case of system failure). One method of data redundancy in the field of databases is database mirroring. In database mirroring, a secondary database is kept in a manner that mirrors a primary database. As modifications occur at the primary database, the modifications are mirrored at the secondary database. Thus, the secondary database is said to be a “mirror” of the primary database.
Secondary databases that are generated by mirroring are typically kept for backup purposes only. That is, even though the secondary database is kept identical to the primary database, the secondary database is usually not made available to clients, even in a read-only mode. This is usually done to avoid data integrity issues that may arise when data is read from the secondary database while a transaction that has already occurred at the primary database is being mirrored at the secondary database. For example, making a secondary database available to readers may result in potential deadlocks. To illustrate, a top-down database reader may attempt to acquire database synchronization objects in a top-down order and a bottom-up database writer may attempt to acquire database synchronization objects in a bottom-up order. A deadlock may result when the reader is waiting for a downstream synchronization object from the writer, and the writer is waiting an upstream synchronization object from the reader. To avoid deadlock situations in multi-page operations, locks for pages to be modified are acquired before modifying any of the pages, and the locks are released after all modifications have been performed. However, this may be inefficient, because individual pages undergoing minor modification may remain locked even after their modification is complete.
Systems and methods of database mirroring are disclosed, where reader threads and writer threads at a mirror database are synchronized such that the mirror database is available for read operations. In addition, a redo thread at a mirror database may latch (e.g., prohibit readers from reading) one database page at a time, even while mirroring large database transactions that modified multiple pages at a principal database. Thus, one or more readers may perform partial reads at a mirror database while a multi-page modification is being performed at the mirror database.
This Summary is provided to introduce a selection of concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.
Systems, methods, and computer-readable media of database mirroring are disclosed. Database mirroring as disclosed herein may enable a mirror database to be available for read operations and may synchronize reader threads of the mirror database with a redo thread of the mirror database to preserve data integrity. Such database mirroring may be achieved using a three-part system. A first part of the system may involve transaction log generation at a principal database. A second part may involve a redo thread at a mirror database using the transaction log to reproduce (i.e., “mirror”) transactions of the principal database. A third part may involve operating reader threads at the mirror database. Each of the three parts may use structure modification operation (SMO) bits, SMO locks, and latches for synchronization purposes.
In a particular embodiment, a method includes initiating a database transaction at a first database. The first database includes a plurality of pages, and each of the plurality of pages includes a structure modification operation (SMO) bit. The database transaction modifies one or more pages of the first database and is initiated by setting the SMO bit of each of the one or more pages to a first value. The method also includes creating one or more first records in a transaction log of the first database. The transaction log is usable at a second database to mirror the database transaction. Each of the one or more first records indicates that the SMO bit of a particular page was set to the first value. The method further includes performing the database transaction, including modifying the one or more pages. The method includes setting the SMO bit of each of the one or more pages to a second value. The method also includes creating one or more second records in the transaction log. Each of the one or more second records indicates that the SMO bit of a particular page was set to the second value. The method further includes committing the database transaction.
In another particular embodiment, a method of mirroring a database transaction of a first database at a second database based on a transaction log of the first database is disclosed. The method includes determining that one or more first records of the transaction log indicate that a SMO bit of each of one or more pages of the first database is set to a first value. The method also includes identifying one or more pages of the second database that correspond to the one or more pages of the first database. The method further includes acquiring one or more exclusive SMO locks. Each particular exclusive SMO lock is associated with a particular page of one or more pages of the second database. The method includes setting a SMO bit of each of the one or more pages of the second database to the first value and mirroring the database transaction. Mirroring the database transaction includes modifying each of the one or more pages of the second database based on the transaction log. The method also includes determining that one or more second records of the transaction log indicate that the SMO bit of each of the one or more pages of the first database is set to a second value. The method further includes setting the SMO bit of the one or more pages of the second database to the second value. The method includes releasing the plurality of exclusive SMO locks after the mirroring is completed.
In another particular embodiment, a computer-readable medium is disclosed. The computer-readable medium includes instructions, that when executed by a computer, cause the computer to receive a command to initiate a read operation of a first page and a second page of a database. The command is received at a reader thread of the database. The instructions also cause the computer to acquire a first shared latch associated with the first page and to read first data from the first page of the database. The instructions further cause the computer to determine that a SMO bit of the second page indicates a pending database transaction at the second page. The instructions cause the computer to release the first shared latch and the second shared latch and to enter a sleep mode until a shared SMO lock associated with the second page is acquired. The shared SMO lock is acquired subsequent to completion of the pending database transaction. The instructions also cause the computer to re-acquire the second shared latch after acquiring the shared SMO lock and to determine that the SMO bit of the second page no longer indicates the pending database transaction at the second page. The instructions cause the computer to read second data from the second page of the database.
The principal database 110 may be a read-write database that includes a plurality of pages 140, 144, 148, 152 that are accessible to a plurality of reader threads 114 and to a plurality of writer threads 112. Each of the plurality of reader threads 114 may read data 143, 147, 151, 155 from the pages 140, 144, 148, 152, respectively. Each of the plurality of writer threads 112 may write data 143, 147, 151, 155 to the pages 140, 144, 148, 152, respectively. In a particular embodiment, the reader threads 114 and writer threads 112 are synchronized to preserve data integrity (e.g., prevent a reader thread from reading data at a page that is being modified by a writer thread). For example, the reader threads 114 and the writer threads 112 may use shared mode and exclusive mode latches for synchronization.
In a particular embodiment, each page 140, 144, 148, 152 of the principal database 110 has an associated latch that may be acquired in either a shared mode or an exclusive mode. A latch may be acquired multiple times in shared mode but only once in exclusive mode. Thus, latches may support a “many-read, single-write” synchronization scheme. For example, multiple reader threads may acquire the latch for a particular page in the shared mode prior to reading data from the particular page. A writer thread may acquire the latch for the particular page in the exclusive mode prior to modifying a particular page, thereby preventing other reader threads from reading dirty (e.g., inconsistent) data and preventing other writer threads from overwriting the modifications before they are committed at the principal database 110.
The mirror database 130 may be located remotely from the principal database 110. The mirror database 130 is accessible by a redo thread 132 and one or more reader threads 134. The mirror database 130 may include a plurality of pages, including a corresponding page for each page of the principal database 110. For example, the pages 160, 164, 168, and 172 may correspond to the pages 140, 144, 148, and 152, respectively. Thus, database mirroring between the principal database 110 and the mirror database 130 may result in the data 163, 167, 171, and 175 being physically identical to the data 143, 147, 151, and 155, respectively.
As database transactions occur at the principal database 110, modifications to the principal database 110 are logged in the transaction log 120. The transaction log may be used by the redo thread 132 at the mirror database 130 to replicate (i.e., “mirror”) the database transactions of the principal database 110, thereby keeping the mirror database 130 physically identical to the principal database 110. In a particular embodiment, newly-generated records of the transaction log 120 are transmitted from the principal database 110 to the mirror database 130 via a transaction log stream 122. In another particular embodiment, transaction logs are transmitted from the principal database 110 to the mirror database 130 periodically.
In a particular embodiment, each record of the transaction log 120 has a log sequence number (LSN). For example, LSNs may increase when the transaction log 120 is arranged in chronological order. When the transaction log 120 includes LSNs, each page of the databases 110, 130 may include an LSN in the page header, where the value of the LSN at a particular page corresponds to the most recent record of the transaction log 120 associated with a modification of the particular page. For example, the pages 140, 144, 148, 152, 160, 164, 168, and 172 include the LSNs 142, 146, 150, 154, 162, 166, 170, and 174, respectively. Storing an LSN at each page of the databases 110, 130 may provide a timestamp at each page that indicates when each page was last modified.
Each page of the principal database 110 and the mirror database 130 includes a structure modification operation (SMO) bit. The SMO bit may be part of a page header of each page. For example, the pages 140, 144, 148, 152, 160, 164, 168, and 172 include the SMO bits 141, 141, 149, 153, 161, 165, 169, and 173, respectively. Both the principal database 110 and the mirror database 130 may support SMO locks. SMO bits and SMO locks may be used to mirror database transactions of the principal database 110 at the mirror database 130, as described herein. SMO bits and SMO locks may also be used to maintain synchronization between the redo thread 132 and the reader threads 134 of the mirror database 130, as described herein.
A particular embodiment of operation at the system 100 of
Prior to the node splitting operation, the B+ tree includes a parent node P 210 that is connected to a first child node C1220 and to a second child node C2230. The parent node P 210 includes pointers to each of the child nodes C1-C2220-230. Each of the child nodes C1-C2220-230 includes pointers to each other, such as “previous leaf node” and “next leaf node” pointers. It should be noted that although the example depicted in
During the node splitting operation, some of the data stored at the first child node C1220 may be moved to a newly allocated third child node C3240. An additional pointer to the third child node C3240 may be added to the parent node P 210, and the “previous leaf node” and “next leaf node” pointers of the child nodes C1-C3220-240 may be modified as illustrated in
In an illustrative embodiment, each node 210-240 corresponds to a particular page of the principal database 110 of
In a particular embodiment, transaction log records are generated at a principal database to reflect operations performed in accordance with the following pseudocode:
In a particular embodiment, step 2) of the pseudocode further includes acquiring an exclusive latch for each page that is to be modified, and step 5) of the pseudocode further includes releasing the exclusive latches after the transaction is completed.
Thus, in accordance with the above pseudocode, the first record 311 may be added to the transaction log 300 to indicate the beginning of the “B+ Tree Split” transaction (TX) depicted in
In a particular embodiment, SMO bits are set to TRUE and exclusive latches are acquired in a particular order of the B+ tree. For example, the transaction log 300 of
An exclusive SMO lock associated with the pages modified by the node splitting operation may be acquired. The record 315 may be added to the transaction log 300 to indicate that an exclusive (EX) SMO lock is acquired for the pages. In a particular embodiment, a different exclusive SMO lock is acquired for each of the pages. In another particular embodiment, a single exclusive SMO lock on all of the pages may be acquired instead.
The record 316 may be added to the transaction log 300 to reflect the allocation of a new page corresponding to the third child node C3240 of
The record 318 of the transaction log 300 may reflect actions that are executed to complete the node splitting operation. For example, the record 318 may include instructions (e.g., to be followed by a redo thread at a mirror database) regarding how to add a pointer to the parent node 210 of
Upon completion of the B+ tree node splitting operation, the SMO bits of the pages corresponding to the nodes 210-240 of
The B+ tree node splitting operation may then be committed, as illustrated by the record 323. Committing a database transaction may “finalize” the database transaction. That is, once a database transaction is committed, it may not be cancelled. In a particular embodiment, the exclusive SMO lock(s) acquired as indicated by the record 315 are automatically released when the B+ tree node splitting operation is committed. In another particular embodiment, release of acquired latches may also be delayed until the commit operation.
It should be noted that although the particular embodiment illustrated in
A redo thread may traverse the transaction log 410 and perform corresponding actions 430 based on the transaction log 410. In a particular embodiment, a redo thread processes each record in a transaction in accordance with the following pseudocode:
Thus, in accordance with the above pseudocode, a redo thread (e.g., the redo thread 132 at the mirror database 130 of
The actions 432-434 may be performed in response to the records 412-414, respectively. During each of the actions 434, an exclusive SMO lock may be acquired for a particular page, an exclusive latch for the particular page may be acquired, the SMO bit of the particular page may be set to TRUE, and the exclusive latch for the particular page may be released.
The actions 436-437 may be performed in response to the records 416-417. During the action 436, a new page may be allocated at the mirror database. During the action 437, an exclusive SMO lock may be acquired for the new page, an exclusive latch for the new page may be acquired, the SMO bit of the new page may be set to TRUE, and the exclusive latch for the new page may be released.
In response to the record 418, the redo thread at the mirror database may complete the B+ tree node splitting operation at the mirror database, including copying, deleting and moving data, as indicated by the action 438. An exclusive latch may be acquired before any particular page is changed, and the exclusive latch may be released after the particular page has been changed.
In response to the records 419-422, the redo thread at the mirror database may acquire an exclusive latch, set the SMO bit to FALSE, and release the exclusive latch for C3, C2, C1, and P, respectively, as indicated by the actions 439-442. In response to the record 423 indicating that the B+ tree node splitting operation was committed at the principal database, the redo thread at the mirror database may commit the mirrored B+tree node splitting operation and release the acquired exclusive SMO locks, as illustrated by the action 443.
It will be appreciated that the system 100 of
The method 500 includes initiating a database transaction that modifies one or more pages of a first database, at 502. The first database includes a plurality of pages, and each page includes a SMO bit. Initiating the database transaction includes setting the SMO bit of each of the one or more pages to a first value. For example, in
The method 500 also includes creating one or more first records in a transaction log of the first database, at 504. The transaction log is useable at a second database to mirror the database transaction. Each of the first records indicates the setting of the SMO bit of a particular page of the one or more pages to the first value. For example, in
The method 500 further includes completing the database transaction, including modifying the one or more pages, at 506, and setting the SMO bit of each of the one or more pages to a second value, at 508. For example, in
The method 500 includes creating one or more second records in the transaction log, at 510. Each of the second records indicates the setting of the SMO bit of a particular page of the one or more pages to the second value. For example, one or more second SMO records may be created at the transaction log 120 of
The method 500 also includes committing the database transaction, at 512. For example, in
The method 600 includes determining that one or more first records of a transaction log of a first database indicate that a SMO bit of each of one or more pages of the first database is set to a first value, at 602. The first database is mirrored by a second database. For example, in
The method 600 also includes identifying one or more pages of the second database that correspond to the pages of the first database, at 604. For example, in
The method 600 further includes acquiring a plurality of exclusive SMO locks, at 606. Each exclusive SMO lock is associated with a particular page of the one or more pages of the second database. For example, in
The method 600 includes setting a SMO bit of each of the one or more pages of the second database to the first value, at 608, and mirroring the database transaction, at 610. Mirroring the database transaction includes modifying the one more pages of the second database based on the transaction log. For example, in
The method 600 further includes determining that one or more second records of the transaction log indicate that the SMO bit of each of the one or more pages of the first database is set to a second value, at 612. For example, in
The method 600 includes setting the SMO bit of the one or more pages of the second database to the second value. For example, in
The method 600 also includes releasing the plurality of exclusive SMO locks, at 616. For example, in
Because database mirroring as disclosed herein may enable concurrent operation of a redo thread and reader threads at a mirror database, a synchronization method may be used at the reader threads to avoid data integrity issues and deadlock scenarios. For example, a particular read operation may involve reading data from mirror database pages that correspond to the child nodes (i.e., leaf nodes) C1, C3210, 240 of
The method 700 includes receiving a command to initiate a read operation of a first page and a second page of a database at a reader thread of the database, at 702. For example, in
The method 700 also includes acquiring a first shared latch associated with the first page, at 704. For example, in
The method 700 includes acquiring a second shared latch associated with the second page, at 708. For example, in
The method 700 also includes determining that a SMO bit of the second page indicates a pending database transaction at the second page, at 710. For example, in
The method 700 further includes storing a LSN of the first page, at 712, and releasing the first shared latch and the second shared latch, at 714. For example, in
The method 700 also includes sleeping until a shared SMO lock associated with the second page is acquired, at 716. The shared SMO lock is acquired subsequent to completion of the pending database transaction. For example, in
The method 700 includes determining whether the LSN of the first page is equal to the stored LSN, at 718. When the LSN is not equal to the stored LSN (i.e., the first page was modified while the reader thread was asleep), the method 700 returns to 702. For example, the LSNs may not match due to a deallocation of the first page, a deletion of a file that includes the first page, or a shrink operation of the file that includes the first page.
When the LSN is equal to the stored LSN (i.e., the first page did not change while the reader thread was asleep), the method 700 includes re-acquiring the second latch, at 720, and determining that the SMO bit of the second page does not indicate a pending transaction at the second page, at 722. For example, in
It will be appreciated that the method 700 of
In a particular embodiment, database transactions may occur at one of five possible isolation levels: a read uncommitted isolation level (e.g., shared locks are not issued—therefore it is possible to read uncommitted data), a read committed isolation level (e.g., shared locks are issued, so only committed data is read), a repeatable read isolation level (e.g., all shared locks are held until the end of a transaction), a snapshot isolation level (e.g., data read at any point during a read operation will be consistent with the data that existed at the start of the read operation), or a serializable isolation level (e.g., data that has modified cannot be read or re-modified until committed). It will be appreciated that database mirroring as disclosed herein may enable mirroring of transactions at a snapshot isolation level. Because SMO bits are set to TRUE in the same order at the principal database and the mirror database, because SMO bits set to FALSE in the same (reverse) order at the principal database and the mirror database, and because only one latch is held at any time at the mirror database, data read during any point of the transaction will be consistent with the data at the beginning of the transaction.
The computing device 810 includes at least one processor 820 and a system memory 830. Depending on the configuration and type of computing device, the system memory 830 may be volatile (such as random access memory or “RAM”), non-volatile (such as read-only memory or “ROM,” flash memory, and similar memory devices that maintain stored data even when power is not provided), or some combination of the two. The system memory 830 typically includes an operating system 832, one or more application platforms, one or more applications (e.g., a database application 836 including reader thread(s) 837 and writer thread(s) 838), and program data (e.g., a transaction log 839) associated with the one or more applications. In an illustrative embodiment where the database application 836 provides access to the principal database 110 of
The computing device 810 may also have additional features or functionality. For example, the computing device 810 may also include removable and/or non-removable additional data storage devices such as magnetic disks, optical disks, tape, and flash memory cards. Such additional storage is illustrated in
The computing device 810 may also have input device(s) 860, such as a keyboard, mouse, pen, voice input device, touch input device, etc. Output device(s) 870, such as a display, speakers, printer, etc. may also be included. The computing device 810 also contains one or more communication connections 880 that allow the computing device 810 to communicate with other computing devices 890 and a database 892 over a wired or a wireless network. For example, the database 892 may be the principal database 110 of
It will be appreciated that not all of the components or devices illustrated in
The illustrations of the embodiments described herein are intended to provide a general understanding of the structure of the various embodiments. The illustrations are not intended to serve as a complete description of all of the elements and features of apparatus and systems that utilize the structures or methods described herein. Many other embodiments may be apparent to those of skill in the art upon reviewing the disclosure. Other embodiments may be utilized and derived from the disclosure, such that structural and logical substitutions and changes may be made without departing from the scope of the disclosure. Accordingly, the disclosure and the figures are to be regarded as illustrative rather than restrictive.
Those of skill would further appreciate that the various illustrative logical blocks, configurations, modules, and process steps or instructions described in connection with the embodiments disclosed herein may be implemented as electronic hardware or computer software. Various illustrative components, blocks, configurations, modules, or steps have been described generally in terms of their functionality. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the overall system. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present disclosure.
The steps of a method described in connection with the embodiments disclosed herein may be embodied directly in hardware or in a software module executed by a processor. A software module may reside in computer readable media, such as random access memory (RAM), flash memory, read only memory (ROM), registers, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art. An exemplary storage medium is coupled to a processor such that the processor can read information from, and write information to, the storage medium. In the alternative, the storage medium may be integral to the processor or the processor and the storage medium may reside as discrete components in a computing device or computer system.
Although specific embodiments have been illustrated and described herein, it should be appreciated that any subsequent arrangement designed to achieve the same or similar purpose may be substituted for the specific embodiments shown. This disclosure is intended to cover any and all subsequent adaptations or variations of various embodiments.
The Abstract of the Disclosure is provided with the understanding that it will not be used to interpret or limit the scope or meaning of the claims. In addition, in the foregoing Detailed Description, various features may be grouped together or described in a single embodiment for the purpose of streamlining the disclosure. This disclosure is not to be interpreted as reflecting an intention that the claimed embodiments require more features than are expressly recited in each claim. Rather, as the following claims reflect, inventive subject matter may be directed to less than all of the features of any of the disclosed embodiments.
The previous description of the embodiments is provided to enable a person skilled in the art to make or use the embodiments. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the scope of the disclosure. Thus, the present disclosure is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope possible consistent with the principles and novel features as defined by the following claims.