CONCURRENCY CONTROL FOR EXTRACTION, TRANSFORM, LOAD PROCESSES

• US 20110213756A1
• Filed: 03/01/2010
• Published: 09/01/2011
• Est. Priority Date: 03/01/2010
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Wang, Rui, Chen, Ying, He, Bin
• Granted Patent: US 8,655,859 B2
• Time in Patent Office: Days
• US Class Current: 707/684
• 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;

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.

2. The method as claimed in claim 1 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.

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 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 communicatively coupled to a computer having a current ETL process, the database comprising:

data accessible to the current ETL process;

at least one database table including the data accessible to the ETL process; and

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

12. The database as claimed in claim 11 wherein the 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 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 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 current ETL process,

wherein the current ETL process is configured to clean up the at least one database table in response to a failure of the prior ATL 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 instructions for causing the computer to implement a method, the method comprising:

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.

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.