Transactional table truncation for concurrent transactions

Patent metadata

Field	Value
Publication number	US-10204130-B2
Application number	US-201615078433-A
Country	US
Kind code	B2
Filing date	Mar 23, 2016
Priority date	Mar 23, 2016
Publication date	Feb 12, 2019
Grant date	Feb 12, 2019

Abstract

A method, a processing device, and a computer program product are provided. A truncate table command for truncating all or a subset of one or more rows of a database table is received from a first transaction executing on a processing device. At least one read operation on the truncated database table is performed for at least one transaction concurrent with the first transaction, wherein the truncated rows are visible to the at least one concurrent transaction and invisible to subsequent transactions. Storage units that store the truncated rows are reclaimed in response to completion of the at least one concurrent transaction.

First claim

We claim as our invention:

1. A processing device comprising:
at least one processor;
a memory; and
a communication bus connecting the at least one processor with the memory, wherein the memory has stored therein instructions, which when executed by the at least one processor cause the processing device to perform a method comprising:
receiving, from a first transaction, a truncate table command to truncate all rows or a subset of the rows of a database table;
in response to the receiving the truncate table command, truncating the all rows or the subset of the rows by marking the all rows or the subset of the rows of the database table as being deleted such that truncated rows appear truncated to transactions that started after the truncating and the truncated rows appear as not truncated to transactions that started before the truncating;
performing at least one read operation on the truncated database table for at least one transaction concurrent with the first transaction; and
reclaiming storage units storing the truncated rows when the truncated rows appear truncated to all current transactions.

2. The processing device of claim 1 , wherein the truncate table command is for truncating all rows of the database table.

3. The processing device of claim 1 , wherein the method further comprises:
locking the database table to prevent the at least one concurrent transaction from modifying the one or more rows of the database table.

4. The processing device of claim 1 , wherein the method further comprises:
storing an identifier for each data storage unit in a table indicating visibility of rows in respective data storage units to transactions.

5. The processing device of claim 4 , wherein the method further comprises:
copying one or more remaining rows to new data storage units;
storing identifiers for existing data storage units in a table and for the one or more new data storage units to indicate visibility of the one or more truncated rows and the one or more remaining rows, wherein the one or more truncated rows is visible to concurrent transactions and the one or more remaining rows are visible to subsequent transactions; and
reclaiming data storage units storing the one or more truncated rows in response to completion of the concurrent transactions.

6. The processing device of claim 1 , wherein the method further comprises:
precluding concurrent updates and deletes that affect rows currently in the database table; and
permitting concurrent inserts of rows into the database table only when the rows to insert are to be inserted into newly assigned data storage units.

7. The processing device of claim 6 , wherein the method further comprises:
permitting concurrent updates of the rows and concurrent deletes of the rows only when the updates of the rows are in the newly assigned data storage units.

8. A computer program product comprising:
a computer readable storage medium having computer readable program code embodied therewith for execution on a processing system, the computer readable program code being configured to be executed by the processing system to:
receive, from a first transaction executing on a processing device, a truncate table command to truncate all rows or a subset of the rows of a database table;
in response to the receiving the truncate table command, truncate the all rows or the subset of the rows by marking the all rows or the subset of the rows of the database table as being deleted such that truncated rows appear truncated to transactions that started after the truncating and the truncated rows appear as not truncated to transactions that started before the truncating;
perform at least one read operation on the truncated database table for at least one transaction concurrent with the first transaction; and
reclaim storage units storing the truncated rows when the truncated rows appear truncated to all current transactions.

9. The computer program product of claim 8 , wherein the truncate table command is for truncating all rows of the database table.

10. The computer program product of claim 8 , wherein the computer readable program code is further configured to be executed by the processing system to:
lock the database table to prevent the at least one concurrent transaction from modifying the one or more rows of the database table.

11. The computer program product of claim 8 , wherein the computer readable program code is further configured to be executed by the processing system to:
store an identifier for each data storage unit in a table indicating visibility of rows in respective data storage units to transactions.

12. The computer program product of claim 11 , wherein the computer readable program code is further configured to be executed by the processing system to:
copy one or more remaining rows to new data storage units;
store identifiers for existing data storage units in a table and for one or more new data storage units to indicate visibility of one or more truncated rows and the one or more remaining rows, wherein the one or more truncated rows is visible to concurrent transactions and the one or more remaining rows are visible to subsequent transactions; and
reclaim storage units storing the one or more truncated rows in response to completion of the concurrent transactions.

13. The computer program product of claim 8 , wherein the computer readable program code is further configured to be executed by the processing system to:
preclude concurrent updates and deletes that affect rows currently in the database table;
permit concurrent inserts of rows into the database table only when the rows to insert are to be inserted into newly assigned data storage units; and
permit concurrent updates of rows and concurrent deletes of the rows only when the rows are in the newly assigned data storage units.

Assignees

• IBM

Inventors

• Mohandas Praveen
• Sarin Sunil K
• Stolze Knut

Classifications

• G06F16/2308

  Concurrency control (transaction processing G06F9/466) · CPC title

• G06F16/2365 Primary

  Ensuring data consistency and integrity · CPC title

• G06F16/2358

  Change logging, detection, and notification (replication G06F16/27) · CPC title

• G06F16/284

  Relational databases · CPC title

• G06F16/2379

  Updates performed during online database operations; commit processing · CPC title