|
Home > Archive > MS SQL Server > March 2006 > Cannot use ROW granularity hint on table locking at the specified granularity is inhibited
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Cannot use ROW granularity hint on table locking at the specified granularity is inhibited
|
|
| chavankoya189@hotmail.com 2006-03-13, 8:23 pm |
| Hi,
I was trying to delete rows from a table and I am getting the
following error
Server: Msg 651, Level 16, State 1, Line 6
Cannot use ROW granularity hint on table 'ODS_END_USER_MONTHL
Y_OLD'
because locking at the specified granularity is inhibited.
I do NOT want to lock the TABLE in Xclusive mode . How to prevent
this and lock the rows only. Tried with pglock also, getting same
problem - Please help
set rowcount 2000 ;
while 1 = 1
begin
delete_more:
begin tran t1
delete from db.dbo.tablename with (rowlock) where
upper(billing_sys) like 'CRIS%' option(maxdop 1) ;
commit tran t1
if @@rowcount > 0 goto delete_more ;
end
set rowcount 0 ;
| |
| Tom Moreau 2006-03-13, 8:23 pm |
| This sounds like an index option to prevent row locks was used. Check out
sp_indexoption in the BOL for details.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
< chavankoya189@hotmai
l.com> wrote in message
news:1142290954.288600.141730@p10g2000cwp.googlegroups.com...
Hi,
I was trying to delete rows from a table and I am getting the
following error
Server: Msg 651, Level 16, State 1, Line 6
Cannot use ROW granularity hint on table 'ODS_END_USER_MONTHL
Y_OLD'
because locking at the specified granularity is inhibited.
I do NOT want to lock the TABLE in Xclusive mode . How to prevent
this and lock the rows only. Tried with pglock also, getting same
problem - Please help
set rowcount 2000 ;
while 1 = 1
begin
delete_more:
begin tran t1
delete from db.dbo.tablename with (rowlock) where
upper(billing_sys) like 'CRIS%' option(maxdop 1) ;
commit tran t1
if @@rowcount > 0 goto delete_more ;
end
set rowcount 0 ;
| |
| chavankoya189@hotmail.com 2006-03-22, 11:23 am |
| Hi Tom,
Thank you for responding. Yes we have used sp_indexoption
and I tried to disable it. The disable on row and page executed
successfully, but when I try to execute again i was getting the same
problem again. And this table does not have any indexes. That could be
the reason. It looks like a bug in SQL Server.
--Chavan Koya
Tom Moreau wrote:
> This sounds like an index option to prevent row locks was used. Check out
> sp_indexoption in the BOL for details.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
| |
| Tom Moreau 2006-03-22, 8:24 pm |
| Never have a table without an index. Technically, it isn't a table when it
doesn't have a primary key. A primary key is supported in the background by
a unique index. Failure to do so will give you locking problems when you do
data mods.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
< chavankoya189@hotmai
l.com> wrote in message
news:1143045689.768636.100480@t31g2000cwb.googlegroups.com...
Hi Tom,
Thank you for responding. Yes we have used sp_indexoption
and I tried to disable it. The disable on row and page executed
successfully, but when I try to execute again i was getting the same
problem again. And this table does not have any indexes. That could be
the reason. It looks like a bug in SQL Server.
--Chavan Koya
Tom Moreau wrote:
> This sounds like an index option to prevent row locks was used. Check out
> sp_indexoption in the BOL for details.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
| |
|
| it could be the poor thing is just flat timing out.
is billing_sys ALWAYS upper case?
if you want it to go faster, create an index on Upper(billing_sys), adn
change your code to
delete from db.dbo.tablename with (rowlock) where
left(upper(billing_s
ys),4) ='CRIS'
|
|
|
|
|