May 10, 2015 at 11:48 pm
Dear friends.
I need in your opinion.
I use one russian ERP system. In this system documents like in other system make transaction in managerial and accounting systems. We operate with objects, and have no accsess directly to SQL Server and in transaction moment i do not see what really occuru on server. Previsiosly as i see for update they use standart schema(as they write in documentation) - update lock. In new version they wrote that this make a lot of unnesesary locks because of using serialized isolation level and changed this method. Now in object level they used another schema - immidiatly use select with exclusive lock with commited read(as they say on object level). And say that this is work better and decrease locking. For me interesting is using read commited with exclusive lock more effective than seriazable isolation level with update lock?
May 11, 2015 at 2:08 am
Both approaches sound like excessive locking to me. Why are they putting such stringent locks on the system to begin with? Most people are trying to avoid locks and the blocks that they sometimes create, hence so much reliance on read_committed_snapshot.
Without seeing the queries, query metrics, locks, blocking, etc., I can offer nothing but speculation as to why one would run faster than the other. It could be that serializable, which takes more locks at the start of transaction and maintains them across the transaction, is locking fewer pages than read_committed, but locking them longer? Just a guess since I can't see anything. I'd suggest getting a full set of metrics to understand the behaviors here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply