Answered by:

Locks in SQL Server 2008

Question

i've got a problem with locks. i have a VB5 app working with SQL SERVER 2008( last 3 months). this app have 40 users working on it. in last one year i've locks almost every day and mostly at the same time...10am and 6pm. The locks are always on the same table, simple table that have only one row with sequence number. A Stored procedure increments that number.

sometimes we got 3 weeks with no locks....after that, they returns every day....sometimes 2 times for day.

There is a possiblity that the problem can be in domain server? or in other configuration "outside" of sql server?

All replies

This is a very typical scneraio which can potentically create blocking in the Database. If you have found the table which is getting into a lock You need to look at the objects that access the central table this is being used for sequence generation. Make sure you handle the transactions well so that you can avoid locks.

If the Sequence is not alphanumeric you should use Identity to be very safe. If you give some more details it would be really useful.

After i find the locks i run the SP_WHO2 to find who-is-blocking-who and when i kill the process it's in the top of the list, nothing happens, other process pass to top of the list and i the "solution" is restart de sql server service.

remember the last call has ended yet and this call interfers with the previous once.

Now 2 waits for 1 and in the mean while if there are other calls that hit the same IF condition as in case 1, it gives raise to a chain of blocked transactions. Thus when you try to kill one the next SPID gets blocked.. finally SQL services requires a recycle. But this is not safe as suring re-cycle there could be data loss due to un commited transactions.

The problem is with the design of the table, one single row and there are multiple columns, instead you should have one row for each Key and store the seq as value; with multiple rows we would be able to narrow down and force rowlock instead of all rows being affected. Currently even when you are trying to update different columns at the same time, since there is only 1 row, the row gets locked (I have given the repro steps in the bottom of the thread) and connections are blocked. However this is just one solution you should consider the efforts as per impact..