Marlon

Hi Marlon

 

Is SQL_NOSIG enabled in your v9 configuration?

 

 

Regards

Hi Neil,

I don't know.  The PROIV v9 help only tells you what the equivalent is, proiv.virtualMachine.database.driver.general.enableSQLAlarm, not where to set it so I don't know what we have. (To say I'm not enamoured with the v9 help would be an understatement).

Re SEL-RANGE not working with SEL-RANGE(var1 + var2 etc.), that's not a bug, it's a feature.  PROIV is full of features!

We don't use SEL-RANGE any more as we are totally Oracle/SQLServer, we also use FFSQL in the selection logic.

SQLSERVE_TABLE_LOCKING is the v8 .ini setting for the Enable Table Locking dasboard option.  According to PROIV help topic 720072, it's enabled by default from PROIV v6.1.57.0 release onwards so we've been using it in the past without knowing it.

Hi Richard,

This

"It does seem dubious that a process is apparently able to lock against itself when then is an 'immediate' timeout but this problem disappears once you wait on locks.  I can only imagine something subtle and confusing is going on, such as the actual release of locks happening 'asynchronously' in the background and the process maybe impatiently tripping over locks from its own preceding transaction that haven't quite been released yet (this might be plausible if there is some kind of asynchronous commit being used for performance or as a default).  This hypothesis could also be consistent with the tracing slowing everything down causing the problem to be masked."

is where I think we're going with our thinking, too.

Richard,

Thanks for your analysis.

In answer to your questions.

"Are you saying you're getting lock timeouts with only one application/session process connected to the database (or at least using some part of the database nothing else is using) ?"

Yes, we are getting table locking with only one connection to the database.  It's a timing test so nothing else runs at the same time.

"Why would you have "Table Locking" and "Read Uncommitted" enabled?"

Are you saying having both of these enabled doesn't make sense?  I did retest with Red Uncommitted disabled and it didn't make any difference.

"Why are there timeouts at all if "Timeout" is configured to -1?"

According to the PROIV help, -1 means "Report record lock immediately the SQL command is issued to the database" so that is consistent with getting the timeout messages.  0 means "Block indefinitely until the database releases the record lock" which would be consistent with not getting an error reported, it just waits until the lock's cleared.

"Why would slowing the system with more detailed tracing help?  (intuitively you might think it would make timeouts worse?)"

Because PROIV is running more slowly so there's enough time for the lock to be released.  Of course, this still doesn't explain why a process is being locked out by itself.

"assuming those are timestamps on the left hand side of your trace above, there appears to be NO DELAY AT ALL before the database says you are timed out...which very strongly suggests that somehow the timeout is actually set to ZERO, which would mean a lock results in immeditate failure...and that certainly would explain the unexpected problems, and why detailed tracing makes the problem less likely."

A timeout value of  -1 means 'report immediately' so the behaviour is consistent with that.

Using a timeout of 0 has 'fixed' the problem, i.e. the write error is no longer reported but I'm not convinced this version of PROIV, v9.5, is actually behaving itself.