1

I have MS SQL server with a database containing several tables. Each table has a field 'SN'. Several clients can work with this database, but every client operates only with records with its own value of SN.

One type of client app is a legacy software written in python using pyodbc. As I understand, that software can create long transactions - it can execute UPDATE or INSERT statements and commit them only several hours later. I know, that it is wrong to do that, but it is not desirable to modify that software.

Transaction isolation level is set to READ_COMMITED with READ_COMMITTED_SNAPSHOT set to ON.

Everything work ok except the following scenario:

  1. First, legacy clent starts transaction which UPDATEs records whith its SN value, say SN = 1
  2. Another client app tries to UPDATE records with its SN value, say SN = 2

This situation results in LCK_M_U lock for second client: ridlock field = 1 pageid = 311 dbid=5 id=lock2776cf380 mode=X associatedObjectId=...many numbers here...

Although those two clients use different SN values this results in a lock. I suspect that this happens because of SQL sever locks not particular rows, but a page (a collection of rows, I guess).

According to SO question (Is it possible to force row level locking in SQL Server?) row locks are not guaranteed because of lock escalation.

I considered setting transaction isolation level to 'READ UNCOMMITTED', but I'm not sure it is the right way.

Is there any way to properly configure MS SQL server to resolve this lock and allow 'simultaneous' updates of different rows?

Community
  • 1
  • 1
Roman
  • 515
  • 5
  • 16
  • you could partition the table(s) based on SN. this will remove locking conflicts between multiple clients. – ughai Apr 07 '15 at 10:36
  • Not sure it is good thing to do. Most of the tables contain only one record with particular SN (it can be unique along entire table like a primary key), and there can be a big number of different SNs. Also according to https://msdn.microsoft.com/en-us/library/ms190787.aspx there are limitations on the number of partitions. – Roman Apr 07 '15 at 12:34

2 Answers2

0

Partitioning on SN might be the best way. There is another method that you can employ, but use with caution. You can disable lock escalation by enabling trace flag 1211. However, this trace flag disables all lock escalation globally in the instance of SQL Server. Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail.

Raj
  • 10,653
  • 2
  • 45
  • 52
  • Thanks for your suggestion. Regarding partitioning - please see my first comment below original question. Disabling lock escalation is not a way to go, because server instance also services another databases and such a change might be not desired. – Roman Apr 07 '15 at 12:36
0

I found a solution which seems to work ok for me. It is described in this topic on microsoft forum. The idea is to create indexes for SN field in the tables like this: 

CREATE INDEX IX_SN ON TABLE_NAME(SN)
Roman
  • 515
  • 5
  • 16