SQL and Table Locking

TheDude27

Using Navision 3.6 SQL

I have a form that is filtered on the Session ID of the each user's session that runs off a common table. For example the Data could look(simplified) like this (primary key is Session, Line):


Session --- Line --- Description --- Quantity

24 --- 10000 --- Line 1 --- 10
24 --- 20000 --- Line 2 --- 20
30 --- 10000 --- Line 1 --- 2


The form then sets a filter using the Filtergroup 2 based on the current users Session.



So here's the problem(s). At first, I was getting some deadlocking(Strange since the chance that row level locking would have issues with the volume of records I am looking at seem slim) issues from the process that populates the above table. Ok No big deal, I'll just do some table locking and make sure that only one user is doing anything to those tables. In C/SIDE this would have easily worked (would have got the waiting for table blah hit control-break to exit). In SQL, no joy, didn't even appear to matter what TransactionType I set either. (I've had another devloper come to me with this issue as well. IF someone wants to explain the finer points of using table locking with SQL I'm all ears).....


Ok No biggie I'll adapt and overcome. So I wrote my own spin lock using a semaphore table that looks(simplifed) like this:

Sesssion --- Login Time --- Working.

24 --- 8:50pm --- No
30 --- 7:20am --- Yes



Login time was a way to maintain a unique identifier even though someone could reuse the session.


So I call the locking management codeunit that sets the working flag to Yes when I am populating the table and unsets it when done. If one user has the flag set, I wait till its unset to let the next user in, and so on. Works great....Except OCCAISIONALLY, when a user is in the form making modifications in their filtered (by session) records and another user is coming in I get "another user has modified the blah lines table". How is this possible? One user NEVER does anything without setting a session based filter thus no records on user 30's screen should ever be touched. I'd post the exact code but its extremely complicated and about 4000 lines.

Anyone run into anything similar this??


Thanks,
The Dude

dmccrae

All statements sent to SQL Server by Navision have a ROWLOCK directive, so row level locking is being done even for a small table. It is possible to remove this override and let SQL determine the locking, but you may still get row-level locking.

When you tried TransactionType did you do it in this order:

TRANSACTIONTYPE := Snapshot;
Rec.LOCKTABLE;
FIND();

The transaction type must be set as the first command in a transaction - e.g. the first in a function/trigger or the first after a COMMIT - otherwise it cannot change the transactiontype that is already set. Also you must do it before the LOCKTABLE of course.

The above will use a SQL Server SERIALIZABLE transaction and will place shared locks on all rows read up until the LOCKTABLE - after that it will place UPDATE locks on all rows read. This will mean only one of the same rows can be read at a time by a connection. That won't prevent deadlocks though.

Its hard to know whats causing the problem with the time-log table without more code. But the error occurs when a record is read by a session without a LOCKTABLE (i.e. without protecting the record from changes by other sessions). Then another sessions changes it - your first session then tries to modify it. You get this error.