Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    May 2006
    Posts
    19

    Unanswered: RowLock blocking other rows

    Hi,

    my problem is that locking 1 row in a table prevent other application instances to lock other rows in the same table.

    The first application executes the following select statement to gain an update lock before editing data:
    SELECT * FROM FL_KONTROLLE WITH (UPDLOCK,ROWLOCK)
    WHERE INSTALLATION_ID=? AND KONTROLLE_ID=?
    The 2 columns in the where clause are the primary key columns of that table.

    While the transaction is still open (no commit executed), a second instance of the same application executes the same statement, using different parameter values. In my understanding, the second application should be able to to gain the lock to its row because it uses different values for the primary key columns, thus requesting locks for a different row.
    But in result, the second instance is blocked, waiting on a lock. According to the monitoring tools, the lock in question is an "KEY" "Update" lock on the primary key index object for that table. That lock is held by the first application, and required by the second one.

    If I'm doing the same selects with another table, it works and both application instances gain the requested locks.

    We are using a MSSQL Server 2000 accessed thru the microsoft jdbc driver.

    Any ideas what can cause (and solve) that? Are there hidden table options to control the row locking behavior?

    Holger

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - don't issue locking hints on your select statements.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2006
    Posts
    19
    The main goal of "select for update" is to make sure that other connections can not update the locked row before the locking connection commits or rollbacks the transaction. That behavior is absolutely essential here because otherwise a second users could accidentally override changes made by the first user.

    If it is possible to set row locks without the locking hints, tell me how.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You could add a timestamp to the table - this will be able to tell you if there has been a change since you last selected the data.
    George
    Home | Blog

  5. #5
    Join Date
    May 2006
    Posts
    19
    The table allready has a version column, but that is no solution.

    What should a user do if the applications tells him that the row has changed by another user since he began to edit it? Should he discard all his work? Due to the complexity of the data model below that single row, it could easily take an hour until he has finished entering all data that need to be saved at once.

    As said in the previous post, it is REQUIRED to set a lock before editing a row.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - just read further re your drivers.

    So to understand - if you do EXACTLY the same process on other tables, everything works fine?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah! So that explains why you are acting so heavy handed on locking.

    I can't replicate this in SSMS. Row locks on PK values do not interfere with other row locks. Is it to do with the provider? For example, are you sure it is not taking a key range lock as that would explain the behaviour?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Uh? The forum has knocked my posts out of order.....
    #6 should read "Ah!...."
    #7 "Sorry - ...."
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Aside from the driver, I am playing around in SQL 2005. AFAI remember, this locking behaviour shouldn't have changed between versions, but it is a while since I read up on any changes....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2006
    Posts
    19
    The activity monitor of the SQL Server Management Studio lists the following locks for the first application (the one holding the lock):

    type, mode, state, index, resource
    PAG, IU, GRANT, PK_FL_KONTROLLE, 1:281
    TAB, IX, GRANT,,
    KEY, U, GRANT, PK_FL_KONTROLLE, (02000237b073)

    And for the second application (the blocked one):
    type, mode, state, index, resource
    PAG, IU, GRANT, PK_FL_KONTROLLE, 1:281
    TAB, IX, GRANT,,
    KEY, U, WAIT, PK_FL_KONTROLLE, (02000237b073)

    My interpretation of the above is that the second application is waiting on a lock on the primary key of the table (PK_FL_KONTROLLE is the name of the primary key constraint).

    If I rollback the first application, the second one gains the waiting lock and the following additional lock:
    KEY, U, GRANT, PK_FL_KONTROLLE, (02006b61d241)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you use profiler to check these two statements, in particular telling us the key values submitted.

    The results are saying they are both attempting to lock the same row.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    INSTALLATION_ID and KONTROLLE_ID are the only two PK columns right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    May 2006
    Posts
    19
    The java debugger showed the parameter values 1, 215571 for the first application instance and 1, 216271 for the second application instance.

    So, I'm sure that I access 2 different rows.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A question I asked and got lost earlier:
    "So to understand - if you do EXACTLY the same process on other tables, everything works fine?" And by exactly the same, I mean passing PK parameters via the java driver.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    May 2006
    Posts
    19
    Quote Originally Posted by pootle flump
    INSTALLATION_ID and KONTROLLE_ID are the only two PK columns right?
    Yes, the index only contains that 2 columns. The management studio generates the following create statement for the primary key:

    ALTER TABLE [dbo].[FL_KONTROLLE] ADD CONSTRAINT [PK_FL_KONTROLLE] PRIMARY KEY CLUSTERED
    (
    [INSTALLATION_ID] ASC,
    [KONTROLLE_ID] ASC
    ) ON [PRIMARY]

    Checking the generated create statement for the primary key of the table locking is working on, results in the same statement with other names.

Posting Permissions

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