• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

Lock Contentions

If there are some locks only two table, what could be solutions to lessen the locks?

  • 3
2 Solutions
check the code and transactions accessing the tables...  if you can give more detail abou lock occurence we may guide you more...
anushahannaAuthor Commented:
Ok. started with DMV. It suggests couple of index for the tables. Will that help the locking?

to get transactions hitting the tables, is profiler the tool to go?
anushahannaAuthor Commented:
sorry for the delay.

i used the dmv's to get the info. would I use the profiler to get the info you are referring.

thanks tigin44
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> some locks only two table

I have two approached from my side and would recommend the first approach..

First Approach:

1. Use Profiler
2. Add two Column Filters one for Textdata column LIKE your first table and Second filter for Textdata column LIKE your second table.
3. Make sure you have included the Lock:Deadlock, Lock:Deadlock Chain, Deadlock Graph and Lock:Timeout under Locks section.
4. Analyze all the queries or operations that is happening on those two tables which are responsible for this deadlock.
5. Tune out the queries involved either by rewriting your queries or by creating appropriate indexes

Second Approach: ( if not able to tune better using first approach)

1. Find out where those queries are used in your application module.
2. Check out the transactional level maintained and the code over there..
3. Check whether any application level tuning or alternative approach can be done to improve the application level.

This should help you remove lock contentions over those two tables..
Similar approach can be done on all modules as required..
anushahannaAuthor Commented:
Thanks for the detailed and clear explanation.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now