ROWLOCK

Question

ROWLOCK

ps.

One Orange Chip

Points: 29423
More actions
October 27, 2010 at 8:05 am

#147397

I know rowlock is a query level hint that we can give to our queries.
One of our vendors suggested our application team that there is a setting which if we enable, rowlock will be used by all queries. I am unaware of any such setting that we can do at table level or database level.
Any ideas?

Pradeep Singh

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I’m not aware of such setting in the database or server level. I know that you can prevent page level lock on index and table level when you create or alter table or index (you can read about it in BOL). This of course won’t prevent table lock. The question is why do you want to make sure that no matter what SQL statement is being used, the server will use row lock?

By the way even if you use the rowlock hint, the server can escalate the row level lock to a table level lock, so even the rowlock hint cannot guarantee that row level lock will be used.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please point out to the vendor that in general it's better to let SQL manage the locks itself. Even with a rowlock hint, SQL will still escalate the locks to table if too many locks are taken (it's that or run out of lock memory)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29423 More actions · Answer 3

Thanks Gail.

Application team even said that they have applied *this* setting on a different server and a previous DBA helped them to do so!!

Pradeep Singh

ps. One Orange Chip Points: 29423 More actions · Answer 4

Thanks Adi. I tried to explain them this but they were somehow of the view that if rowlevel lock *setting* is in place, they will get lesser deadlocks.

Furthermore, they had read_committed_snapshot concurrency level turned on last night. I have told them to beware of consequences of simulataneous update to a row by multiple sessions.

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

ps. (10/27/2010)
Application team even said that they have applied *this* setting on a different server and a previous DBA helped them to do so!!

Then ask them what the setting is. If they've applied it, they must know.

I know there's traceflags to disable lock escalation, but those won't prevent SQL from starting with a page or table lock. They're also pretty dangerous to use if you're careless.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

ps. (10/27/2010)
Thanks Adi. I tried to explain them this but they were somehow of the view that if rowlevel lock *setting* is in place, they will get lesser deadlocks.

Deadlocks are, in the vast majority of cases, the result of poor indexing or badly written queries. Forcing locking hints is a (poor) attempt to hide the symptoms, not fix the problem.

Furthermore, they had read_committed_snapshot concurrency level turned on last night. I have told them to beware of consequences of simulataneous update to a row by multiple sessions

Watch your TempDB configuration.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29423 More actions · Answer 7

Gail - as of today i am just going to run some DMVs to get an overview of performance.

Also, I think deadlocks should reduce coz of snapshot isolation. I will also enable trace flag 1222.

Adi - I found these two settings that can be used in 2005 with create table/index.

WITH( ALLOW_ROW_LOCKS = ON/OFF, ALLOW_PAGE_LOCKS = ON/OFF)

is there a way to find out if these are turned ON or OFF for a particular table?

Pradeep Singh

ps. One Orange Chip Points: 29423 More actions · Answer 8

GilaMonster (10/27/2010)

Watch your TempDB configuration.

yes i had told them that tempdb will be used heavily because of row versioning. As of now only 40 MB is used out of some 1.5 GB

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

ps. (10/27/2010)
is there a way to find out if these are turned ON or OFF for a particular table?

They're not enabled for a table, they're enabled for an index.

Query sys.indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29423 More actions · Answer 10

Hi Gail. I am confused a bit. see these links.

http://stackoverflow.com/questions/418082/sql-server-2005-generated-create-table-script-not-working

http://dbaspot.com/forums/sqlserver-server/375200-create-table-script.html

IN both of the cases users are creating tables with these settings. Am i missing something?

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Edit: Misunderstood the question.

That option isn't on the table, it's on the primary key, which is an index. You can't say

CREATE TABLE [dbo].[MyTable2](

[id] [int] IDENTITY(1,1) NOT NULL,

SomeCol varchar(20)

WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

)

The allow_row_locks and allow_page_locks are options when creating an index. Primary key, unique constraint or plain simple index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 12

ps. (10/27/2010)
Hi Gail. I am confused a bit. see these links.
http://stackoverflow.com/questions/418082/sql-server-2005-generated-create-table-script-not-working
http://dbaspot.com/forums/sqlserver-server/375200-create-table-script.html
IN both of the cases users are creating tables with these settings. Am i missing something?

I think that this is because those settings are written at the part that creates the primary key, so although those settings are used in the create table statement, it actually reference the index that is created by the primary key.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ps. One Orange Chip Points: 29423 More actions · Answer 13

Thanks Adi, Gail. I have got information from you that i needed to reply to them.

Pradeep Singh