|
I'm using local temporary tables quite heavily for ETL tasks, say, to materialize data from remote tables or complex queries, and commonly I use them with NOT TRANSACTIONAL. As the late Breck had suggested in many articles and [his very helpful book](SQL Anywhere Studio 9 Developer's Guide, tables with that clause lead to improved performance as their contents does neither affect the transaction log nor the rollback log. However, up to now I had always assumed DML statements on these tables would still be atomar, i.e. either fully succeed or fail - just that the table contents is not affected by COMMIT and ROLLBACK commands. But I seem to be wrong: When I insert rows in such a table with 17.0.11.7236 or 17.0.11.7313, and a PK violation occurs, the inserted rows up unto that PK violation are still contained in the table. Here's a simple sample from the v17 demo database with table GroupO.Products: select list(ID, ', ' order by ID) from groupo.Products;
-- lists (in my case) 300, 301, 302, 400, 401, 500, 501, 600, 601, 700
-- create a local temporary table based on Products's schema
create local temporary table LT_Products (like GroupO.Products including primary key)
not transactional;
-- insert one of these rows
insert into LT_Products
select * from GroupO.Products
where ID = 500;
select list(ID, ', ' order by ID) from LT_Products;
-- row with ID 500 is contained in the local temporary table
-- insert all rows - leading to a PK violation for ID 500
-- with SQLCODE=-193 and error message
-- "Could not execute statement. Primary key for table 'LT_Products' is not unique: Primary key value ('500')"
insert into LT_Products
select * from GroupO.Products
order by ID;
select list(ID, ', ' order by ID) from LT_Products;
-- the local temporary table does now contain rows with IDs 300, 301, 302, 400, 401, 500,
--> Resume: The second INSERT statement was only partially successful aka not atomic. As the statement had failed, I had excepted that the table still only holds row with ID 500.
Is this a bug or by design? (This happened during development, I have always made sure that DML statements against NOT TRANSACTIONAL tables do not fail in production.) |
|
As I see it: an error occurs after some operation, but there is no rollback information in the log. Therefor there is no way to revert, hence the past operations will remain and no recently added records are deleted. If using non transactional stuff you will have to make sure you can handle everything, either by creating your own rollback system (eg. delete whole table) or by making sure that the partial operation will never break anything (eg by making sure that inserts later on will not insert existing records again)
Well, I am aware that there is no transactional control for NOT TRANSACTIONAL tables, so if a second SQL statement within the same transaction fails or the system goes down, the first won't be undone. (If the system goes down or the connection is lost, the whole local temporary table would be lost anyway.) But in my understanding, the description of the rollback log relates to whole statements within transactions, not to statements that are only partially executed... here "the error occurs during some operation", not after.
(26 Jun '23, 04:00)
Volker Barth
|
