Database access with multilevel lock

• US 20050125406A1
• Filed: 12/03/2003
• Published: 06/09/2005
• Est. Priority Date: 12/03/2003
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Cina, Miroslav
• Granted Patent: US 7,574,438 B2
• Time in Patent Office: Days
• 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 of a table by multiple processes, comprising:

associating a lock level 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 attempting to associate the particular process with a lower lock level, and if the particular process has been successfully associated with the lower lock level, releasing a previous lock level associated with the particular process so that the previous lock level may be associated with other processes; and

allowing the particular process to access the table when the lock level for the particular process is equal to a preset value.

2. The method of claim 1 in which the preset value is equal to one.

3. The method of claim 1 in which each of the processes attempts to associate itself with a lower lock level independently of other processes.

4. The method of claim 1, further comprising storing in a queue information indicating which process is associated with which lock level.

5. The method of claim 1, further comprising calling multiple instances of a procedure that associates a lock level with a process, each instance of the procedure associated with one of the multiple processes and is configured to attempt to associate a different lock level with the process until the process is granted access to the record.

6. The method of claim 1, further comprising allowing processes to read the record but not modify the record when the lock levels for the processes are different from the preset value.

7. The method of claim 1, further comprising locking the record when the lock level having the preset value is associated with a process.

8. The method of claim 1 in which at least two of the processes are being run in a parallel processing environment.

9. A method comprising:

upon receiving a request from a first process to access a record in a database, associating a first lock level to the first process and allowing the first process to access the record, preventing other processes from accessing the record until the first process finishes accessing the record;

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 to the second process;

when the first process finishes accessing the record, releasing the first lock level and either

(a) releasing the second lock level from being associated with the second process and associating the first lock level with the second process, allowing the second process to access the record but preventing other processes from accessing the record until the second process finishes accessing the record, and when the second process finishes accessing the record, releasing the first lock level from being associated with the second process, or

(b) releasing a lock level from being associated with a third process and associating the first lock level with the third process, allowing the third process to access the record but preventing other processes from accessing the record until the third process finishes accessing the record, and when the third process finishes accessing the record, releasing the first lock level from being associated with the third process.

10. The method of claim 9 in which preventing other processes from accessing the record comprises allowing the other processes to read the record but not modify the record.

11. The method of claim 9, further comprising locking the record when the first lock level is associated with a process.

12. The method of claim 9, further comprising writing to a queue to specify which lock level is associated with which process.

13. The method of claim 9 in which at least two of the first, second, and third processes are being run in a parallel processing environment.

14. A method comprising:

locking a record in a database at multiple levels when multiple processes running in parallel attempt to access the record;

assigning a lock level to each of the multiple processes, different processes having different lock levels; and

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

15. The method of claim 14, further comprising reassigning the lock levels of the processes when a process accessing the record terminates its access to the record.

16. The method of claim 15 in which a process that attempted to access the record earlier than another process is assigned a lower lock level than the other process, and 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.

17. The method of claim 14, further comprising storing in a queue information indicating which process is associated with which lock level.

18. The method of claim 14, further comprising calling multiple instances of a procedure that assigns a lock level to a process, each instance of the procedure associated with one of the multiple processes and is configured to attempt to assign a different lock level to the process until the process is granted access to the record.

19. A system comprising:

a database to store records; and

a queue to store information relating to lock levels of processes that attempt to access the records, different processes having different lock levels when accessing the same record, one of the processes having a particular lock level being allowed to access the record.

20. The system of claim 19, further comprising a memory to store software code for implementing a procedure in which instances of the procedure are used to assign lock levels to the processes.

21. The system of claim 20 in which the software code is configured so that the instances of the procedure are run in parallel.

22. A computer program product, tangibly stored on a machine-readable medium, for implementing a multi-level lock process, comprising instructions operable to cause one or more programmable processors to:

lock a record in a database at a first level to allow a first process to modify the record but prevent other processes from modifying the record; and

lock the record at a second level to allow a second process to modify the record after the record is unlocked at the first level but to prevent other processes from modifying the record while the record is being modified by the second process.

23. The computer program product of claim 22 further comprising instructions operable to cause the one or more programmable processors to, after the record is locked at the second level, check whether the record is locked at the first level, and if the record is not locked at the first level, lock the record at the first level a second time and unlocking the record at the second level.

24. The computer program product of claim 23 further comprising instructions operable to cause the one or more programmable processors to write to a queue to specify that the record has been locked at the first level and/or the second level.

25. The computer program product of claim 22 further comprising instructions operable to cause the one or more programmable processors to run the first and second processes in parallel.

26. A computer program product, tangibly stored on a machine-readable medium, for implementing a multi-level lock process, comprising instructions operable to cause one or more programmable processors to:

upon receiving a request from a first process to access a record in a database, associate a first lock level to the first process and allow the first process to access the record but prevent other processes from accessing the record until the first process finishes accessing the record;

upon receiving a request from a second process to access the record while the first process is still accessing the record, associate a second lock level to the second process;

when the first process finishes accessing the record, release the first lock level from being associated with the first process, and either

(a) release the second lock level from being associated with the second process and associate the first lock level with the second process, allowing the second process to access the record but preventing other processes from accessing the record until the second process finishes accessing the record, and when the second process finishes accessing the record, releasing the first lock level from being associated with the second process, or

(b) release a lock level from being associated with a third process and associate the first lock level with the third process, allowing the third process to access the record but preventing other processes from accessing the record until the third process finishes accessing the record, and when the third process finishes accessing the record, releasing the first lock level from being associated with the third process.

27. The computer program product of claim 26 in which the instructions cause the one or more programmable processors to allow the other processes to read the record but not modify the record.

28. The computer program product of claim 26, further comprising instructions operable to cause the one or more programmable processors to lock the record when the first lock level is associated with a process.

29. The computer program product of claim 26, further comprising instructions operable to cause the one or more programmable processors to write to a queue to specify which lock level is associated with which process.

30. The computer program product of claim 26, in which the instructions cause the one or more programmable processors to execute at least two of the first, second, and third processes in a parallel processing environment.

31. A computer program product, tangibly stored on a machine-readable medium, for scheduling access of a table by multiple processes, comprising instructions operable to cause one or more programmable processors to:

associate a lock level 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; and

allow the particular process to access the table when the lock level for the particular process is equal to a preset value.

32. The computer program product of claim 31 in which the instructions are configured so that the preset value is equal to one.

33. The computer program product of claim 31 in which the instructions are configured so that each of the processes attempts to associate itself with a lower lock level independently of other processes.

34. A computer program product, tangibly stored on a machine-readable medium, for implementing a multi-level lock process, comprising instructions operable to cause one or more programmable processors to:

lock a record in a database at multiple levels when multiple processes running in parallel attempt to access the record;

assign a lock level to each process, different processes having different lock levels; and

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

35. The computer program product of claim 34 further comprising instructions operable to cause the one or more programmable processors to reassign the lock levels of the processes when a process accessing the record terminates its access to the record.

36. The computer program product of claim 34 in which the instructions are configured so that a process that attempted to access the record earlier than another process is assigned a lower lock level than the other process, and 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.

37. The computer program product of claim 34 further comprising instructions operable to cause the one or more programmable processors to store in a queue information indicating which process is associated with which lock level.

38. The computer program product of claim 34, further comprising instructions operable to cause the one or more programmable processors to call multiple instances of a procedure that assigns a lock level to a process, each instance of the procedure associated with one of the multiple processes and is configured to attempt to assign a different lock level to the process until the process is granted access to the record.