Method and apparatus for providing concurrent data insertion and updating

• US 8,898,126 B1
• Filed: 04/04/2007
• Issued: 11/25/2014
• Est. Priority Date: 04/04/2007
• Status: Active Grant

• Current Assignee: Emc IP Holding Company LLC (Dell Technologies Inc.)
• Original Assignee: EMC Corporation (Dell Technologies Inc.)
• Inventors: Dai, Qiang
• Primary Examiner(s): Saeed, Usmaan
• Assistant Examiner(s): CHOI, MICHELE C
• Application Number:
  US11/732,664
• Time in Patent Office: 2,792 Days
• Field of Search: None
• US Class Current: 707/703
• CPC Class Codes: G06F 16/2308 Concurrency control transaction processing G06F9/466

Abstract

A method, computer program product and apparatus for providing a scheduling mechanism for inserting/updating data items in a database including obtaining each of the at least one keys included within a selected data item, determining whether each of the at least one keys is suitable for processing and storing each of the at least one keys in a first queue when each of the keys is available for processing, otherwise storing the keys in a second queue.

Claims

1. A computer-implemented method for updating and inserting data items into a database, wherein a processor executes the method comprising:

fetching data items, each of the data items comprising a key and information, wherein each key comprises a primary key for the respective data item;

obtaining each key of the keys of the data items;

determining whether each key of the keys is in a delay queue or is in processing, wherein each key in the delay queue is not available for updating in the database and is not available for committing in the database;

determining, based on a determination that at least one key of the keys is not in the delay queue and is not in processing, if a process queue exists and, based on a negative determination that the process queue exists, creating the process queue;

determining, based on a determination that at least one key of the keys is in the delay queue or is in processing, if the delay queue exists and, based on a negative determination that the delay queue exists, creating the delay queue;

for each key of the keys, storing the key of the keys in the process queue when the key of the keys is available for updating in the database or is available for committing in the database, otherwise storing the key of the keys in the delay queue;

scheduling an insert/update operation for the database including data items associated with keys in the process queue;

executing a batch commit to information of the data items associated with the keys in the process queue to the database; and

removing the keys from the process queue associated with the information committed to the database.

2. The method as recited in claim 1, further comprising:

scheduling an insert/update operation including data items associated with the keys in said delay queue prior to data items in said process queue;

executing a batch commit to information of the data items associated with the keys in the delay queue to the database; and

removing the keys from the delay queue associated with the information committed to the database.

3. The method as recited in claim 2, further comprising executing a second thread to perform the batch commit to the information of the data items associated with the keys in the delay queue.

4. The method as recited in claim 1, further comprising executing a thread to perform the batch commit to the information of the data items associated with the keys in the process queue.

5. The method as recited in claim 4, further comprising getting a database connection.

6. The method as recited in claim 5, further comprising returning the database connection back to a connection pool.

7. The computer implemented method of claim 1, wherein a key is not available for updating in the database and is not available for committing in the database if information corresponding to the key needs to be updated in the database or committed in the database sequentially after information associated with another key which has not been updated in the database and has not been committed in the database to ensure correct information is committed.

8. The computer implemented method of claim 1, further comprising:

determining if the delay queue is ready to execute; and

scheduling the delay queue to be processed.

9. The computer implemented method of claim 1, wherein a key is not available for updating in the database and is not available for committing in the database when information corresponding to the key is not available for updating in the database and is not available for committing in the database.

10. An apparatus for updating and inserting data items into a database, the apparatus comprising:

a processor in communication with a memory, the processor executing logical code causing the processor to perform;

fetching data items, each of the data items comprising a key and information, wherein each key comprises a primary key for the respective data item;

obtaining each key of the keys of the data items;

determining whether each key of the keys is in a delay queue or is in processing, wherein each key in the delay queue is not available for updating in the database and is not available for committing in the database;

determining, based on a determination that at least one key of the keys is not in the delay queue and is not in processing, if a process queue exists and, based on a negative determination that the process queue exists, creating the process queue;

determining, based on a determination that at least one key of the keys is in the delay queue or is in processing, if the delay queue exists and, based on a negative determination that the delay queue exists, creating the delay queue;

for each key of the keys, storing the key of the keys in the process queue when the key of the keys is available for updating in the database or is available for committing in the database, otherwise storing the key of the keys in the delay queue;

scheduling an insert/update operation for the database including data items associated with keys in the process queue;

executing a batch commit to information of the data items associated with the keys in the process queue to the database; and

removing the keys from the process queue associated with the information committed to the database.

11. The apparatus as recited in claim 10, the processor further executing logical code causing the processor to perform:

scheduling an insert/update operation including data items associated with the keys in said delay queue prior to data items in said process queue;

executing a batch commit to information of the data items associated with the keys in the delay queue to the database; and

removing the keys from the delay queue associated with the information committed to the database.

12. The apparatus as recited in claim 11, further comprising executing a second thread to perform the batch commit to the information of the data items associated with the keys in the delay queue.

13. The apparatus as recited in claim 10, further comprising executing a thread to perform the batch commit to the information of the data items associated with the keys in the process queue.

14. The apparatus as recited in claim 13, further comprising getting a database connection.

15. The apparatus as recited in claim 14, further comprising returning the database connection back to a connection pool.

16. A computer program product, stored on a non-transitory computer readable storage medium, for updates and inserts of data items into a database, the product providing logical code causing a processor to execute:

fetching data items, each of the data items comprising a key and information, wherein each key comprises a primary key for the respective data item;

obtaining each key of the keys of the data items;

determining whether each key of the keys is in a delay queue or is in processing, wherein each key in the delay queue is not available for updating in the database and is not available for committing in the database;

determining, based on a determination that at least one key of the keys is not in the delay queue and is not in processing, if a process queue exists and, based on a negative determination that the process queue exists, creating the process queue;

determining, based on a determination that at least one key of the keys is in the delay queue or is in processing, if the delay queue exists and, based on a negative determination that the delay queue exists, creating the delay queue;

for each key of the keys, storing the key of the keys in the process queue when the key of the keys is available for updating in the database or is available committing in the database, otherwise storing the key of the keys in the delay queue;

scheduling an insert/update operation for the database including data items associated with keys in the process queue;

executing a batch commit to information of the data items associated with the keys in the process queue to the database; and

removing the keys from the process queue associated with the information committed to the database.

17. The product as recited in claim 16, providing further code to the processor for causing the processor to execute:

scheduling an insert/update operation including data items associated with the keys in said delay queue prior to data items in said process queue;

executing a batch commit to information of the data items associated with the keys in the delay queue to the database; and

removing the keys from the delay queue associated with the information committed to the database.

18. The product as recited in claim 17, further comprising executing a second thread to perform the batch commit to the information of the data items associated with the keys in the delay queue.

19. The product as recited in claim 16, further comprising executing a thread to perform the batch commit to the information of the data items associated with the keys in the process queue.

20. The product as recited in claim 19, further comprising getting a database connection.

21. The product as recited in claim 20, further comprising returning the database connection back to a connection pool.