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
There are several insert into statements.some of them on the same tables again and again.
any help would be greatly appreciated.
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.
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.
create procedure USP_RESOURCEREORDER
@id as int,
@tablename as varchar(100)
declare @strsql as varchar(1000)
@strsql='insert into ' + @tablename + 'select * from tbl'
@strsql='insert into #tmp1 select * from ' + @tablename
@strsql='insert into #tmp2 select * from ' + @tablename