DATABASE SYSTEM WITH IMPROVED METHODS FOR ASYNCHRONOUS LOGGING OF TRANSACTIONS

• US 20040010499A1
• Filed: 07/02/2002
• Published: 01/15/2004
• Est. Priority Date: 07/02/2002
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Bhatt, Vadiraja, Vaitheeswaran, Girish, Ghosh, Prasanta
• Granted Patent: US 6,721,765 B2
• Time in Patent Office: Days
• US Class Current: 707/100
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A database system providing improved methods for asynchronous logging of transactions is described. Log records are created describing changes to a database made by a transaction. When a command committing changes to the database for the transaction is received, a logging request is placed in a queue. An asynchronous logging service removes requests from the queue and transfers log records from the transaction to a shared cache. The shared cache stores log records before they are written to the transaction log. The logging service writes log pages containing log records for the transaction from the cache to the transaction log. After all log pages in the cache for the transaction have been written to the transaction log, changes to the database made by the transaction can be committed to the database.

Claims

1. In a database system having a transaction log which is shared among multiple transactions, a method for storing log records describing changes occurring in a database in a manner that reduces contention among transactions for said transaction log, the method comprising:

creating log records describing changes to said database for a given transaction;

upon receiving for said given transaction a command which commits changes to said database, placing a request for storing log records of said given transaction in a request queue;

removing said request of said given transaction from said request queue and transferring said log records from said given transaction to a shared cache, said shared cache for storing log records from multiple transactions before said log records are written to said transaction log;

writing said log records from said shared cache to said transaction log; and

committing any changes to said database made by said given transaction to said database after all log records for said given transaction have been written to said transaction log.

2. The method of claim 1, wherein said step of placing a request in a request queue includes associating said request with an identifier which enables identification of log records of said given transaction.

3. The method of claim 1, wherein said step of removing requests from said request queue includes removing requests in sequence based upon receipt of requests into said request queue.

4. The method of claim 1, wherein said step of removing requests from said request queue includes removing requests in sequence based upon a time each said request is placed in said request queue.

5. The method of claim 1, wherein said step of transferring log records from said given transaction to said shared cache includes using an independently executing thread for transferring said log records to said shared cache.

6. The method of claim 5, wherein said independently executing thread monitors said request queue and handles each request by transferring log records from said given transaction placing said request to said shared cache.

7. The method of claim 1, further comprising:

allocating in memory a private log cache for each given transaction, said private log cache for storing log records for a given transaction separately from log records of other transactions before transferring said log records to said shared cache.

8. The method of claim 1, wherein access to said shared cache is controlled through use of a log semaphore and said step of transferring log records from said given transaction to said shared cache includes controlling the log semaphore to obtain exclusive access to said shared cache.

9. The method of claim 1, wherein said step of writing said log records from said shared cache to said transaction log includes writing log pages containing said log records in sequence, said sequence based upon the sequence of log pages in said shared cache.

10. The method of claim 9, wherein said step of writing said log pages from said shared cache to said transaction log in sequence includes the substeps of:

assigning a sequence number to each log page in said shared cache; and

writing log pages from said shared cache to said transaction log based upon said sequence number of each said log page.

11. The method of claim 1, wherein said step of writing said log records from said shared cache to said transaction log includes using an independently executing thread for writing said log records to said transaction log.

12. The method of claim 1, wherein said step of writing said log records from said shared cache to said transaction log includes tracking log pages previously written to said transaction log to avoid traversing the same log page more than once.

13. The method of claim 1, wherein said step of writing said log records from said shared cache to said transaction log includes only writing log pages that are full, thereby avoiding writing log pages more than once.

14. The method of claim 1, wherein said step committing changes to said database from said given transaction includes the substeps of:

determining a particular log page containing a log record of said command to commit changes to said database for said given transaction; and

committing changes to said database made by said given transaction only after said particular log page is successfully written to said transaction log.

15. The method of claim 1, further comprising:

allocating log pages to said shared cache independently of transactions.

16. The method of claim 15, wherein said step of allocating log pages to said shared cache includes using an independently executing thread for allocating log pages to said shared cache.

17. The method of claim 1, further comprising:

allocating log pages to said shared cache when unused log pages in said shared cache are less than a pre-selected threshold.

18. The method of claim 17, wherein said step of allocating log pages to said shared cache includes using an independently executing thread for allocating log pages to said shared cache.

19. The method of claim 1, wherein said step of writing said log records from said shared cache to said transaction log includes monitoring said shared cache and writing log pages to said transaction log whenever log pages not yet written to said transaction log are found in said shared cache.

20. A computer-readable medium having computer-executable instructions for performing the method of claim 1.

21. A downloadable set of computer-executable instructions for performing the method of claim 1.

22. In a database system having a transaction log for writing log records of changes to a database from a plurality of transactions, a system for writing log records to said transaction log that reduces contention among said plurality of transactions, the system comprising:

a private log cache for each transaction, said private log cache consisting of an area in memory set aside for storing log records which have been created to describe changes to said database for a given transaction but which have not yet been posted to said transaction log separately from log records of other transactions, so that each transaction is associated with its own private log cache;

a flusher service that receives requests to write log records from said plurality of transactions and writes log records for a given transaction from a private log cache of said given transaction to a shared buffer, said shared buffer for storing log records from multiple transactions before said log records are written to said transaction log; and

a log writer service that writes log records from said shared buffer to said transaction log.

