Database access with multilevel lock

• US 7,574,438 B2
• Filed: 12/03/2003
• Issued: 08/11/2009
• Est. Priority Date: 12/03/2003
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Cina, Miroslav
• Primary Examiner(s): Truong; Cam Y T
• Application Number:
  US10/727,183
• Publication Number:
  US 20050125406A1
• Time in Patent Office: 2,078 Days
• Field of Search: 707/8, 707/102, 707/100
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2343 Locking methods, e.g. distributed locking or locking implementation details
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

A multi-level lock procedure is used in scheduling access of a table in a database by multiple processes running in parallel. A lock level is associated with a particular process, a higher lock level representing a larger number of other processes having priority over the particular process in accessing the table. Repeatedly attempt to associate the particular process with a lower lock level, and if the particular process has been successfully associated with the lower lock level, release a previous lock level associated with the particular process so that the previous lock level may be associated with other processes. Allow the particular process to access the table when the lock level for the particular process is equal to a preset value.

Claims

1. A method of scheduling access to a database by multiple processes, comprising:

in multiple instances of a software-implemented procedure, each instance of the software-implemented procedure associated with a particular process of the multiple processes, associating a lock level with the particular process and attempting to associate a different lock level with the particular process until the particular process is granted access to the database, a higher lock level representing a larger number of other processes having priority over the particular process in accessing the database, each process of the multiple processes being associated with no more than one lock level;

repeatedly calling, using the particular process, said each instance of the software-implemented procedure to attempt to associate the particular process with successively lower lock levels until the lock level is equal to a preset value, and each time the particular process has been successfully associated with a lower lock level, releasing a previous lock level associated with the particular process so that the previous lock level is available to be associated with the other processes, the preset value equals to one;

allowing, using the software-implemented procedure, the particular process to access the database when the lock level for the particular process is equal to the preset value,

at a computer, storing, in a queue, data indicating which process is associated with which lock level, the computer including a processor;

updating, using the software-implemented procedure, the data indicating which process is associated with which lock level;

reassigning lock levels of the multiple processes when a process accessing a record terminates its access to the record, wherein the process that attempted to access the record earlier than other process is assigned a lower lock level than the other process, and said each process other than the process terminating its access to the record is assigned a lower lock level when the process terminates its access to the record, wherein the particular process changes two or more lock levels from an initially assigned lock level to the lock level having the preset value before the particular process is allowed to access the record, said each process of the multiple processes attempts to associate itself with a lower lock level independently of the other processes, and at least two processes of the multiple processes are being run in a parallel processing environment; and

allowing the multiple processes to read the record but not modify the record when the lock levels for the multiple processes are different from a preset value.

2. The method of claim 1 further comprising:

locking the record in the database at multiple levels when the multiple processes running in parallel attempt to access the record, said each process of the multiple processes associated with one of the instances of the procedure; and

selectively permitting one process of the multiple processes to access the record at a time.

3. A method of scheduling access to a database by multiple processes comprising:

in multiple instances of a software-implemented procedure,

upon receiving a request from a first process to access a record in the database, associating a first lock level with the first process and allowing the first process to access the record, preventing other processes from modifying the record until the first process finishes accessing the record, a higher lock level representing a larger number of other processes having priority over the first process in accessing the database, each process of the multiple processes being associated with no more than one lock level;

at a computer, upon receiving a request from a second process to access the record while the first process is still accessing the record, associating a second lock level with the second process, the computer including a processor;

upon receiving a request from a third process to access the record while the first process is still accessing the record, associating a third lock level with the third process;

when the first process finishes accessing the record, releasing the first lock level, the second and third processes each repeatedly calling one instance of the instances of the procedure respectively associated with the second process or the third process to attempt to associate itself with a lower lock level until the lower lock level is equal to a preset value, the preset value equals to one, wherein if the second process associates itself with the first lock level before the third process associates itself with the first lock level, the third process associates itself with the second lock level and repeatedly attempts to associate itself with the first lock level;

when the second process associates itself with the first lock level, permitting the second process to modify the record;

when the third process associates itself with the first lock level, permitting the third process to modify the record;

updating, using the instances of the software-implemented procedure, data indicating which process is associated with which lock level;

storing, in a queue, the data indicating which process is associated with which lock level;

reassigning lock levels of the multiple processes when a process accessing the record terminates its access to the record, wherein the process that attempted to access the record earlier than other process is assigned a lower lock level than the other process, and said each process of the multiple processes other than the process terminating its access to the record is assigned a lower lock level when the process terminates its access to the record, wherein said each process of the multiple processes changes two or more lock levels from an initially assigned lock level to a lock level having the preset value before said each process of the multiple processes is allowed to access the record;

allowing the multiple processes to read the record but not modify the record when the lock levels for the multiple processes are different from a preset value; and

wherein said each process of the multiple processes attempts to associate itself with a lower lock level independently of other processes, and at least two processes of the multiple processes are being run in a parallel processing environment.

4. A system of scheduling access to a database, the system comprising:

a memory;

a database to store records;

a queue to store information relating to lock levels of processes that attempt to access the records, each different process having a different lock level when attempting to access a same record, a process of the processes having a particular lock level, the process having the particular lock level being allowed to access the same record, a higher lock level representing a larger number of other processes having priority over a particular process in accessing the database, each process of the processes being associated with no more than one lock level; and

a processor to;

execute multiple instances of a procedure to assign a respective lock level to each process of different processes, each process of at least some of the different processes having a lock level other than the particular lock level, each instance of the procedure associated with said each process of the processes

repeatedly call an instance of the instances of the procedure associated with the particular process to attempt to associate the particular process with another lock level that is closer to the particular lock level until the another lock level is equal to a preset value, the preset value equals to one, the procedure allowing any one process to access the record when that one process has the particular lock level;

store, in a queue, data indicating which process is associated with which lock level;

update, using the procedure, the data indicating which process is associated with which lock level;

reassign lock levels of the processes when a process accessing the record terminates its access to the record, wherein the process that attempted to access the record earlier than other process is assigned a lower lock level than the other process, and said each process of the processes other than the process terminating its access to the record is assigned a lower lock level when the process terminates its access to the record, wherein the particular process changes two or more lock levels from an initially assigned lock level to a lock level having the preset value before the particular process is allowed to access the record, said each process of the processes attempts to associate itself with a lower lock level independently of other processes, and at least two processes of the processes are being run in a parallel processing environment; and

allow the processes to read the record but not modify the record when the lock levels for the processes are different from a preset value.