understanding transactions

[Giovanni Bucci]

Hi,

Trying to understand transactions in Firebird, I use Firebird SQL 3 and 2 ISQL windows to simulate conflicts.

I have been able to understand a lot but still I cannot understand how it should works this:

Table1 with fields keyfield and field1

2 transactions in 2 ISQL one is T1 which is old than T2 according to Current_transaction from rbd$database

in both transactions:

SET TRANSACTION READ WRITE WAIT READ COMMITTED NO RECORD_VERSION;

Select * from Table1;

Update Table1 set field1 = 'Test' where keyfield = 'key';

T2 Starts the update but not commit

T1 starts the update and it is forced to wait
T2.commit

What happen

   T1 is free and can commit too overwriting the value of T2 update

What i would expect

  T1 should receive a lock error

I am based on the FB language reference I would expect at least one situation where 2 concurrent transactions of this type would return a lock error, but I am not able to have this lock and I am not sure where I am wrong.

READ COMMITTED Isolation Level

The READ COMMITTED isolation level allows all data changes that other transactions have committed since it started to be seen immediately by the uncommitted current transaction. Uncommitted changes are not visible to a READ COMMITTED transaction.

To retrieve the updated list of rows in the table you are interested in — “refresh” — the SELECT statement just needs to be requested again, whilst still in the uncommitted READ COMMITTED transaction.

RECORD_VERSION

One of two modifying parameters can be specified for READ COMMITTED transactions, depending on the kind of conflict resolution desired: RECORD_VERSION and NO RECORD_VERSION. As the names suggest, they are mutually exclusive.

• NO RECORD_VERSION (the default value) is a kind of two-phase locking mechanism: it will make the transaction unable to write to any row that has an update pending from another transaction.

  • if NO WAIT is the lock resolution strategy specified, it will throw a lock conflict error immediately

  • with WAIT specified, it will wait until the other transaction either commits or is rolled back. If the other transaction is rolled back, or if it is committed and its transaction ID is older than the current transaction’s ID, then the current transaction’s change is allowed. A lock conflict error is returned if the other transaction was committed and its ID was newer than that of the current transaction.
    
    

  • Can you help me understand what I should expect and why it is not working as per reference ? I am not a native english speaker so maybe i did not read properly.

Thanks.

[Dimitry Sibiryakov]

Giovanni Bucci wrote 01.05.2024 17:02:
> Can you help me understand what I should expect and why it is not working as per
> reference ?

Do not expect anything from "read committed no record_version" mode. It is a
Borland's messy attempt to emulate "dirty read" or Oracle's "read committed"
behavior. Result of this is in general unpredictable.
Should not be used in real applications ever.

--
WBR, SD.

[Dalton Calford]

https://ib-aid.com/en/transactions-in-firebird-acid-isolation-levels-deadlocks-and-update-conflicts-resolution/

Is a great resource for this sort of thing

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/c0af7e8e-9105-4d11-a6ae-070bd7a40f4b%40ibphoenix.com.

[Giovanni Bucci]

Thank you for the fast answer, it is 3 days I am on it , I am not a native english speaker so please confirm I understand all correctly:

Firebird is born from Interbase,

Interbase has a defect which has been mantained as it was also in Firebird,

The firebird manual keep telling me something which is not true, possibly in all the versions, up to FB5 language reference.

In real cases I never have to use the   "read committed no record_version" mode as it does not works by definition.

Is my understanding all correct ?

Just to be sure I do not try to make something else that by definition does not work, there is any other thing like that ?

I mean, there is a document somewhere like "things that are there but do not work actually and you do not have to use" ?

Thank you, you saved me, I will submit another question about transactions later.

[Dimitry Sibiryakov]

Giovanni Bucci wrote 01.05.2024 17:21:
> The firebird manual keep telling me something which is not true, possibly in all
> the versions, up to FB5 language reference.

Yes, there is a little mistake in it: in exactly this mode getting or not
getting of the error depends not on transaction number but on exact timing of
internal operations.

> In real cases I never have to use the  "read committed no record_version" mode
> as it does not works by definition.
>
> Is my understanding all correct ?

Not quite so. "read committed no record_version" DOES work. It is just a
little unpredictable from application point of view. That's why you CAN use it
but be prepared to get surprised from time to time.

--
WBR, SD.

[Giovanni Bucci]

Thank you, for the rapid reply.

One more question about the SET TRANSACTION READ WRITE WAIT READ COMMITTED NO RECORD_VERSION.

From 2 different ISQL I have two transactions with  the same  SET TRANSACTION READ WRITE WAIT READ COMMITTED NO RECORD_VERSION

they do the same update on the same row with different data.

it seems there is no difference either if the first one to start a transaction is the older transaction T1 or the newer T2.

I always obtain a situation like:

T1.update , T2.update, T2.wait, T1.commit, T2.ready, T2.commit / retain and if commit then it over writes changes made by T1

The same happens for T2.update, T1.update, T1.wait, T2.commit, T1.ready, T1.commit /  retain and if commit then it over writes changes made by T1

Is this the expected behaviour under any circumstance ?

Thanks.

[Dimitry Sibiryakov]

Giovanni Bucci wrote 01.05.2024 17:46:
> Is this the expected behaviour under any circumstance ?

As I already said: you'll get this behaviour in MOST cases (perhaps 99,999%).
But sometimes (the rest 0,001%) you'll get update conflict error.

PS: "Lost writes" used to be considered as a bad thing that's why this
transaction mode is not recommended for use.

--
WBR, SD.

[Giovanni Bucci]

Thank you for confirming the behaviour is correct and i need to expect this for that isolation mode