Jump to content


Photo
- - - - -

New 6.2 Record Locking


8 replies to this topic

#1 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 18 February 2010 - 04:33 PM

We are going to code for the new version 6 locking mechanism and were wondering if anyone had already done this and knew the issues which will be raised. We are using SQL Server. Thanks.
Lew

#2 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 18 February 2010 - 06:33 PM

What new locking mechanism is that then?
Is there a link explaining it anywhere?
Nothing's as simple as you think

#3 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 18 February 2010 - 06:48 PM

I asked PROIV for some info on some environment variables from Ross Bevin's post to my question: http://www.proivrc.c...?showtopic=4067

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 6.1.57.0 release and above.

SQLSERVE_TABLE_LOCKING
It allows MS SQL2005 to return a timeout value when set to YES (enables LOCK_TIMEOUT). The current default is NO.


SQL_ADD_UPDLOCK
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.

SQL_DISABLE_TIMESTAMP_CHECK
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.


Lew

Edited by Lewis Mccabe, 18 February 2010 - 06:49 PM.


#4 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 19 February 2010 - 01:24 PM

I haven't used SQL/Server, but for what it's worth.

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..)
Nothing's as simple as you think

#5 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 19 February 2010 - 01:31 PM

> I'd guess the "new" behaviour would be acquiring only a share-lock on read

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 :)
Nothing's as simple as you think

#6 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 19 February 2010 - 02:19 PM

Table locking is probably because SQLServer can very quickly escalate to that level with it's default configuration.

Gory details - you couldn't put it better!
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#7 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 19 February 2010 - 02:28 PM

My interest was in @SQL_TIMEOUT. With the option for -1 and an immediate execution of lock logic, I was assuming we would not get any more error 18's. I tested last night but the error 18's persisted.

#8 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 19 February 2010 - 05:21 PM

You're getting 'ERROR IN WRITING FILE' ?
Do you get any more specific SQL/Server information in @SYSERR / @SYSERRTEXT ?
Nothing's as simple as you think

#9 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 19 February 2010 - 05:30 PM

Yes - error in writing file. I did not check for additional info in that extending the time in @SQL_TIMEOUT solved the problem. When I get a chance I will post the details.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users