Concurrency problem when using named cursors [CORE3490]

[firebird-automations]

Submitted by: Roger Vellacott (rvellacott_passfield.co.uk)

Is related to QA481

If, in PSQL, I open a named cursor on a record, and some other operation changes
a field in that record, then the change is lost when I post using the cursor,
even if the cursor does not fetch the changed field.

If I define the cursor as FOR UPDATE OF MY_FIELD WITH LOCK, then the system
crashes if, after some other operation changes the record, I try to post using
the cursor.

Here is a simple example, using FB 2.5 classic. The example is unrealistic, and can be easily avoided, but equivalent updates to records already open in cursors can easily happen when triggers operate recusrively.

CREATE TABLE MY_TABLE (A INTEGER, B INTEGER,C INTEGER);
INSERT INTO MY_TABLE(A,B,C) VALUES (1,1,1);

set term ^ ;

EXECUTE BLOCK AS

DECLARE MY_CURSOR CURSOR FOR
(SELECT B FROM MY_TABLE
WHERE A = 1 /* FOR UPDATE OF B WITH LOCK */ ) ;
DECLARE B INTEGER;

BEGIN
OPEN MY_CURSOR;
FETCH MY_CURSOR INTO :B;

UPDATE MY_TABLE SET C = 2
WHERE A = 1;

UPDATE MY_TABLE SET B = 2
WHERE CURRENT OF MY_CURSOR;
END^

SELECT * FROM MY_TABLE gives the result 1,2,1

If "FOR_UPDATE OF B WITH LOCK" is uncommented, Flamerobin crashes with the
message

Engine Code : 335544333
Engine Message :
internal Firebird consistency check (cannot find record back version (291),
file: vio.cpp line: 5024)

Commits: e3fb73c 48259b5

[firebird-automations]

Modified by: Roger Vellacott (rvellacott_passfield.co.uk)

description: If, in PSQL, I open a named cursor on a record, and some other operation changes
a field in that record, then the change is lost when I post using the cursor,
even if the cursor does not fetch the changed field.

If I define the cursor as FOR UPDATE OF MY_FIELD WITH LOCK, then the system
crashes if, after some other operation changes the record, I try to post using
the cursor.

Here is a simple example, using FB 2.5 classic. The example is unrealistic, and can be easily avoided, but equivalent updates to records already open in cursors can easily happen when triggers operate recusrively.

CREATE TABLE MY_TABLE (A INTEGER, B INTEGER,C INTEGER);
INSERT INTO MY_TABLE(A,B,C) VALUES (1,1,1);

set term ^ ;

EXECUTE BLOCK AS

DECLARE MY_CURSOR CURSOR FOR
(SELECT B FROM MY_TABLE
WHERE A = 1 /* FOR UPDATE OF B WITH LOCK */ ) ;
DECLARE B INTEGER;

BEGIN
OPEN MY_CURSOR;
FETCH MY_CURSOR INTO :B;

UPDATE MY_TABLE SET C = 2
WHERE A = 1;

UPDATE MY_TABLE SET B = 2
WHERE CURRENT OF MY_CURSOR;
END^

SELECT * FROM MY_TABLE gives the result 1,2,1

If "FOR_UPDATE OF B WITH LOCK" is uncommented, the system crashes with the
message

Engine Code : 335544333
Engine Message :
internal Firebird consistency check (cannot find record back version (291),
file: vio.cpp line: 5024)

=>

If, in PSQL, I open a named cursor on a record, and some other operation changes
a field in that record, then the change is lost when I post using the cursor,
even if the cursor does not fetch the changed field.

If I define the cursor as FOR UPDATE OF MY_FIELD WITH LOCK, then the system
crashes if, after some other operation changes the record, I try to post using
the cursor.

Here is a simple example, using FB 2.5 classic. The example is unrealistic, and can be easily avoided, but equivalent updates to records already open in cursors can easily happen when triggers operate recusrively.

CREATE TABLE MY_TABLE (A INTEGER, B INTEGER,C INTEGER);
INSERT INTO MY_TABLE(A,B,C) VALUES (1,1,1);

set term ^ ;

EXECUTE BLOCK AS

DECLARE MY_CURSOR CURSOR FOR
(SELECT B FROM MY_TABLE
WHERE A = 1 /* FOR UPDATE OF B WITH LOCK */ ) ;
DECLARE B INTEGER;

BEGIN
OPEN MY_CURSOR;
FETCH MY_CURSOR INTO :B;

UPDATE MY_TABLE SET C = 2
WHERE A = 1;

UPDATE MY_TABLE SET B = 2
WHERE CURRENT OF MY_CURSOR;
END^

SELECT * FROM MY_TABLE gives the result 1,2,1

If "FOR_UPDATE OF B WITH LOCK" is uncommented, Flamerobin crashes with the
message

Engine Code : 335544333
Engine Message :
internal Firebird consistency check (cannot find record back version (291),
file: vio.cpp line: 5024)

[firebird-automations]

Commented by: @dyemanov

The logical solution here is to re-fetch the record before updating it based on the active cursor. However, there's one issue here. Imagine your first update looking like this:

UPDATE MY_TABLE SET A = 0 WHERE A = 1;

After its execution, the fetched record won't satisfy the cursor's search condition anymore. Should we ignore this and update the record once more? Or should we re-evaluate the predicate against the refreshed record and filter it out? If the latter, how should the second (cursor based) update behave - as a no-op or throw an error?

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @dyemanov

Version: 2.5.1 [ 10333 ]

Version: 2.1.4 [ 10361 ]

Version: 2.0.6 [ 10303 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.1.0 [ 10041 ]

[firebird-automations]

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.5.2 [ 10450 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA481 [ QA481 ]

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: Done successfully

[firebird-automations]

Commented by: @pcisar

Test created.

[firebird-automations]

Modified by: @pcisar

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