2

SQL Server has many ways of locking resource. I am trying to understand what make SQL Server pick what level of locks it will choose. I want to know when will it use Page or table lock over row lock?

Problem

I have a PHP application that uses transaction with every http request to ensure all queries are executed before a commit. One issue that is puzzling me is when many (5+) people use the application the app seems to be hanging (spinning for a long periods of time)! Nothing I can think of will cause such a behaviors except for database locks! The scenario that I am thinking it happening is that SQL Server is choosing to pick Page or Table lock over rowlock for some reason. I am trying to ensure that SQL Server is doing a row lock not Page or table lock. I am using an ORM so I can't use ROWLOCK hint in my queries.

Is there a way for me to run queries explain plan to see what lock level will be used?

Community
  • 1
  • 1
Junior
  • 11,602
  • 27
  • 106
  • 212
  • You can run SQL Profiler to see what locks SQL Server creates. I believe that there is a Lock:Escalation event class that would be of use to you. There are many other lock events that you can also look at. – Tom H Feb 29 '16 at 14:28
  • To pin it down to database, you should use SQL Server Profiler on the database at a time when the behaviour exhibits itself. You seem to have jumped to the conclusion that it is SQL Server though, which might not be correct. It's important to first locate WHAT is causing the delay, before asking WHY. I'd suggest also enabling logging on your webserver and analyse the logs there. – Witchfinder Feb 29 '16 at 14:32
  • @Witchfinder I agree with you 100% I am not sold that SQL Server locks is causing the problem but it seems to me that the most thing that makes since. Do you have a good approach to detect the actual issue? First, I thought it is an environment issue "issue with my web server" I tried to changed the environment but sill the same thing. I don't think the code will cause such an issue as there is no infinite loops that I can pin point that are causing the issue (even if there is an infinite loop I should be able to spot it when I run the application alone) – Junior Feb 29 '16 at 14:58
  • Perhaps start by monitoring disk queue, memory usage and CPU activity. Use SQL Server Profiler as well - that will at least let you either take SQL Server out of the running, or allow you to focus on what's going on in your DB. Does the site grind to a halt for all users, or just for the additional users? – Witchfinder Feb 29 '16 at 15:39
  • @Witchfinder it seems to be halting for everyone. I am going to take you advice and run another test while doing better monition and see if I can pin point the issue – Junior Feb 29 '16 at 15:44

1 Answers1

0

As you can see here there is no default granularity in lock modes.

In general the optimizer will choose the best course of action to handle this.

Could it be a case of livelock due to a long running transaction that leads to resource starvation?

You can also check here and here for information on lock escalation, but I'd suggest to not disable it for any table.

Community
  • 1
  • 1
Athanasios Kataras
  • 25,191
  • 4
  • 32
  • 61
  • Thank you for that information. I just had a quick read about livelock. When will this happen? Another thing that may be worth mentioning is when I run the application by myself I get no issue and everything runs smooth and pretty fast. Each transaction may have 50+ queries as the application is data driven system so I expect high number of short queries. The longest query runs in 15ms. most of the queries looks like this `SELECT * FROM table when pk = '100'` or SELECT * FROM table when pk = IN('100','101','102','103')` – Junior Feb 29 '16 at 14:54
  • Consider the following case: One user starts a transaction and locks TableA by updating a row and then user two tries to update same locked rows on TableA and a third and so forth. Then the process becomes serialized. If you have mane concurrent non stop requests then is could be that one of the users will never (till he times out) get the required resource. Selects do not lock tables for reading from others but lock while they run any updates / inserts. – Athanasios Kataras Feb 29 '16 at 15:04
  • Thank you for that example. for this application I would say this is an impossible case (each request is tied to a session (another table) the session id is associated with every table each user will be able to only update record associated with his/her session id. However, I have many table that will be read (read only) by all regardless of the session. – Junior Feb 29 '16 at 15:09
  • I'm guessing these are all lookup tables then. This should probably not affect them. – Athanasios Kataras Feb 29 '16 at 15:10