transactions - How do I configure MS SQL server to avoid update lock (LCK_M_U) -

transactions - How do I configure MS SQL server to avoid update lock (LCK_M_U) -

i have ms sql server database containing several tables. each table has field 'sn'. several clients can work database, every client operates records own value of sn.

one type of client app legacy software written in python using pyodbc. understand, software can create long transactions - can execute update or insert statements , commit them several hours later. know, wrong that, not desirable modify software.

transaction isolation level set read_commited read_committed_snapshot set on.

everything work ok except next scenario:

first, legacy clent starts transaction updates records whith sn value, sn = 1 another client app tries update records sn value, sn = 2

this situation results in lck_m_u lock sec client: ridlock field = 1 pageid = 311 dbid=5 id=lock2776cf380 mode=x associatedobjectid=...many numbers here...

although 2 clients utilize different sn values results in lock. suspect happens because of sql sever locks not particular rows, page (a collection of rows, guess).

according question (is possible forcefulness row level locking in sql server?) row locks not guaranteed because of lock escalation.

i considered setting transaction isolation level 'read uncommitted', i'm not sure right way.

is there way configure ms sql server resolve lock , allow 'simultaneous' updates of different rows?

partitioning on sn might best way. there method can employ, utilize caution. can disable lock escalation enabling trace flag 1211. however, trace flag disables lock escalation globally in instance of sql server. lock escalation serves useful purpose in sql server maximizing efficiency of queries otherwise slowed downwards overhead of acquiring , releasing several thousands of locks. lock escalation helps minimize required memory maintain track of locks. memory sql server can dynamically allocate lock structures finite, if disable lock escalation , lock memory grows big enough, attempts allocate additional locks query may fail.

sql-server transactions