Advice on lock and lock timeout handling
Posted: Thu 16 Oct 2008 09:14
Hi Devart Team,
I thought I would ask here if you could provide some useful pointers on how to handle locks safely.
We have developed a multiuser system where we can have up to 5 people putting orders on at the same time. Due to the complexity of the orders, and the need to collate data from various places, it can take 5-10 seconds to complete the save. Hence the need for the transactions which you kindly helped me to get working in a previous thread.
I have found when we are very busy and sales people are saving orders regularly, we occasionallly get lock timeouts which is a bit of a worry as I gather this means the transaction might not rollback on the error.
I am pretty new to SQL and previously used Paradox which was a complete bodge up when it came to locking.
What I am looking for is some guidance on how to handle locking correctly so that it doesn't cause problems with the data. And I thought maybe with your expertise in SQL and your component you'd be the best to ask for some pointers
One thing I have done to try to cure this is to increase the locktimeout value in the connection options. But this kind of seems like a workaround rather than a proper fix. Do you think this is sensible to do this and will it help? By default, I think the timeout is 2 seconds. So with a possible 10 second save I guess is was exceeding the timeout. So upped the timeout to 20 seconds to give time for one save to complete. We are looking at reducing the time it takes also to do the save.
Is there are better way (I'm sure there is!) or this a valid method?
Is there anyway to check for a lock condition? Or how can I properly handle the exception when a lock timeout occurs?
Any advice or examples would be appreciated,
Many thanks,
Trevor
I thought I would ask here if you could provide some useful pointers on how to handle locks safely.
We have developed a multiuser system where we can have up to 5 people putting orders on at the same time. Due to the complexity of the orders, and the need to collate data from various places, it can take 5-10 seconds to complete the save. Hence the need for the transactions which you kindly helped me to get working in a previous thread.
I have found when we are very busy and sales people are saving orders regularly, we occasionallly get lock timeouts which is a bit of a worry as I gather this means the transaction might not rollback on the error.
I am pretty new to SQL and previously used Paradox which was a complete bodge up when it came to locking.
What I am looking for is some guidance on how to handle locking correctly so that it doesn't cause problems with the data. And I thought maybe with your expertise in SQL and your component you'd be the best to ask for some pointers
One thing I have done to try to cure this is to increase the locktimeout value in the connection options. But this kind of seems like a workaround rather than a proper fix. Do you think this is sensible to do this and will it help? By default, I think the timeout is 2 seconds. So with a possible 10 second save I guess is was exceeding the timeout. So upped the timeout to 20 seconds to give time for one save to complete. We are looking at reducing the time it takes also to do the save.
Is there are better way (I'm sure there is!) or this a valid method?
Is there anyway to check for a lock condition? Or how can I properly handle the exception when a lock timeout occurs?
Any advice or examples would be appreciated,
Many thanks,
Trevor