Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Location
    Riga, Latvia
    Posts
    7

    Unanswered: 6.5 reasons for slow inserts and blocking

    Hallo!

    I have a table in sql 6.5SP5a with 2`130`698 rows. The table has a primary
    key on identity column and an insert trigger that updates the inserted row
    according to some logic. Recently I've noticed that some amount of
    intermittent blocking is occuring. It happens when the stored procedure that
    inserts a new row in the table is receiving simultaneous calls. The
    procedure looks something like this:

    create procedure pr_insert_or_update
    @mode tinyint,
    @id int,
    @param1 int,
    @param2 datetime,
    @param3 varchar(3)

    as

    if @mode=0
    begin

    insert into Table(column1, column2, column3, column4, column5)
    values(param1, param2, param3, user_name(), host_name())

    select @@identity as identity_column,
    column_updated_by_the_trigger
    from Table where identity_column=@@identity

    end

    else
    begin

    update table set column1=@param1, column2=@param2,
    column3=@param3, column4=user_name(), column5=host_name()
    where identity_column=@id

    select @id

    end

    The syprocesses waittype, status, and cmd entries for the suid at the head
    of a blocking chain are usually 0x0000, runnable, and SELECT accordingly.
    Although cmd is SELECT, DBCC OPENTRAN for the (user) database shows that the
    oldest open transaction is "insert" and it belongs to the suid at the head
    of a blocking chain. Also the exclusive blocking page locks are held.
    I used the blocker script from Microsoft to gather this information. Still,
    I'm stuck with finding out what is the cause of blocking since the waittype
    is usually 0x0000 and DBCC PSS doesn't return any result in my case.

    Any ideas on what could have caused/how to resolve blocking? Could it be
    slow network when users are holding page locks for too long? Maybe I should
    consider switching on "insert row lock" for the Table?


    Many thanks!

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Two things come to mind.

    1) It appears to me that you have a trigger on the table, column_updated_by_the_trigger, so the INSERT transaction is actually a couple of SQL statements linked together into 1 transaction. The INSERT plus any SQL statements within the trigger. So locks on the INSERT are being held until the trigger has been completed.

    2) Once the trigger has completed, the statement will auto commit or auto rollback, I say this because I see no BEGIN TRAN, and I'm assuming the application that calls this has not issued it and you have the default settings, Implicit Transactions set to false.
    So when the INSERT finishes and you have released the locks the next process that is in-line to execute the INSERT statement will happen, however the first process now will execute the SELECT, but will be locked out by the second process doing the INSERT.
    Two options to solve this (a) Place a BEGIN TRAN prior to the INSERT and a COMMIT TRAN after the SELECT or (b) Place the hint (nolock) after the table name in the SELECT.
    MCDBA

Posting Permissions

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