I have a function creating records (INSERT) in a table and then showing these records in a form. Later when the user closes the form (OnCloseForm), the same records are deleted (DELETEALL). It is only the exact records which is inserted when user runs the function, which then are deleted as the user closes the form. The records nature are as you can see temporary, but unfortunately I can not use a temporary table in this case. Approximately I can have 50 users running this function at the same time, so there are a lot of inserts and deletes in this table. And of course this causes deadlocks… Any suggestions on how I can avoid deadlocks in this case? The system is SQL-server 2000 and Navision 3.60.
Try using COMMIT after every (n) INSERT(s).
Hallo Fredrik, do you mean tablelocks instead of deadlocks? deadlock only occur on locking two tables at the same time in two different orders. but you talked of one table (INSERT, DELETE). br Josef Metz
To deadlock you need more than 1 consumer and more than one resource.In SQL you can deadlock on the same table because the lock resource is not the table but the record or index key-range. Since you have 2 consumers you can deadlock. There is a diagnostic flag that might help you - I’ll check for the number and get back.
Thanks for all the answers! [^] Well, I dont know if the right definition is deadlock, but the error message contains that word. The error occurs when one user tries to delete in the table at the same time as someone else is trying to insert records. INSERT is made within a REPEAT - UNTIL and in my old code COMMIT was made after. I have now, according to Steffen, put COMMIT after each INSERT within the loop. With this change I have not been able to recreate the deadlock anymore. Right now the change is evaluated in the live database. RobertC, please come back with the diagnostic flag number, it’ll be insteresting! Problem is that during INSERT and DELETE or DELETEALL the complete table is locked, this behaviour cannot be avoided. By using COMMIT after every INSERT the time the table is locked is minimized. Right?
Regarding the flag, you need to create the table [$ndo$dbconfig] in your database, as documented in either the Application Designers Guide or Installation and System Management guide for SQL - can’t remember which - for index hinting. Instead place the following in it: CREATE TABLE [$ndo$dbconfig] (config VARCHAR(1024)) INSERT INTO [$ndo$dbconfig] VALUES (‘DefaultLockGranularity=Yes’) Restart your clients. This will cause Navision to not place ROWLOCK directives on SELECT statements, which means SQL Server will have the option to page or table lock instead. This can reduce deadlocks, but at the cost of concurrency. It is unknown what affect it will actually have for your situation, because the choice SQL Server makes is not predictable - depends on many factors. It may turn out to be too radical concurrency wise. Regarding INSERT, DELETE and DELETEALL - they do not table lock. This is why you are experiencing deadlocks. Reducing the duration of transactions by using COMMIT can also reduce deadlocks and concurrency problems, at the expense of placing the owness on the application programmer to ensure consistent data during the overall task being performed (e.g. by making tasks repeatable). This is what transactions do for you automatically 'cos they’re all or nothing.