Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    80

    Unanswered: Transaction locking tables

    Hi all,
    I am writing an sp which includes insert and update statements.
    sp is working fine.
    But when I tried to make it as a single Transaction its not working(waiting indefinetly at second insert statement).tables are getting locked.
    what could be the possible reason for tables getting locked indefinetly.
    I Tried with
    set transaction isolationlevel serializable
    option.
    There are several insert into statements.some of them on the same tables again and again.
    any help would be greatly appreciated.
    Thanks.

  2. #2
    Join Date
    Mar 2004
    Location
    West London
    Posts
    34
    Can we see the code?

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Few tips:
    Instead, try to do all your reads first, then perform all of the database changes (UPDATES, INSERTS, DELETES) near the end of the transaction.
    Don't ever pause a transaction to wait for user input.
    Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure.
    Use sp_who and sp_who2 to see the current activity.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Mar 2004
    Posts
    80
    thanks for the reply,
    I have been creating some temp tables in my sp and inserting data into those temp tables could it be one of the reasons for the problem.
    I have been restarting the server each time tables are locked indefinetly.
    any other possible solutions.
    <code>
    create procedure USP_RESOURCEREORDER
    @id as int,
    @tablename as varchar(100)
    as
    create #tmp1(......)
    create #tmp2(......)
    declare @strsql as varchar(1000)
    begin tran
    @strsql='insert into ' + @tablename + 'select * from tbl'
    exec(@strsql)
    if@@error<>0
    goto undo
    @strsql='insert into #tmp1 select * from ' + @tablename
    exec(@strsql)
    if@@error<>0
    goto undo
    @strsql='insert into #tmp2 select * from ' + @tablename
    exec(@strsql)
    if@@error<>0
    goto undo
    ------------
    -----------
    -----------
    commit
    return 1
    undo:
    rollback
    return -1

    </code>

Posting Permissions

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