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

Concurrency problem when using named cursors [CORE3490] #3849

Closed
firebird-automations opened this issue May 23, 2011 · 9 comments
Closed

Concurrency problem when using named cursors [CORE3490] #3849

firebird-automations opened this issue May 23, 2011 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA481 [ QA481 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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