row-level or page-level locking?

row-level or page-level locking?

Post by fnor » Sat, 11 Nov 2000 04:00:00



I'm dealing with an SQL7 database that's been in production for about a year
now, and it's getting to be extremely slow. In the last couple of weeks
we've been running into some deadlocks that have been killing a web app.

I was under the impression that SQL7 is doing row-level locking by default,
which should in theory keep this problem from occuring -- essentially, two
threads of the web app are trying to write to the database simultaneously,
and one of them becomes the "victim" -- this wasn't an issue until recently.

Am I mistaken on this? Do I need to turn on row-level locking somewhere?

thanks,
John Stotler

 
 
 

row-level or page-level locking?

Post by Carlos Eduardo Roja » Sat, 11 Nov 2000 04:00:00


Row level locking is turned on by default in sql server 7. The problem is
that sql server evaluates the cost of the locks, and if it decides that a
page lock is better, this is the way it takes. The reason is that sometimes,
it takes a lot of memory to lock many rows, and it's cheaper to lock just
the table, because it's only one lock instead of many of them.
If you want to force sql server to use row level lock in your queries, use
hints. You can read more about hints in the books online, but here's an
example:
SELECT au_lname FROM authors WITH (ROWLOCK)
--
Hope this helps,,
---------------------------------------------------
Carlos Eduardo Rojas
MCSE+I, MCDBA, MCSS
Pass Spanish Group


Quote:> I'm dealing with an SQL7 database that's been in production for about a
year
> now, and it's getting to be extremely slow. In the last couple of weeks
> we've been running into some deadlocks that have been killing a web app.

> I was under the impression that SQL7 is doing row-level locking by
default,
> which should in theory keep this problem from occuring -- essentially, two
> threads of the web app are trying to write to the database simultaneously,
> and one of them becomes the "victim" -- this wasn't an issue until
recently.

> Am I mistaken on this? Do I need to turn on row-level locking somewhere?

> thanks,
> John Stotler


 
 
 

row-level or page-level locking?

Post by fnor » Sat, 11 Nov 2000 04:00:00


So there isn't a setting on the server to force row-level locking? We're
dealing with thousands and thousands of lines of code and hundreds of
queries written in a tested and released software package... Changing each
of the queries isn't an option for us.

Thanks for the response, by the way.

John Stotler



> Row level locking is turned on by default in sql server 7. The problem is
> that sql server evaluates the cost of the locks, and if it decides that a
> page lock is better, this is the way it takes. The reason is that
sometimes,
> it takes a lot of memory to lock many rows, and it's cheaper to lock just
> the table, because it's only one lock instead of many of them.
> If you want to force sql server to use row level lock in your queries, use
> hints. You can read more about hints in the books online, but here's an
> example:
> SELECT au_lname FROM authors WITH (ROWLOCK)
> --
> Hope this helps,,
> ---------------------------------------------------
> Carlos Eduardo Rojas
> MCSE+I, MCDBA, MCSS
> Pass Spanish Group



> > I'm dealing with an SQL7 database that's been in production for about a
> year
> > now, and it's getting to be extremely slow. In the last couple of weeks
> > we've been running into some deadlocks that have been killing a web app.

> > I was under the impression that SQL7 is doing row-level locking by
> default,
> > which should in theory keep this problem from occuring -- essentially,
two
> > threads of the web app are trying to write to the database
simultaneously,
> > and one of them becomes the "victim" -- this wasn't an issue until
> recently.

> > Am I mistaken on this? Do I need to turn on row-level locking somewhere?

> > thanks,
> > John Stotler

 
 
 

row-level or page-level locking?

Post by Erland Sommarsk » Sun, 12 Nov 2000 04:00:00



>I'm dealing with an SQL7 database that's been in production for about a
>year now, and it's getting to be extremely slow. In the last couple of
>weeks we've been running into some deadlocks that have been killing a web
>app.

>I was under the impression that SQL7 is doing row-level locking by default,
>which should in theory keep this problem from occuring -- essentially, two
>threads of the web app are trying to write to the database simultaneously,
>and one of them becomes the "victim" -- this wasn't an issue until
>recently.

>Am I mistaken on this? Do I need to turn on row-level locking somewhere?

It doesn't sound to me that row-locking vs. page-locking is your
problem. Your problem appears to be that your database has grown,
and you are now paying the price for missing indexes. If I were you,
I would spend time on finding the cause for the bottlenecks.
Resolving them are likely to likely resolve the deadlocks as well.

--

This is signature isn't half as witty as it used to be.

 
 
 

1. row level vs page level locking is it more than marketing

What has row at a time processing, versus set based, got to do with page =
level versus row level locking???
Most programmers on planet earth have done the very same thing you trumpet =
you own abilities in doing.
So what?
Are you really as incredibly impressed with yourself as you would lead us =
to believe by your unending egocentrical posts; or are you on holiday and =
your worst enemy is gleefully destroying your credibility at your =
still-logged-in terminal?

Peter =22Had a gutsful of Pablo=22 Tashkoff

I was doing a bench for a customer and they were doing some
nightly processing... looking at their code, I saw that one
section was taking 2.5 hours.  I rewrote it and that same
section took a couple of minutes. =20

What was the magic?  Converted their row at a time logic to
set based logic.=20
--=20
Pablo Sanchez              =7C Ph =23 (650) 933.3812          Fax =23 =
(650) 933.2821
pablo=40sgi.com              =7C Pg =23 (800) 930.5635   -or-   pablo_p=40p=
ager.sgi.com=20
---------------------------------------------------------------------------=
----
I am accountable for my actions.   http://reality.sgi.com/pablo =5B =
/Sybase_FAQ =5D

2. Big issue: ADODB.command 2.5 and 2.6

3. table-level lock or row-level lock

4. ASE OLE DB Provider

5. Page lock to simulate row level lock

6. 'Query to Complex' Workaround needed!

7. Paradox 5.0: Row level locking or page locking?

8. Page level, table level locks used.

9. Record-Level or Page-Level Locking

10. table level locks, row level locks, column level locks?

11. row level locking and dead-lock prevention