SQL Server Performance

Page lock on a single record fetch and update

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by WingSzeto, Feb 25, 2009.

  1. WingSzeto Member

    I recently encountered a deadlock. The queries involved and the table schema are listed at the end of the email. The row lock and page lock are enable for all indexes. tblSignIn currently has 7.8 million records. Both of the queries caused page lock when deadlock happened. I don't quite understand because both queries deal with single record. I would expect a key lock instead. Anyway, I update statistics on that table daily and reindex it once a month. tblSignIn is heavily used table but this deadlock doesn't happen that often, maybe 4~6 times a week. Sometimes both queries ran (< 1 sec) and sometimes ran 4 seconds, either way the deadlock would happen in either time. I have tried to put the fillfactor (90%) to the index involved key2 to minimize fragmentation since the Update statement (see the query at the end of this email) is updating key2 which is part of the index. But it didn't help much. The profiler trace also indicates that "Parallel query worker thread was involved in a deadlock" and I am suprised by it because it is a simple statemetn and it shouldn't be using parallelism. Anyway, I tried the 'Option (maxdop 1)' for both queries but caused more blocking. It may be because I didn't set the maxdop to the right number. Our server is a dual quad server. If Option maxdop is the solution to my deadlock problem, what is the maxdop number I should set to? We are on SQL 2008 64 bit (CU3) and windows 2008 64 bit. However, this deadlock happened on SQL 2005 32 bit before we upgraded to SQL 20008 64 bit. My questions are:
    1. Why the queries triggered a page lock?
    2, If the upate statement which updates a field that is part of the index is a concern for my problem, is there a diffferent way to do itt?
    3. if the parallel query thread is the problem, what should I try (ie. maxdop = ?) ?
    Any pointers on this matter is very much appreciated.
    SELECT Sign_ID, Emp_ID, Dept_ID, Access_ID, Status, Expiration, AccessCode, Key1, Method, ConfidentCode, ParamsFROM tblSignInWHERE key2 = ‘somethingsomething’ UPDATE tblSignIn SETKey1 = 'anythinganything',Key2 = 'whatwhatwhat'
    WHERE Sign_ID = 456852
    The table schema is as follows:CREATE TABLE [dbo].[tblSignIn](
    [Sign_ID] [int]
    IDENTITY(1,1) NOT NULL,[Emp_ID] [int]
    NOT NULL,[Dept_ID] [int]
    NOT NULL,[Access_ID] [int]
    NOT NULL,[Expiration] [datetime]
    NOT NULL,[AccessCode] [int]
    NOT NULL,[Method] [varchar]
    (20) NOT NULL,[ConfidentCode] [varchar]
    (20) NOT NULL,[Key1] [varchar]
    (255) NULL,[Key2] [varchar]
    (255) NULL,[Status] [tinyint]
    NOT NULL,[CreateDate] [smalldatetime]
    NOT NULL,[ChangeDate] [smalldatetime]
    NULL,[Change_ID] [int]
    NULL,[ChangeNote] [varchar]
    (255) NULL,[Params] [varchar]
    (4000) NULL,
    CONSTRAINT [PK_tblSignIn] PRIMARY KEY CLUSTERED ([Sign_ID]
    ASC))
    ON [PRIMARY]CREATE
    NONCLUSTERED INDEX [IX_tblSignIn_01] ON [dbo].[tblSignIn] ([Key2]
    ASC)CREATE
    NONCLUSTERED INDEX [IX_tblSignIn] ON [dbo].[tblSignIn] ([Expiration]
    ASC)

    Wingman
  2. Adriaan New Member

    Does your actual code have a SELECT query, right before the UPDATE on the very same row? Why?
    Are you updaing just one row, or could the update affect multiple rows?
    With MAXDOP, the only number that prevents unwanted parallellism is 1.
    Perhaps the indexes or statistics need maintenance.
  3. WingSzeto Member

    Thank for reply.
    The two queries are in two seperated areas in the actual code. The select will come first and then the update happens after that (at least 10 seconds later or more). So it is not possible for these two statements touch the same row at the same time. Yes, they both deal with single row. On the other hand, we do have three web servers which could fire this code at the same time but the key2 and sign_ID would be different from each statements. So it is possible that multiple same select statement with different key2 hit the database at the same time and the row that they are retreiving could be on the same page. But the row that update statement is touching is on a different page at least that is what i found from the profiler trace for the deadlock. I can see that the mutliple Select statements which are touching the same page may lock each other out if each triggers a page lock but I just don't understand why a page lock happens for a single row fetch.
    As I mentioned in my email, I have done statistics update (and with full scan) on that table daily and reindex the entire db monthly. Do you mean I need to do it more frequently?
    wingman
  4. Adriaan New Member

    What is the point of that SELECT statement?
    Unless it is assigning values from the row to variables, I can only struggle to think why you would need an isolated SELECT statement halfway through a stored procedure.
    You're updating an indexed column, Key1, which is VARCHAR(255) - not an ideal candidate for an index. If it has repeating values, consider using a substitute key referring to a lookup table.
    Perhaps you could change the PK to a unique clustered index, and INCLUDE the Key1 column, instead of having a separate index.
  5. eyuksek2000 New Member

    Hello,
    We have the same deadlock problem as you did long time ago :) I was wondering whether you have found the solution to that problem!
    I know it has been a long time but I am stuck! I have a select statement and then and update at the same row and I going into deadlock at the same spot most of the time.
    Thank you in advance.

    Eray

Share This Page