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
by