Professional Web Applications Themes

Locking questions ... - Microsoft SQL / MS SQL Server

My understanding was that if the isolation level on a connection was READ COMMITED that a select statement would request locks with mode shared. But when I run a simple select * from the query yzer, the locks generated are intent-share and intent-exclusive. These intent-exclusive locks seem to be setting us up for occasional deadlocks in our application. If they were simply shared locks, the problem wouldn't be happening. Also, in the Profiler, I can get info on object id on locks aquired but the lock released info says nothing, so I can't really tell the duration of a particular ...

  1. #1

    Default Locking questions ...

    My understanding was that if the isolation level on a connection was READ
    COMMITED that a select statement would request locks with mode shared. But
    when I run a simple select * from the query yzer, the locks generated
    are intent-share and intent-exclusive. These intent-exclusive locks seem to
    be setting us up for occasional deadlocks in our application. If they were
    simply shared locks, the problem wouldn't be happening.

    Also, in the Profiler, I can get info on object id on locks aquired but the
    lock released info says nothing, so I can't really tell the duration of a
    particular lock. Any hints?

    Thanks,

    Bob Castleman
    SuccessWare Software


    Bob Guest

  2. #2

    Default Locking questions ...

    In the book "inside microsoft sql 7.0" isbn:
    0-7356-0517-3 under READ COMMITTED it says:
    "Also, your transaction must put share locks (at minimum)
    on the data that will be visited." So it can be more then
    share locks.

    To solve your deadlock problem you need to figure out wich
    ressources causes the dealock by using a trace flag.
    Start your sql with traceflag SQLSERVER.EXE -T1204

    You can also try a less restrictive isolation level, but
    don't do that until you know for sure what is causing the
    dealock and if it can be avoided.

    If you MUST change the isolation level, go for uncommited
    read, which has a lot of drawback but solves the dealock
    issue QUICKLY. The best is to understand the dealock issue
    with the trace flag and sp_who2.
    sp_who2 returns syslocks table information.

    Hope this helps.
     
    connection was READ 
    mode shared. But 
    locks generated 
    exclusive locks seem to 
    application. If they were 
    locks aquired but the 
    the duration of a 
    Laurent Guest

  3. #3

    Default Re: Locking questions ...

    Bob,

    A Select that is not wrapped in a transaction or anything funny will not
    hold locks on rows that it has already read. So the scenario you posed does
    not seem correct. I can see the Update blocking the read from continuing on
    but the rows that were already read should not affect the updates ability to
    continue unless the locks are at the page or table levels. Is the select
    using a proper index to find the rows or is it doing a table scan? Are you
    sure these are real deadlocks and not just normal blocking? Are you
    actually getting the error messages from sql server stating that it has
    resolved a deadlock. I would concentrate on seeing if you can optimize
    this refresh and make sure it isn't doing anything "funny", other than plain
    selects. If the selects are escalating to table level you can see if adding
    a ROWLOCK or PAGLOCK hint helps. But if it is not using row locks then you
    might want to see why.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Bob Castleman" <nomailhere> wrote in message
    news:phx.gbl... 
    would 
    changes 
    the 
    just 
    > locking". [/ref]
    you [/ref]
    > READ [/ref]
    > But [/ref]
    > generated [/ref][/ref]
    seem [/ref]
    > were [/ref][/ref]
    but [/ref][/ref]
    of 
    > >
    > >[/ref]
    >
    >[/ref]


    Andrew Guest

  4. #4

    Default Re: Locking questions ...

    It is definately a deadlock, as I have trapped the deadlock chain SPIDs in
    the Profiler. It is also definately happening in the the sequence I've given
    (also determined through the Profiler), though you are most likely correct
    that my precise reasoning is flawed.

    It would be best to turn on trace flag 1204 but the sys admin doesn't want
    to stop the service. He says it is tricky doing it on clustered servers.
    I'll press for that as well as look into indexing and optimizing on the
    refresh.

    Thanks.

    Bob

    "Andrew J. Kelly" <com> wrote in message
    news:phx.gbl... 
    does 
    on 
    to 
    you 
    plain 
    adding 
    you 
    > would [/ref]
    idea 
    > changes [/ref]
    refresh, [/ref]
    This 
    > the 
    > just [/ref]
    the [/ref]
    substantially 
    > > locking". [/ref][/ref]
    How [/ref]
    > you 
    > > READ [/ref][/ref]
    shared. 
    > > generated [/ref]
    > seem [/ref][/ref]
    they [/ref]
    > but [/ref]
    > of 
    > >
    > >[/ref]
    >
    >[/ref]


    Bob Guest

  5. #5

    Default Re: Locking questions ...

    If you have a profiler trace then you should be able to see step by step
    what statements the two spids involved in the deadlocks are doing and get a
    better understanding of how they may have come into that situation. The
    main reason you get deadlocks is that the same tables are accessed in
    different orders by 2 different users. This is usually the result of some
    type of modifications on both ends but you may want to see if how things are
    addressed by the 2 spids.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Bob Castleman" <nomailhere> wrote in message
    news:phx.gbl... 
    given 
    > does [/ref]
    continuing [/ref]
    ability [/ref]
    select 
    > you 
    > plain 
    > adding 
    > you 
    > > would [/ref]
    > idea 
    > > changes [/ref]
    > refresh, [/ref][/ref]
    deadlock [/ref]
    > This [/ref][/ref]
    in 
    > > just [/ref][/ref]
    deadlock [/ref]
    > the [/ref]
    > substantially [/ref]
    > How [/ref][/ref]
    have [/ref][/ref]
    was [/ref]
    > shared. [/ref][/ref]
    locks [/ref]
    > they [/ref][/ref]
    aquired [/ref][/ref]
    duration 
    > >
    > >[/ref]
    >
    >[/ref]


    Andrew Guest

Similar Threads

  1. Locking a PDF
    By graffiti in forum Adobe Acrobat Macintosh
    Replies: 3
    Last Post: December 1st, 08:59 PM
  2. Need Help with Locking
    By CFRAM in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: July 28th, 03:16 AM
  3. DB2 locking UDF dll
    By W Gemini in forum IBM DB2
    Replies: 2
    Last Post: August 7th, 03:23 AM
  4. locking?
    By Will in forum Windows Setup, Administration & Security
    Replies: 2
    Last Post: July 25th, 03:23 AM
  5. Locking ?
    By Will in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 24th, 07:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139