How to read record(row) from table which is locked

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 

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

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

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

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