25112
asked on
handling table level locks for multiple inserts at the same time. (2012)
The below code is in a stored procedure. It is called by different users. when the #tempTABLE is huge (more than 160k), we see deadlocks that cause errors ProcessControl, when multiple users are running the stored procedure to
upload.
could you suggest what are the best standards that can be applied to handle this graciously?
BEGIN TRAN InsertBlockFromTemp
INSERT INTO [dbo].ProcessControl WITH (ROWLOCK) ( 9 COLUMNS )
SELECT 9 columns FROM #tempTABLE ORDER BY IDENTITYColumnPK_Column
COMMIT TRAN InsertBlockFromTemp
upload.
could you suggest what are the best standards that can be applied to handle this graciously?
BEGIN TRAN InsertBlockFromTemp
INSERT INTO [dbo].ProcessControl WITH (ROWLOCK) ( 9 COLUMNS )
SELECT 9 columns FROM #tempTABLE ORDER BY IDENTITYColumnPK_Column
COMMIT TRAN InsertBlockFromTemp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In this regard, am i understanding right that
1)READ UNCOMMITTED is better than READ COMMITTED? are these only STORED PROCEDURE level Isolation levels and not to affect outside it, right?
2)are there any other ISOLATION LEVELs to consider?
1)READ UNCOMMITTED is better than READ COMMITTED? are these only STORED PROCEDURE level Isolation levels and not to affect outside it, right?
2)are there any other ISOLATION LEVELs to consider?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>You will have to reset to READ UNCOMMITTED to Read Committed after you are done.
this will not help in this:
after 5000 rows, ROWLEVEL lock, defaults to TABLE LEVEL lock?
http://social.technet.microsoft.com/wiki/contents/articles/19870.sql-server-understanding-lock-escalation.aspx
this will not help in this:
after 5000 rows, ROWLEVEL lock, defaults to TABLE LEVEL lock?
http://social.technet.microsoft.com/wiki/contents/articles/19870.sql-server-understanding-lock-escalation.aspx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>> I use default SQL isolation levels without loosing any data
can you explain this pl?
do you start a stored procedure declaring a specific preferred IL?
and then before ending stored procedure reverting back to READ COMMITTED?
thank you.
can you explain this pl?
do you start a stored procedure declaring a specific preferred IL?
and then before ending stored procedure reverting back to READ COMMITTED?
thank you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it is the issue lcohan brought up: the destination table is locked.. NOLOCK can't help in this regard?
lcohan: what is the alternative to using explicit transactions? (to avoid this issue)