23. The system of claim 22, wherein said flusher service is an independently executing thread.

24. The system of claim 22, wherein said log writer service is an independently executing thread.

25. The system of claim 22, wherein said flusher service includes a queue for receiving requests to write log records from said plurality of transactions.

26. The system of claim 25, wherein said flusher service writes log records for transactions in sequence, said sequence based upon receipt of requests in said queue.

27. The system of claim 25, wherein said flusher service monitors said queue and removes requests from said queue in sequence to avoid contention for said shared buffer among said plurality of transactions.

28. The system of claim 22, wherein said flusher service receives a request to write log records when a command to commit changes to said database is made by a given transaction.

29. The system of claim 22, further comprising:

a log allocator service which monitors said shared buffer and allocates log pages to said shared buffer when necessary.

30. The system of claim 29, wherein said log allocator service pre-allocates log pages to said shared buffer.

31. The system of claim 29, wherein said log allocator service is an independently executing thread for allocating log pages to said shared buffer.

32. The system of claim 29, wherein said log allocator service allocates additional log pages to said shared buffer when unused log pages in said shared buffer are less than a pre-selected threshold.

33. The system of claim 22, wherein said flusher service retrieves log records from a private log cache of a given transaction by using a handle included with said request to write log records from said given transaction.

34. The system of claim 22, wherein said log writer service writes log pages from said shared buffer to said transaction log in sequence, said sequence based upon the sequence of log pages in said shared buffer.

35. The system of claim 22, wherein said log writer service assigns a sequence number to each log page in said shared buffer and writes log pages from said shared buffer to said transaction log based upon said sequence number of each said log page.

36. The system of claim 22, wherein said log writer service commits any changes to said database made by said given transaction to said database only after all log records in said shared buffer for said given transaction have been written to said transaction log.

37. The system of claim 22, wherein writing said log records to said transaction log includes writing said log records to disk.

38. In a database system having a transaction log which is shared among a plurality of transactions for writing log records of changes to at least one database, a method for said plurality of transactions to write log records to said transaction log, the method comprising:

allocating in memory a private log cache for each transaction, said private log cache for storing log records for a given transaction separately from log records of other transactions before said log records are written to said transaction log;

creating log records describing changes to at least one database made by a given transaction and storing said log records in a private log cache for said given transaction;

upon receiving for said given transaction a command which commits changes to said at least one database, moving said log records from said private log cache to a log buffer, said log buffer for storing log records from multiple transactions before said log records are written to said transaction log;

writing said log records from said log buffer to said transaction log; and

committing changes to said at least one database made by said given transaction after all log records for said given transaction have been written to said transaction log.

39. The method of claim 38, wherein said step of moving said log records from said private log cache to said log buffer includes the substeps of:

receiving a request for performing logging operations in a request queue, said request for logging operations including an identifier which enables identification of said private log cache of said given transaction;

removing requests from said request queue; and

using said identifier to move log records from said private log cache to said log buffer.

40. The method of claim 38, wherein said substep of removing requests from said request queue includes removing requests in sequence, said sequence based upon receipt of requests into said request queue.

41. The method of claim 38, wherein said step of moving log records from said private log cache to said log buffer includes using an independently executing thread for moving said log records to said log buffer.

42. The method of claim 41, wherein said independently executing thread monitors said request queue and handles each given request by moving log records from said private log cache of said given transaction to said log buffer.

43. The method of claim 38, wherein access to said log buffer cache is controlled through use of a log semaphore and said step of moving log records from said private log cache to said log buffer includes controlling said log semaphore to obtain exclusive access to said log buffer.

44. The method of claim 38, wherein said step of writing said log records from said log buffer to said transaction log includes writing log pages containing said log records in sequence, said sequence based upon the sequence of log pages in said log buffer.

45. The method of claim 38, wherein said step of writing said log pages from said log buffer to said transaction log includes the substeps of:

assigning a sequence number to each log page in said log buffer; and

writing log pages from said log buffer to said transaction log based upon said sequence number of each said log page.

46. The method of claim 38, wherein said step of writing said log records from said log buffer to said transaction log includes using an independently executing thread for writing said log records to said transaction log.

47. The method of claim 38, wherein said step of writing said log records from said log buffer to said transaction log includes tracking log pages in said log buffer written to said transaction log to avoid traversing the same log page more than once.

48. The method of claim 38, wherein said step of writing said log records from said log buffer to said transaction log includes only writing log pages that are full, thereby avoiding writing log pages more than once.

49. The method of claim 38, wherein said step committing changes to said at least one database made by said given transaction includes the substeps of:

determining a particular log page in said log buffer containing a log record of a command to commit changes to said at least one database made by said given transaction; and

committing changes to said at least one database made by said given transaction only after said particular log page is successfully written to said transaction log.

50. The method of claim 38, further comprising:

allocating log pages to said log buffer in advance of receiving commands which commit changes to said at least one database.

51. The method of claim 50, wherein said step of allocating log pages to said log buffer includes using an independently executing thread for allocating log pages to said log buffer.

52. The method of claim 38, further comprising:

allocating additional log pages to said log buffer when unused log pages in said log buffer are less than a pre-selected threshold.

53. The method of claim 38, wherein said step of writing said log records from said log buffer to said transaction log includes automatically writing log pages to said transaction log whenever log pages not yet written to said transaction log are found in said log buffer.

54. The method of claim 38, wherein said step of writing said log records from said log buffer to said transaction log includes writing said log records to disk.