Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Table Lock...

  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Table Lock...

    Hello Friends,
    I am having a VB application running for the SQL SERVER DB. The VB application is installed on the multiple of PCs in the network. Now when I am trying to fetch the same from all the different PCs simultaneously, its amazingly fast. But the issue comes when I am trying to update the same table (but different rows) from the different PCs simultaneously. The time taken is directly proportaional to the number of users. I am not getting what could be the problem? Can any one suggest me the approch? Is it some related to table / row / page locking? As all the connections are trying to update on the same table. I checked the isolation level. Its default, "READ COMMITTED". Kindly suggest...




    Thanks in advance........
    Rahul Jha

  2. #2
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Here is the code that every user is trying to execute........ The example below is for a particular user.....


    BEGIN TRANSACTION UPDATE Symp_QuoteLineItemDisplaySeq SET QuoteLineItem_ID=102088101 WHERE QuoteRevision_ID=1020817 AND DisplaySEQ=1 COMMIT TRANSACTION
    BEGIN TRANSACTION UPDATE Symp_QuoteLineItemDisplaySeq SET QuoteLineItem_ID=1020813211 WHERE QuoteRevision_ID=1020817 AND DisplaySEQ=2 COMMIT TRANSACTION
    BEGIN TRANSACTION UPDATE Symp_QuoteLineItemDisplaySeq SET QuoteLineItem_ID=1020813212 WHERE QuoteRevision_ID=1020817 AND DisplaySEQ=3 COMMIT TRANSACTION
    BEGIN TRANSACTION UPDATE Symp_QuoteLineItemDisplaySeq SET QuoteLineItem_ID=1020813213 WHERE QuoteRevision_ID=1020817 AND DisplaySEQ=4 COMMIT TRANSACTION
    BEGIN TRANSACTION UPDATE Symp_QuoteLineItemDisplaySeq SET QuoteLineItem_ID=1020813214 WHERE QuoteRevision_ID=1020817 AND DisplaySEQ=5 COMMIT TRANSACTION
    BEGIN TRANSACTION UPDATE Symp_QuoteLineItemDisplaySeq SET QuoteLineItem_ID=102088102 WHERE QuoteRevision_ID=1020817 AND DisplaySEQ=6 COMMIT TRANSACTION

    Thanks,
    Rahul Jha

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    post the ddl including the constraints
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2005
    Posts
    122
    Of you do of course have the appropriate indexes so the update statements won't have to do a full table/index scan?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    When you do the update, is any blocking shown by the system stored procedure sp_who2?

  6. #6
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Here is the DDL for the table.....
    CREATE TABLE [dbo].[Symp_QuoteLineItemDisplaySeq](
    [QuoteLineItem_ID] [bigint] NULL,
    [DisplaySeq] [int] NOT NULL,
    [QuoteRevision_ID] [bigint] NOT NULL,
    CONSTRAINT [PK_Symp_QuoteLineItemDispalySeq] PRIMARY KEY CLUSTERED
    (
    [QuoteRevision_ID] ASC,
    [DisplaySeq] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    USE [Symphony_Backbone]
    GO
    ALTER TABLE [dbo].[Symp_QuoteLineItemDisplaySeq] WITH CHECK ADD CONSTRAINT [FK_Symp_QuoteLineItemDispalySeq_QuoteLineItem_ID] FOREIGN KEY([QuoteLineItem_ID])
    REFERENCES [dbo].[Symp_QuoteLineItem] ([QuoteLineItem_ID])
    No, there were no blocking. Checked that sp_who2


    Thanks,
    Rahul Jha

  7. #7
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Here is the DDL for the table.....

    CREATE TABLE [dbo].[Symp_QuoteLineItemDisplaySeq](
    [QuoteLineItem_ID] [bigint] NULL,
    [DisplaySeq] [int] NOT NULL,
    [QuoteRevision_ID] [bigint] NOT NULL,
    CONSTRAINT [PK_Symp_QuoteLineItemDispalySeq] PRIMARY KEY CLUSTERED
    (
    [QuoteRevision_ID] ASC,
    [DisplaySeq] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    USE [Symphony_Backbone]
    GO
    ALTER TABLE [dbo].[Symp_QuoteLineItemDisplaySeq] WITH CHECK ADD CONSTRAINT [FK_Symp_QuoteLineItemDispalySeq_QuoteLineItem_ID] FOREIGN KEY([QuoteLineItem_ID])
    REFERENCES [dbo].[Symp_QuoteLineItem] ([QuoteLineItem_ID])
    No, there were no blocking. Checked that sp_who2


    Thanks,
    Rahul Jha

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you think you might want a better primary key, or an index?

    The cardinality must be very low on that
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Alright then. Let's have a look at what the update is actually doing, then. bring up profiler, and see what the CPU and IO information is for the update query. It should be similar to the select query information, provided they are both trying to use the same access path. If the numbers are off by a factor of 2 or more, then there is a problem with the update query. Judging from the queries given, I would say they should rip right on through with little to no problem. If you ran them in Query Analyzer, there would likely be no delay at all. The application may be doing something else behind the scenes, though.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How can the cardinality on a PK be low?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The cardinality on a PK has to be high, but the cardinality on a PK index can be very low.

    One example where they might be different is if you have a surrogate key column (a GUID or an IDENTITY) and an active/inactive flag column. The combination of the two columns has a cardinality of 1. An AK (Alternate Key) where the surrogate key comes first would have a cardinality of 1, so its index would also have a cardinality of 1. An AK where the active flag came first would probably have a very large cardinality (because the leftmost column isn't very selective), and so would its index.

    -PatP

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The cardinality is based on the left most column only of a composite index and not on all the columns comprising that index?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The way that SQL Server has traditionally measured cardinality only considers the left most column.

    -PatP

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ta

  15. #15
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    why begin tran..commit if you are not checking for errors to commit/roll back ?

Posting Permissions

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