Database server system with improved methods for logging transactions

• US 6,321,234 B1
• Filed: 11/08/1996
• Issued: 11/20/2001
• Est. Priority Date: 09/18/1996
• Status: Expired due to Term

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Debrunner, Daniel
• Primary Examiner(s): Banankhah, Majid A.
• Application Number:
  US08/744,870
• Time in Patent Office: 1,838 Days
• Field of Search: 707/202, 707/203, 707/204, 707/2, 707/6, 707/9, 707/10, 707/7, 707/201, 709/100, 709/101
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• CPC Class Codes: Y10S 707/99939 Privileged access
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A SQL database server system having an enhanced logging system is described. The logging system implements a “private log cache” (PLC) for reducing the contention on the system'"'"'s “log” resource (which is protected by a log semaphore). An area of memory private to a user'"'"'s task is set aside as a PLC—a cache where log records are built and stored before being posted to the log. Each PLC may hold multiple log records for a single transaction before they are flushed to the log (page chain) through the log semaphore. When a transaction commits or the memory fills with log records, the PLC associated with the transaction is flushed to the log. Because the log records for a complete transaction are immediately transferred through the log semaphore, contention on the log semaphore decreases. Contention alleviated by the PLC dramatically increases transaction throughput of the database server system.

Claims

1. In a database system for processing a sequence of database changes as a transaction, said database system capable of processing multiple transactions from concurrently connected clients, a method for posting changes occurring in the database system to a transaction log in a manner that reduces contention among transactions for the transaction log, the method comprising:

receiving multiple transactions, each transaction being received as a sequence of SQL commands from a given client requesting changes to a database;

for each change to the database for a given transaction of a given client, creating a log record describing the change and storing the log record in a private log cache for that given transaction, said private log cache for storing log records which have been created for that given transaction but not yet posted to the transaction log, so that each transaction is associated with its own private log cache; and

upon receiving from a particular client for a particular transaction one of the SQL commands which commits changes to the database, performing substeps of;

(i) flushing the private log cache for that particular transaction to the transaction log, and

(ii) flushing the transaction log to disk, wherein said log records are flushed to the transaction log before any corresponding data pages affected by said given task are flushed to disk;

wherein at least one change includes a modification to a data page stored in memory and wherein said data page is pinned to said private log cache such that said data page can only be written to disk after its corresponding log records for the modification are flushed from the private log cache to the transaction log and then flushed from the transaction log to disk.

2. The method of claim 1, wherein each transaction includes an SQL “

BEGIN TRANSACTION”

command and an SQL “

COMMIT”

command.

3. The method of claim 2, wherein the private log cache is flushed to the transaction log upon receipt of the SQL “

COMMIT”

command.

4. The method of claim 1, wherein said log records are only appended to one end of the transaction log.

5. The method of claim 1, wherein said substep (i) includes:

acquiring exclusive access to the transaction log prior to flushing the private log cache to the transaction log; and

relinquishing exclusive access to the transaction log after flushing the private log cache to the transaction log.

6. The method of claim 5, wherein said step of acquiring exclusive access includes acquiring a log semaphore which grants exclusive access for the transaction log to a single task at a time.

7. The method of claim 1, wherein said transaction log is sequentially arranged so that changes which occur in the system are represented in the transaction log in the sequence in which they occur.

8. The method of claim 1, wherein at least one change comprises a modification to a data page stored in memory, and wherein the method further comprises:

after flushing the transaction log to disk, flushing said data page to disk.

9. The method of claim 1, wherein a plurality of transactions are executing in the system at a given point in time, and wherein a plurality of private log caches are created, one for each transaction currently executing in the system.

10. In a database system having a transaction log which is shared among multiple transactions, a method for logging transactions which occur in the system in a manner which reduces contention for the transaction log, the method comprising:

allocating in memory a private log cache for each transaction so that log records for a given transaction can first be stored separately from log records of other transactions before posting to the transaction log, each private log cache restricted to storing multiple log records for a single transaction;

receiving commands specifying a particular transaction to be executed by the system;

in response to receipt of said commands, creating in the private log cache for the particular transaction particular log records describing the particular transaction;

prior to committing any changes specified by the particular transaction, posting the particular log records to the transaction log by copying the particular log records from the private log cache for the particular transaction to the transaction log;

if the particular log records can be successfully posted to the transaction log and the transaction log can be flushed to disk, completing execution of the particular transaction by committing any changes specified by the particular transaction;

flushing the transaction log to disk before said particular data page is flushed to disk;

wherein the particular transaction specifies a change which affects a particular data page, and wherein said posting step comprises posting to the transaction log any log records having log information representing the change which affects said particular data page, said posting step being performed before said particular data page is flushed to disk; and

wherein each data page is associated with a log pin which points to a place in the transaction log where the transaction log must be flushed to disk before the data page itself is written to disk.

11. The method of claim 10, wherein access to the transaction log is controlled through use of a log semaphore, and wherein said posting step includes:

acquiring the log semaphore so that the particular log records can be posted with exclusive access to the transaction log.

12. The method of claim 10, wherein the particular log records are posted to the transaction log when the private log cache fills to capacity.

13. The method of claim 10, wherein the particular log records are posted to the transaction log in response to the system receiving a command to commit all changes specified by the particular transaction.

14. The method of claim 13, wherein said command to commit all changes Comprises an SQL “

COMMIT”

command.

15. The method of claim 10, further comprising:

attempting to flush the particular data page to disk; and

rolling back the transaction if the particular data page cannot be successfully flushed to disk.

16. The method of claim 15, wherein said rolling back step comprises:

using the particular log records for the transaction to undo any changes for the transaction, so that the system can be restored to its state that existed just prior to undertaking execution of the transaction.