Informix

 View Only
  • 1.  IFX_ISOLATION_LEVEL 2 vs 3

    Posted Thu March 10, 2022 03:28 AM
    Hello

    I d like to understand what are the differences between isolation level 2 and 3
    Both of them are "equivalent to TRANSACTION_READ_COMMITTED"
    https://www.ibm.com/docs/en/informix-servers/14.10?topic=database-informix-environment-variables-informix-jdbc-driver

    Please, could someone redirect me to the right place that explains the subject.

    Thanks

    ------------------------------
    Samuel
    ------------------------------

    #Informix


  • 2.  RE: IFX_ISOLATION_LEVEL 2 vs 3

    IBM Champion
    Posted Thu March 10, 2022 07:23 AM
    Samuel:

    Here's the link to the SQL SET ISOLATION statement for Informix, the isolation levels are explained there:

    Help - HCL Informix V14.10 documentation


    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: IFX_ISOLATION_LEVEL 2 vs 3

    Posted Thu March 10, 2022 08:38 AM


    ------------------------------
    ZhiWei Cui
    GBASE
    ------------------------------



  • 4.  RE: IFX_ISOLATION_LEVEL 2 vs 3

    IBM Champion
    Posted Thu March 10, 2022 10:20 AM

    Hi,

    First of all, all of the ISOLATION LEVELS apply to reads only , not UPDATES, DELETES, INSERTS.

    Level 2: Committed Read means that when you try to read a row using a SELECT, the server returns the row if it has been committed (no lock on it: not being updated at read time). The system tries to put a lock on that row but does not actually put the lock; if it possible to put a lock , that means no one is modifying it at that particular time. Hence it has been committed. However, nothing prevents others users modifying that row or even deleting it after the row was read. The row that was read was a real row that was committed when the read was done. The only guaranty is that the row was not being modified when it was read.

    Level 3: Cursor Stability (applies to reads using cursors only) means that when you try to read the row using a FETCH thru a cursor, the server returns the row if it has been committed and puts a shared lock on it. That lock stays until you realese the lock by fetching another row using FETCH NEXT. Others users can read the same row but no one can modify it while you are reading it.

    --  Cordialement, Regards,    Khaled Bentebal  Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr