Always rowlock?

Get our Free Newletter

Don't you have the time to be online here at DUG every day? So how do you follow what is going on in the Dynamics industry and community?
If you subscribe to the DUG Newsletter then you can relax! We will make sure that you don't miss the big news!

Check out our
Newsletter Subscription Center
page a complete list of our different newsletters.

This post has 2 Replies | 0 Followers

149 Posts
1,457 Points
Joined: 2001-7-21
Last Online:
2007-9-7 18:28
Location: NJ, USA
thaug Posted: 2005-11-30 1:50 | Locked
I noticed with 4.0 SP1 (may be in other versions, I don't know), that when using SQL 2000, an option in Alter Database is Always Rowlock. Currently, it is not checked off.

I'm wondering what this really will do. I've seen other posts about record and table level locking, but I didn't see anything on this setting in particular.

Is it advisable to enable this? We just upgraded to 4.0, with the help of our NSC, and we are seeing some of the inevitable speed and locking problems that we didn't have when we were on 2.6 code with 3.7 clients.
There is no data, only bool!
Male
822 Posts
6,676 Points
Joined: 2001-7-3
Last Online:
2011-10-24 11:52
Location: Vedbæk, Denmark
MicrosoftEmployee
Dean McCrae replied on 2005-11-30 20:33 | Locked
So you are not running 2.6 app code from 4.0? If not, it would explain the difference in performance.

The Always Rowlock when checked causes locking behaviour to be that of 4.0, 3.70 and all versions prior, for the SQL version. That is, all SELECT statements contain a ROWLOCK directive that forces SQL Server to place row locks, when locking.

From 4.0 SP1 onwards, this has changed. The ROWLOCK is not issued which means SQL Server has the freedom to choose at what level it will lock; row, page or table. The up side of this is that it is a big reduction in lock maintenence and memory required for many rowlocks, which instead will be replaced with page locks. Therefore improved performance. The down-side is that page locking is not as fine-grained and therefore a user can be locking 'too much' data that can impact other users. Therefore reduced concurrency.

Either way there can be problems of different kinds. But the use of ROWLOCK should be unnecessary is the server is able to utilize lock levels well. I would leave it unchecked.
This posting is provided "AS IS" with no warranties, and confers no rights.
149 Posts
1,457 Points
Joined: 2001-7-21
Last Online:
2007-9-7 18:28
Location: NJ, USA
thaug replied on 2005-12-2 19:41 | Locked
We are using 4.0 code, just with our modifications, which were pretty extensive in some cases, especially in the sales header processing area.

Either way I wasn't sure what the purpose of this was, so now you have answered it. Thanks. I will leave it unchecked.
There is no data, only bool!
Page 1 of 1 (3 items) | Get this RSS feed | Bookmark and Share