Richard Bassett

As a matter of interest, would you be experiencing locking issues here because you are not using row versioning?

(IIRC, row versioning a.k.a. MVCC is an optional thing in SQL/Server, unlike Oracle, Postgres and MySQL/InnoDB)

Hi Marlon,

OK, so if Timeout -1 in ProIV means "give up instantly on encountering a lock" then that makes sense of the observed behaviour.  However, that doesn't sound like a setting that is of very much use in the real world?  Also, I notice that ProIV's use of -1 and 0 here seems to be the exact opposite of SQL/Server - which seems to use -1 to mean indefinite wait and 0 to mean no wait (see e.g. https://docs.microso...ql-server-ver15 ).

Given that level of ambiguity, perhaps I should ask here what is really meant by "Table Locking" ?  For me, that would normally mean locking entire tables instead of only locking the records (rows) that you read and write.  Again, in my experience at least, that's an approach that isn't of much use in the real world.

Regarding "Table Locking" and read uncommitted, I was asking why one would want to set either of them, not suggesting the choices were connected.  In most circumstances I would consider both of them unhelpful.  Also, I don't see that read uncommitted offers any advantages (only disadvantages) in any database that has multiversioned concurrency control, which I thought SQL/Server had for a long time now?

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.

ALTHOUGH... something I just noticed now..

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

(although it still wouldn't explain how a single application session/process could encounter a lock, if that's really the case)

I'm not sure I can help much as several things here don't seem to make sense, at least not to me with my limited knowledge of SQL Server.  For what it's worth these are the things I found odd:

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) ?

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

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

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

What are the write errors you're getting?

For example what is in @SYSERRTEXT?

(Caveat:  I've never used SQL Server but I'm assuming ProIV's behaviour is largely database-independent)