Issue Details (XML | Word | Printable)

Key: CORE-3490
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Roger Vellacott
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Concurrency problem when using named cursors

Created: 23/May/11 11:09 AM   Updated: 27/Mar/14 12:41 PM
Component/s: None
Affects Version/s: 2.1.0, 2.1.1, 2.0.5, 2.1.2, 2.1.3, 3.0 Initial, 2.0.6, 2.5.0, 2.1.4, 2.5.1
Fix Version/s: 2.5.2, 3.0 Alpha 1

Time Tracking:
Not Specified

Environment: Windows 7 64bit, FB2.5 Classic
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
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)




 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 23/May/11 12:47 PM
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?