Table Locking

1 of our customers has a problem with table locking They are running 3.70 cside They have a document template function that inserts a saved no. of order lines when the function is run. The only problem is that this locks other users out (of the Document Dimension table inparticular). Users shipping orders get the lock out (ctrl break) and sometimes the ‘Please try again later’ message. I have tried using a temp sl table as a stop off point but the locking still occurs, as the code is still run within the sl table. Does anyone have any tips as to avoid/reduce this? Thanks Col

Colin, it sounds to me like your problem is not ‘table locking’, but rather ‘deadlocking’. Table locking is a good thing – it enables very high throughput in a multi-user environment, while ensuring that the ACID properties of data consistency and persistence are met. Deadlocking, on the other hand, is a bad thing – it is related to locking, but only occurs when we programmers lock 2 or more resources (tables) in one process, and then in some other process, we lock the same 2 resources IN A DIFFERENT ORDER. The key to avoiding deadlocks is just this: be sure that tables are always locked in the same order. This can be a hard problem to solve – it is usually difficult to impossible to replicate the problem in a test environment, and users who experience the problem often are unable to describe their symptoms with sufficient precision. Hopefully, one user will report “My station was waiting on the Sales Line table”, while another user reports simultaneously “My station was waiting on the Document Dimension table”. That might give you a clue that those two tables are involved in a deadlock. If, in fact, your problem involves just the Sales Line and Document Dimension tables, your solution may be as simple as adding a DocDim.LOCKTABLE; at the beginning of your procedure. (See the Sales Line OnInsert trigger for an example of this code pattern.) Hope this helps (and please accept my apologies for the rambling reply).