Concurrency control for extraction, transform, load processes

• US 8,655,859 B2
• Filed: 03/01/2010
• Issued: 02/18/2014
• Est. Priority Date: 03/01/2010
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Chen, Ying, He, Bin, Wang, Rui
• Primary Examiner(s): Lu, Charles
• Application Number:
  US12/714,929
• Publication Number:
  US 20110213756A1
• Time in Patent Office: 1,450 Days
• Field of Search: 707/684, 707/999.008
• US Class Current: 707/704
• CPC Class Codes: G06F 16/2336 Pessimistic concurrency control approaches, e.g. locking or multiple versions without time stamps
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

System and methods manage concurrent ETL processes accessing a database. Exemplary embodiments include a method for concurrency management for ETL processes in a database having database tables and communicatively coupled to a computer, the method including establishing a session lock for the database, determining that a current ETL process is accessing the database at a current time, associating a current expiration time with the session lock, the expiration time being stored in a lock table in the database, sending the session lock to the current ETL process and performing ETL-level locking for the current ETL process.

Claims

1. A method for concurrency management for extraction, transform, load (ETL) processes in a database having database tables and communicatively coupled to a computer, the method comprising:

establishing a session lock for the database having a plurality of tables;

determining that a first current ETL process is accessing the database at a current time;

determining that a second current ETL process is accessing the database at the current time;

responsive to the first and second current ETL processes reading from a first subset of the plurality of tables, the first current ETL process writing into a second subset of the plurality of tables, the second current ETL process writing into a third subset of the plurality of tables, the first, second and third subsets being mutually exclusive, executing the first and second ETL processes simultaneously;

associating a current expiration time with the session lock, the expiration time being stored in a lock table in the database;

sending the session lock to at least one of the first and second current ETL processes; and

responsive to the first and second ETL processes writing into a common subset of the plurality of tables, performing ETL-level locking for the first current ETL process.

2. The method as claimed in claim 1 wherein performing ETL-level locking for the first current ETL process, comprises:

determining whether a prior expiration time set in the lock table occurs before the current time; and

in response to a determination that the prior expiration time set in the lock table occurs before the current time, setting the lock table to the current expiration time.

3. The method as claimed in claim 2 further comprising:

in response to a determination that the first expiration time set in the lock table does not occur before the current time, waiting for a predetermined time period.

4. The method as claimed in claim 2 further comprising:

releasing the session lock.

5. The method as claimed in claim 4 wherein releasing the session lock comprises setting the current expiration time to the current time.

6. The method as claimed in claim 2 wherein the ETL-level locking is database-level exclusive locking.

7. The method as claimed in claim 2 wherein the ETL-level locking is table-level exclusive locking.

8. The method as claimed in claim 2 wherein the ETL-level locking is table-level read-write locking.

9. The method as claimed in claim 1 wherein the database includes a rollback snapshot table.

10. The method as claimed in claim 9 further comprising:

in response to sending the session lock, checking the rollback snapshot table to determine which database tables the first current ETL process touches;

determining if there has been a prior ETL process failure in the database that has affected the database tables that the current ETL process touches;

in response to a determination that there has been a prior ETL process failure in the database, cleaning the database tables that have been affected by the prior ETL process failure;

performing ETL loading; and

releasing the session lock.

11. A database residing in a memory that is communicatively coupled to a computer having a first current ETL process and a second current ETL process, the database comprising:

data accessible to the first current ETL process and the second current ETL process;

a plurality of tables;

at least one database table of the plurality of tables, including the data accessible to the first current ETL process; and

a lock table including an expiration time value for the second current ETL process, the second current ETL process accessing data accessible to the first current ETL process in the at least one database table,

wherein responsive to the first and second current ETL processes reading from a first subset of the plurality of tables, the first current ETL process writing into a second subset of the plurality of tables, the second current ETL process writing into a third subset of the plurality of tables, the first, second and third subsets being mutually exclusive, executing the first and second ETL processes simultaneously,

wherein responsive to the first and second ETL processes writing into a common subset of the plurality of tables, performing ETL-level locking for the first current ETL process.

12. The database as claimed in claim 11 wherein the first current ETL process is configured to check the lock table to determine if the database is locked to access by the at least one ETL process on a database level.

13. The database as claimed in claim 11 wherein the lock table further includes a table_name value.

14. The database as claimed in claim 13 wherein the first current ETL process is configured to check the lock table to determine if the database is locked to access by the at least one ETL process on a table level.

15. The database as claimed in claim 11 wherein the lock table further includes a table_name value, a lock_type value and an ID value.

16. The database as claimed in claim 15 wherein the first current ETL process is configured to check the lock table to determine if the database is locked to access by the at least one ETL process on a read-write level.

17. The database as claimed in claim 11 further comprising:

a rollback snapshot table configured to record and store a snapshot of the data accessible to the first current ETL process,

wherein the first current ETL process is configured to clean up the at least one database table in response to a failure of the prior ETL process.

18. A computer program product for concurrency management for extraction, transform, load (ETL) processes in a database having database tables and communicatively coupled to a computer, the computer program product including a non-transitory computer readable medium having instructions for causing the computer to implement a method, the method comprising:

establishing a session lock for the database having a plurality of tables;

determining that a first current ETL process is accessing the database at a current time;

determining that a second current ETL process is accessing the database at the current time;

responsive to the first and second current ETL processes reading from a first subset of the plurality of tables, the first current ETL process writing into a second subset of the plurality of tables, the second current ETL process writing into a third subset of the plurality of tables, the first, second and third subsets being mutually exclusive, executing the first and second ETL processes simultaneously;

associating a current expiration time with the session lock, the expiration time being stored in a lock table in the database;

sending the session lock to at least one of the first and second current ETL processes; and

responsive to the first and second ETL processes writing into a common subset of the plurality of tables, performing ETL-level locking for the current ETL process.

19. The computer program product as claimed in claim 18 wherein performing ETL-level locking for the first ETL process, comprises:

determining whether a prior expiration time set in the lock table occurs before the current time; and

in response to a determination that the prior expiration time set in the lock table occurs before the current time, setting the lock table to the current expiration time,

wherein the ETL-level locking is at least one of database-level exclusive locking, table-level exclusive locking and table-level read-write locking.

20. The computer program product as claimed in claim 18 wherein the database includes a rollback snapshot table configured to record and store a snapshot of data stored in the database.