New 6.2 Record Locking
Posted 18 February 2010 - 06:48 PM
PROIV does not have documentation for these yet but they gave me the following information:
From PROIV support for version 6.2
PROIV's enhanced record lock mechanism: LOCK_TIMEOUT for SQL Server is enabled by default. The amount of seconds before a record lock message is displayed back to PROIV User is determined by the following table. Note: @SQL_TIMEOUT defined in PROIV Logic has higher precedence over SQLTIMEOUT. LOCK_TIMEOUT can be disabled by defining the PROIV Environment Variable: SQLSERVE_TABLE_LOCKING=N. Once LOCK_TIMEOUT has been disabled, PROIV will use the lock detection mechanism implemented in version 55 where the ODBC query timeout attribute is used to detect record lock.
@SQL_TIMEOUT -1 Report record lock immediately the SQL command is issued to the database
@SQL_TIMEOUT 0 Set to value of SQLTIMEOUT defined in PRO4.INI
@SQL_TIMEOUT >0 Block for the specified amount of seconds before raising a record lock message.
To ensure the break key works verify that LOCKED_ROWS_RETURNED=Y and LOCK_TIMEOUT=Y. If LOCK_TIMEOUT is not specified it will be enabled by default with the 220.127.116.11 release and above.
It allows MS SQL2005 to return a timeout value when set to YES (enables LOCK_TIMEOUT). The current default is NO.
When using SQL Server 2005 or 2008, you need to have SQL_ADD_UPDLOCK = Y in the environment section of the pro4v6.ini file, to maintain behavior consistent with SQL Server 2000, and prevent unwanted problems with file locks. This is due to changes in the locking mechanisms within the database. This inserts 'WITH(UPDLOCK)' into the 'SELECT FOR UPDATE' queries.
If set to 'Y' or 'y' the PROIV Kernel will ignore minor mismatches between the timestamp of the file definition of a given file in the genfile entry of a function and the timestamp of the file definition itself. If not set and the SQL file definition is modified it will display a file mismatch error message.
I tested setting @SQL_TIMEOUT to -1 in logic and it executed lock logic immediately. I was hoping someone had tested this already and could enlighten us further to save me some time. I did not know that you could set @SQL_TIMEOUT in logic.
Edited by Lewis Mccabe, 18 February 2010 - 06:49 PM.
Posted 19 February 2010 - 01:24 PM
SQLTIMEOUT and @SQL_TIMEOUT aren't new - I've used them with DB2 and V5.5, we actually had to get ProIV to make a minor fix/change to the kernel to get the behaviour we wanted with DB2 (mostly because DB2 locks in lookup mode and because we we didn't want certain behaviour that was oriented to screen functions - our objectives and use of ProIV were probably atypical)
Two things to watch out for are how much gets rolled back when there is a lock timeout and what impact you're having on deadlock detection.
SQL_ADD_UPDLOCK looks SQL/Server-specific and setting it is presumably to provide backward-compatible behaviour and avoid some "new" behaviour. So I would set it if I were you
I'd guess the "new" behaviour would be acquiring only a share-lock on read and promoting that lock when an actual update occurs. This is not that unusual in the DBMS world but it can result in lock-waits and errors occuring at a different time in the ProIV cycle obviously - and not when lock-logic can be applied.
I'm not sure why it talks about *table* locking, I find that a bit odd, maybe it's to do with some SQL/Server-specific restriction.
SQL_DISABLE_TIMESTAMP_CHECK would seem to have nothing to do with locking, just a way to suppress the safety check for all functions having been genned with the exact same version of a filedef (affects safe reuse of SQL statements and maybe a lot of other stuff - I wouldn't risk that personally..)
Posted 19 February 2010 - 01:31 PM
Actually, strike that. I meant acquiring an intent-lock. This would co-exist with other read-only access but not with write intent. Detection of lock conflicts would still occur at read time, the difference would be that other readers can proceed and might delay promotion of an intent-lock to an exlusive lock.
And that could be entirely moot depending on SQL/Server's support for row-versioning. I would ignore me and study the SQL/Server docs for the gory details
Posted 19 February 2010 - 02:19 PM
Gory details - you couldn't put it better!
of the poster and do not represent those of any organisation.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users