Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Rolled back transaction produces unexpected results leading to duplicate values in PRIMARY KEY field [CORE6343] #6584

Closed
firebird-automations opened this issue Jun 24, 2020 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Tomasz J (tommy)

Recently we have noticed duplicate entries in primary key field. How to reproduce the issue:

Database:

CREATE GLOBAL TEMPORARY TABLE GTT_TABLE (
ID INTEGER NOT NULL
) ON COMMIT DELETE ROWS;

CREATE TABLE TEST_TABLE (
ID INTEGER NOT NULL PRIMARY KEY
);

CREATE OR ALTER PROCEDURE TEST_PROC
RETURNS (
ID1 INTEGER)
AS
DECLARE VARIABLE ID2 INTEGER;
BEGIN
INSERT INTO GTT_TABLE VALUES(1);
INSERT INTO GTT_TABLE VALUES(2);
INSERT INTO GTT_TABLE VALUES(3);
FOR SELECT ID FROM GTT_TABLE
INTO :ID1 DO
BEGIN
INSERT INTO TEST_TABLE (ID) VALUES (:ID1);

                FOR SELECT 1 FROM RDB$DATABASE INTO :ID2 DO
                   IF \(:ID1=3\) THEN ID1 = 1/0;  \-\-in production there is EXCEPTION EX\_NAME instead
               
                SUSPEND;

                DELETE FROM TEST\_TABLE;
           END

END

Now using ISQL (reproduced also using other tools) execute:

1)
SQL> select * from test_proc;

     ID1

============
1
2
Statement failed, SQLSTATE = 22012
arithmetic exception, numeric overflow, or string truncation
-Integer divide by zero. The code attempted to divide an integer value by an integer divisor of zero.
-At procedure 'TEST_PROC' line: 16, col: 38

Result as expected.

2)
SQL> ROLLBACK;

3)
SQL> SELECT * FROM TEST_TABLE;

      ID

============
2
3

Expected result: empty dataset.

Now repeat steps 1), 2), 3) effects of 1) and 2) are the same ( 1) should already raise primary key violation) , but the final result is:

SQL> SELECT * FROM TEST_TABLE;

      ID

============
2
3
2
3

I have reproduced the above in 3.0.5 Linux, 3.0.4 Windows, 3.0.3 Linux (all Classic).

I hope I have included enough details (this is my first ticket).

Commits: 8b328f8

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Confirmed on WI-V3.0.6.33322. No such problem on WI-V4.0.0.2073 (checked with ReadConsistency = 0 and 1) - table 'TEST_TABLE' remains empty..

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0.4 [ 10863 ]

Version: 3.0.3 [ 10810 ]

Version: 3.0.2 [ 10785 ]

Version: 3.0.1 [ 10730 ]

Version: 3.0.0 [ 10740 ]

Fix Version: 3.0.6 [ 10889 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Please test snapshot build 33326.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Already tested. All OK.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment