Firebird is returninig the wrong information from database after waiting on a
lock
----------------------------------------------------------------------------------
Key: CORE-3764
URL: http://tracker.firebirdsql.org/browse/CORE-3764
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.0
Environment: Windows 7 32 bits.
Firebird 2.5.0.26074.
Reporter: Nataniel
Priority: Critical
Firebird is returninig the wrong information from database after waiting for a
lock to be released.
The problem can be easely simulated with this steps:
01 - Create a table "Table_01" with the following fields: Field_01, Field_02
and Field_03 (script 01) and insert some data (script 02).
02 - Open IBExpert and register the database and configure the transactions
(Tools -> Database registration info -> Transactions) as following:
isc_tpb_read_committed, isc_tpb_rec_version and isc_tpb_wait
03 - Execute the following SQL command into the database
select
Field_01,
Field_02,
Field_03
from Table_01
where Field_03 is null
order by Field_02
rows 5
for update with lock;
This query returns 5 rows with Field_01's value 1, 2, 3, 4 and 5.
04 - Open a second instance of IBExpert and executed the same SQL command, as
the first sql command locked the records the second one keep waiting.
05 - Get back to the first IbExpert instance and execute the following command
to update the record being locked and commit the transaction.
update Table_01 set
Field_03 = -1
where Field_01 in (1, 2, 3, 4, 5);
06 - After commiting the first transaction it releases the lock and the second
one continue executing but returns "random data", instead of returining rows 6,
7, 8, 9 and 10 it sometimes returns nothing, sometimes return 4 itens (codes 6
to 9).
This problem generate enormous issues on softwares with severe concurrency.
Below as the scripts to create the table and populate it.
Script 01:
CREATE TABLE TABLE_01 (
FIELD_01 INTEGER NOT NULL,
FIELD_02 VARCHAR(100),
FIELD_03 INTEGER
);
ALTER TABLE TABLE_01 ADD CONSTRAINT PK_TABLE_01 PRIMARY KEY
(FIELD_01);
Script 02:
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(1, 'ITEM 01', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(2, 'ITEM 02', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(3, 'ITEM 03', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(4, 'ITEM 04', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(5, 'ITEM 05', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(6, 'ITEM 06', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(7, 'ITEM 07', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(8, 'ITEM 08', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(9, 'ITEM 09', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(10, 'ITEM 10', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(11, 'ITEM 11', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(12, 'ITEM 12', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(13, 'ITEM 13', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(14, 'ITEM 14', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(15, 'ITEM 15', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(16, 'ITEM 16', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(17, 'ITEM 17', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(18, 'ITEM 18', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(19, 'ITEM 19', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(20, 'ITEM 20', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(21, 'ITEM 21', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(22, 'ITEM 22', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(23, 'ITEM 23', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(24, 'ITEM 24', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(25, 'ITEM 25', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(26, 'ITEM 26', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(27, 'ITEM 27', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(28, 'ITEM 28', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(29, 'ITEM 29', NULL);
INSERT INTO TABLE_01 (FIELD_01, FIELD_02, FIELD_03) VALUES
(30, 'ITEM 30', NULL);
COMMIT WORK;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Virtualization & Cloud Management Using Capacity Planning
Cloud computing makes use of virtualization - but cloud computing
also focuses on allowing computing to be delivered as a service.
http://www.accelacomm.com/jaw/sfnl/114/51521223/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
