1

Thanks for taking the time to read my question. We have built a system using T-SQL that processes (large) quantities of data. When we start processing this data, this operation usually takes around 15 minutes to complete. During this time, for some reason, it seems that there is a lock on all objects in the database. To some extend I understand that I cannot query tables that are being written to. But also SQL Server gives me a time-out lock message when I refresh the list of tables for example (using F5 on the Tables node in the object explorer).

My code works based on the following template:

CREATE PROCEDURE Sample

as

SET NOCOUNT, XACT_ABORT ON

BEGIN TRY

BEGIN TRANSACTION

...

COMMIT TRANSACTION

END TRY

BEGIN CATCH
    BEGIN
        PRINT 'STORED PROCEDURE - Returned errors while processing'
        ROLLBACK TRANSACTION
    END
END CATCH

Now, when I start this I can perform SELECT WITH (NOLOCK) queries on any table. But I cannot view definitions on views, stored procedures or basically do any other kind of operation in the database.

I have already set the READ UNCOMMITTED isolation level setting, but that doesn't seems to make a difference.

Appreciate any help on this matter.

Kind regards,

sirokinl
  • 51
  • 6
  • Isolation levels affect how select statements work,those won't affect DDL,DML..Locking,blockings are big topics,so please search on how to resolve locking blockings and start from there – TheGameiswar Aug 03 '16 at 06:29

2 Answers2

0

The code that is in your TRY CATCH block performs a big amount of data update. Changing the isolation level will not resolve the issue with the locking.

What is best for you is to do these basic checks/changes:

  • Try divide the update/insert into smaller batches; things will move on smoother reducing the locking significantly.
  • Check the indexing of the tables taking part in the update query(ies) so that returning data is faster.
Igor Micev
  • 1,514
  • 1
  • 17
  • 23
0

There issue have nothing to do with isolation level it is the issue of Exclusive .

It is case of lock Escalation till database level.

Avoid Escalation do smaller batch TRANSACTIONs ...

sandeep rawat
  • 4,797
  • 1
  • 18
  • 36