January 7, 2011 at 12:42 pm
Hello,
I know I can implement Row Locking on SQL Server using the WITH (ROWLOCK) query hint. But I'm developing a project with Entity Framework, and we have several situations where row locking is very important to us due to concurrency issues, and it's not possible to pass Query Hints when using the Entity framework. My question is: is there a way to set the whole database to enforce Row Locks by default on every transaction?
Tks
January 7, 2011 at 1:07 pm
Not that I know of.
Entity Framework doesn't allow you to control the options on the connection?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2011 at 1:13 pm
I can tap into the Entity Framework to get the connection, and then use "real sql" with query hints to enforce the row lock, but that kinna beats the purpose of using the Entity Framework 😉
Since I would like to use row locks with most of my updates/deletes, I'd be better off with a database level configuration to always enforce it on any transaction.
January 7, 2011 at 1:26 pm
If I recall the chapters in Kalen's book correctly, there is a strong bias to row locking, but, no you can't really guarantee the locking type, and to a degree, you don't want to. What happens when you do something that affects 8-10 pages worth of data, which represents a very large number of rows, and it put's a lock on each row, which chews up a gigantic amount of memory as opposed to the 8-10 locks for the pages? There's a reason why it does multiple types of locks.
"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
January 7, 2011 at 1:31 pm
Well, good point there. We need row locks for several critical tables updated by our application server, to serialize access to them, and avoid deadlocks due to some denormalized columns (for reporting and performance). I'll probably encapsulate these updates on stored procedures, and call them on Entity Framework.
Tks
January 7, 2011 at 1:56 pm
pascalbjr (1/7/2011)
Well, good point there. We need row locks for several critical tables updated by our application server, to serialize access to them, and avoid deadlocks due to some denormalized columns (for reporting and performance). I'll probably encapsulate these updates on stored procedures, and call them on Entity Framework.Tks
I'd look at a couple of things. First, you can use Serializable locking. It's not great in some areas, but it will avoid deadlocks... just introduce possibly serious blocking. Second, check out Read Committed Snapshot. This will allow for locking, but still allow for access to the data while it's locked. This can prevent some deadlocking and completely eliminates blocking.
"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
January 7, 2011 at 2:01 pm
I'll look into it! Tks so much for your time and attention!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply