Uniface User Forum

 View Only
  • 1.  How to read record(row) from table which is locked

    Posted 01-19-2023 11:48

    Hi all,

    I am fixing a bug in my app but it seems it behaves differently on my local env. vs Virtual machine env. and I am not able to find the difference (setting, asn file, SQL management studio?).

    On virtual machine if via one instance of app a row is locked (store without commit) then in other instance of the app the record is not possible to retrieve it from the table.

    On my local env. I do the same operation and I can retrieve the record from locked table on the other instance of the app.  Of course I can not modify or save it. 

    Do you know how can this be possible and what should I check? 

    Jozef 



    ------------------------------
    Jozef Prokopovic
    Uniface Community Edition Shared Account
    US
    ------------------------------


  • 2.  RE: How to read record(row) from table which is locked

    Posted 01-20-2023 05:39
    Edited by Gianni Sandigliano 01-20-2023 05:39

    Hi Jozef,

    locking technique is homogeneous from the Uniface perspective but unfortunately different (R)DBMS working under Uniface react in different ways; also the same (R)DBMS configured differently reacts to Uniface in different ways.

    To follow on your question some more informations are needed:
    1) Which Uniface locking mode you've used in your case:
    - pessimistic (read/lock instruction)
    - cautious (default)
    - optimistic (configured at DBMS or table level)

    2) Which (R)DBMS you are using either:
    - in your local env
    - in your VM env (I suppose SQLlite here considering you have a Community Edition account)
    It is also important to know how the two (R)DBMS instances (both!) are configured.

    Let us know...

    Regards,
    Gianni



    ------------------------------
    Gianni Sandigliano
    IT
    ------------------------------



  • 3.  RE: How to read record(row) from table which is locked

    Posted 01-20-2023 07:38
    Hi Gianni,

    1) Which Uniface locking mode you've used in your case:

    the entity Locking setting is set to  "Optimistic".

    2) Which (R)DBMS you are using either:

    both env use MS SQL

    "It is also important to know how the two (R)DBMS instances (both!) are configured."

    What should I check in the configuration?

    Jozef




    ------------------------------
    Jozef Prokopovic
    Uniface Community Edition Shared Account
    US
    ------------------------------



  • 4.  RE: How to read record(row) from table which is locked

    ROCKETEER
    Posted 01-20-2023 07:52
    I have also encountered this before with MS SQL Server. In all cases the ODBC driver and settings used by Uniface was the same and the only difference was on which platform I did perform my tests (e.g. Windows or Linux). In some cases, I even had differences between two different Windows Server versions: on one machine a locked record could be read (by another session) and on the other an error was returned. Never really got to the bottom of this and simply accepted this difference in behavior. If I remember it correctly then I have used the lock isolation "read uncommitted" - did not test this with any other lock isolation. And I am pretty sure that the server was configured in the same way on all systems, but I am not really a dba.

    Maybe there is a MS SQL Server expert who reads this and can explain these differences?

    I hope this helps (somehow).

    Daniel

    ------------------------------
    Daniel Iseli
    Principal Technical Support Engineer
    Uniface Services
    Rocket Software, Switzerland
    ------------------------------



  • 5.  RE: How to read record(row) from table which is locked

    Posted 01-22-2023 04:00
    Edited by Gianni Sandigliano 01-22-2023 04:49

    Hi Jozef and Daniel,

    I am NOT a great SQL Server expert but I have helped solve more than one Uniface application locking problem working on SQL Server working alongside real SQL Server experts.

    SQL Server in its standard "on premise" installation has always been configured as "read committed" since its first release and still is today; this configuration is a reasonable compromise, and is valid not only for SQL Server but also for all other RDBMSs that refer to ANSI isolation levels.

    Starting with SQL Server 2005 some new configurations modes have been released in the product regarding locking; these new configurations use SNAPSHOT:
    - READ_COMMITTED_SNAPSHOT (since SQL 2005)
    - ALLOW_SNAPSHOT_ISOLATION (since SQL 2008 R2)
    More information can be found on the internet at the page: https://msdn.microsoft.com/en-us/library/ms188277.aspx

    Based on repeated field tests, the "read committed snapshot" configuration would seem to be the one that guarantees the best behavior homogeneity to a Uniface application with other RDBMSs such as Oracle and PostgreSQL. It is also the one implementing the global golden rule "Read (Select) should NOT be blocked by Write (Insert / Update / Delete)".

    Some Uniface users I've worked with in the past on these issues have reported to me that the "read committed snapshot" mode is the one that is configured by default when acquiring an "on cloud" SQL Server host on Azure. I haven't personally checked if this statement is true but in the configurations I've worked on this mode has solved several concurrency problems among users of the Uniface application.

    Uniface fully supports "read committed snapshot" mode using:

    USYS$MSS_params iso=ss

    The only side effect of this setup is the additional workload that the SQL Server implementation of this mode places on the TempDB; in an installation with about 100 users the additional occupation of the TempDB for maintaining the so called "Version Store" in SQL 2012 was about 40GB, which can grow up under specific circumstances. Consequently, this configuration must be implemented in full agreement with the administrators of the database on which it is to be applied.

    I hope it helps you.

    Best Regards,
    Gianni



    ------------------------------
    Gianni Sandigliano
    IT
    ------------------------------



  • 6.  RE: How to read record(row) from table which is locked

    Posted 01-23-2023 07:47
    Hi Gianni,

    the setting I have in the asn file is using iso:ts. Does this make any difference or may it have impact comparing to the iso:ss ?

    Jozef

    ------------------------------
    Jozef Prokopovic
    Uniface Community Edition Shared Account
    US
    ------------------------------



  • 7.  RE: How to read record(row) from table which is locked

    Posted 01-23-2023 13:37

    Hi Jozef,

    "ts" it's short to mean "transaction snapshot" so you are asking Uniface to configure its connection to SQLServer in this way.

    This configuration should enable your application to have "read never blocked from write", using either a real table or a view.

    Two ideas to check:

    1) The use of "transaction snapshot" MUST be enabled at database level. See Uniface manual but also SQL Server manual about it. Are you sure both your DBMS server are correctly enabled?

    2) We've choosen iso=ss over iso=ts because we found small issues with ts. I do not remember right now what these small issues were related; I'll try to dig in my documentation to look for it.

    Regards,
    Gianni



    ------------------------------
    Gianni Sandigliano
    IT
    ------------------------------



  • 8.  RE: How to read record(row) from table which is locked

    Posted 01-26-2023 05:52

    Hi Jozef,

    digging into my documentation I've found the following sentence from Microsoft which could explain why we've choosen iso=ss over iso=ts:

    Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level. If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the Snapshot isolation level for each session in order to access versioned rows.

    Aiming for the minimal changes to the production application to provide "reads should not block writes and reads should not be blocked from writes" we decided for:
    - SQLserver database: alter database <DATABASENAME> set read_committed_snapshot on;
    - Uniface: USYS$MSS_PARAMS iso=ss.
    It WORKED!

    Hope it helps.

    Regards,
    Gianni



    ------------------------------
    Gianni Sandigliano
    IT
    ------------------------------



  • 9.  RE: How to read record(row) from table which is locked

    Posted 01-26-2023 09:57
    Hi Gianni,

    our setting of the SQL is "Allow snapshot isolation = TRUE" and the "Is Read Committed Snapshot On = FALSE" and iso:ts. But that settting is the same on both environments.

    I will test your configuration and how it works. What should be the Allow snapshot isolation then? True or False?

    Jozef

    ------------------------------
    Jozef Prokopovic
    Uniface Community Edition Shared Account
    US
    ------------------------------



  • 10.  RE: How to read record(row) from table which is locked

    Posted 01-27-2023 01:46

    Hi Jozef,

    we did NOT change "Allow snapshot isolation"...so I suppose the default should be False.

    Gianni



    ------------------------------
    Gianni Sandigliano
    IT
    ------------------------------