Lock escalation

Hello,
I'd like to get a general opinion if in other people's experience they tend
to get whole table locks.
We have an inhouse customer accounting app. One process in particular takes
just under 2 minutes to run. It processes 1400 customers by checking 4200
balance histories, checking and updating 1400 status records, and inserting
8400 new records across 5 tables. The whole process is in an expicit
transaction.
Our server is SQLServer2000 on a 3GHz Pentium4 with 2GB but with 1GB
dedicated to SQLServer.
I have noticed that the locks escalate to table locks so that noone can view
any data when the posting is being done.
Does this behaviour sound normal for this many records or is something
wrong? I don't think that to whole tables should be locked for 8000
records. The 5 tables have an average of 400K records each. The whole DB
is 900MB.
Thanks
Brian Morris
"Brian" <s@.y> wrote in message news:OWuWOJxfHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I'd like to get a general opinion if in other people's experience they
> tend to get whole table locks.
> We have an inhouse customer accounting app. One process in particular
> takes just under 2 minutes to run. It processes 1400 customers by
> checking 4200 balance histories, checking and updating 1400 status
> records, and inserting 8400 new records across 5 tables. The whole
> process is in an expicit transaction.
> Our server is SQLServer2000 on a 3GHz Pentium4 with 2GB but with 1GB
> dedicated to SQLServer.
> I have noticed that the locks escalate to table locks so that noone can
> view any data when the posting is being done.
> Does this behaviour sound normal for this many records or is something
> wrong? I don't think that to whole tables should be locked for 8000
> records. The 5 tables have an average of 400K records each. The whole DB
> is 900MB.
It depends. What are the queries? Can you post them? And more
importantly, what are the indexes.
Most likely you're doing some sort of range query of an indexed field.
Say something like
Select orderdate from ordertable where orderdate between '2007-01-01' and
'2007-01-31'
and orderdate has no index on it. (Obviously your queries wil be more
complex ;-)
But, while that may only cover say 8000 orders (out of 400K), if there's no
index on orderdate, SQL Server has to do a table scan. And to make sure
that another transaction can't insert (or delete) records while it's
scanning the table, it has to lock the table.
Note the CPU speed really won't matter much here. Memory helps some because
it can cache more of the database, but ultimately it's probably dominated by
disk I/O.
If you can, post the queries and DDL and perhaps we can help find a more
effecient query.

> Thanks
> Brian Morris
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Greg,
All the fields I query on are indexed. Actually in checking I did find a
couple extra unnecessary indexes that I deleted which actually shortened the
run time by 15% but the table locks are still there.
I'm not so worried about the runtime but more the locking.
I can't easily post the queries but I can do the DDL. Would that be still
useful?
Thanks
Brian
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%233KA3zxfHHA.3676@.TK2MSFTNGP05.phx.gbl...
> "Brian" <s@.y> wrote in message
> news:OWuWOJxfHHA.1216@.TK2MSFTNGP03.phx.gbl...
> It depends. What are the queries? Can you post them? And more
> importantly, what are the indexes.
> Most likely you're doing some sort of range query of an indexed field.
> Say something like
> Select orderdate from ordertable where orderdate between '2007-01-01' and
> '2007-01-31'
> and orderdate has no index on it. (Obviously your queries wil be more
> complex ;-)
> But, while that may only cover say 8000 orders (out of 400K), if there's
> no index on orderdate, SQL Server has to do a table scan. And to make
> sure that another transaction can't insert (or delete) records while it's
> scanning the table, it has to lock the table.
> Note the CPU speed really won't matter much here. Memory helps some
> because it can cache more of the database, but ultimately it's probably
> dominated by disk I/O.
> If you can, post the queries and DDL and perhaps we can help find a more
> effecient query.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||Your server doesn't have much RAM. Locks use memory and when the optimizer
is pinched for memory, it is more likely to escalate to a table lock. With
just 1GB of RAM for SQL Server, I am not surprised you're seeing this.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Brian" <s@.y> wrote in message news:OWuWOJxfHHA.1216@.TK2MSFTNGP03.phx.gbl...
Hello,
I'd like to get a general opinion if in other people's experience they tend
to get whole table locks.
We have an inhouse customer accounting app. One process in particular takes
just under 2 minutes to run. It processes 1400 customers by checking 4200
balance histories, checking and updating 1400 status records, and inserting
8400 new records across 5 tables. The whole process is in an expicit
transaction.
Our server is SQLServer2000 on a 3GHz Pentium4 with 2GB but with 1GB
dedicated to SQLServer.
I have noticed that the locks escalate to table locks so that noone can view
any data when the posting is being done.
Does this behaviour sound normal for this many records or is something
wrong? I don't think that to whole tables should be locked for 8000
records. The 5 tables have an average of 400K records each. The whole DB
is 900MB.
Thanks
Brian Morris
|||A possible solution would be to split your big explicit single transaction
into multiple transactions.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Brian" <s@.y> wrote in message news:OWuWOJxfHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I'd like to get a general opinion if in other people's experience they
> tend to get whole table locks.
> We have an inhouse customer accounting app. One process in particular
> takes just under 2 minutes to run. It processes 1400 customers by
> checking 4200 balance histories, checking and updating 1400 status
> records, and inserting 8400 new records across 5 tables. The whole
> process is in an expicit transaction.
> Our server is SQLServer2000 on a 3GHz Pentium4 with 2GB but with 1GB
> dedicated to SQLServer.
> I have noticed that the locks escalate to table locks so that noone can
> view any data when the posting is being done.
> Does this behaviour sound normal for this many records or is something
> wrong? I don't think that to whole tables should be locked for 8000
> records. The 5 tables have an average of 400K records each. The whole DB
> is 900MB.
> Thanks
> Brian Morris
>
|||I thought that may be part of the problem but the process has to be a go or
no-go kind of thing for each group of customers and this group is 1400 and
we can't take people out of the group.
Brian
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eub2vO3fHHA.596@.TK2MSFTNGP06.phx.gbl...
>A possible solution would be to split your big explicit single transaction
>into multiple transactions.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Brian" <s@.y> wrote in message
> news:OWuWOJxfHHA.1216@.TK2MSFTNGP03.phx.gbl...
>
|||Hum, maybe if you give an explicit ROWLOCK hint on your tables, they will
not automatically escalate to a table lock. You could also try with a
PagLock (possibly in conjonction with a clustered index).
Another possibility would be to set up the table lock from the beginning in
order to accelerate the update.
Maybe redesigning your schema in order that this transaction doesn't block
other peoples could also be the solution. However, as I don't know what you
are doing with this stuff, I cannot tell you anything more on this.
Changing the isolation level is another possibility but again, I don't know
what you are doing with this stuff.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Brian" <s@.y> wrote in message news:eOvaPq3fHHA.4536@.TK2MSFTNGP04.phx.gbl...
>I thought that may be part of the problem but the process has to be a go or
>no-go kind of thing for each group of customers and this group is 1400 and
>we can't take people out of the group.
> Brian
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:eub2vO3fHHA.596@.TK2MSFTNGP06.phx.gbl...
>
|||I can't do the ROWLOCK hint becuase I've got some ADO code that does some
complicated calculations and updates via recordsets instead of SQL.
We don't have any clustered indexes.
We need the ReadCommitted isolation.
I'm guessing by your response that this kind of locking is not supposed to
happen.
I'll keep reading around and see if we can figure out what we are doing
wrong.
Thanks
Brian
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23dHiNA4fHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hum, maybe if you give an explicit ROWLOCK hint on your tables, they will
> not automatically escalate to a table lock. You could also try with a
> PagLock (possibly in conjonction with a clustered index).
> Another possibility would be to set up the table lock from the beginning
> in order to accelerate the update.
> Maybe redesigning your schema in order that this transaction doesn't block
> other peoples could also be the solution. However, as I don't know what
> you are doing with this stuff, I cannot tell you anything more on this.
> Changing the isolation level is another possibility but again, I don't
> know what you are doing with this stuff.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Brian" <s@.y> wrote in message
> news:eOvaPq3fHHA.4536@.TK2MSFTNGP04.phx.gbl...
>
|||"Brian" <s@.y> wrote in message news:OrVBqNyfHHA.208@.TK2MSFTNGP05.phx.gbl...
> Greg,
> All the fields I query on are indexed. Actually in checking I did find a
> couple extra unnecessary indexes that I deleted which actually shortened
> the run time by 15% but the table locks are still there.
> I'm not so worried about the runtime but more the locking.
> I can't easily post the queries but I can do the DDL. Would that be still
> useful?
Hmm, w/o the queries, the DDL won't really help. But perhaps someone might
have some insight. So I'd say do it.
Can't easily post due to complexity or privacy? If the latter, perhaps
sanitizing them some?

> Thanks
> Brian
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:%233KA3zxfHHA.3676@.TK2MSFTNGP05.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||"Brian" <s@.y> wrote in message news:OW9ugU4fHHA.3632@.TK2MSFTNGP02.phx.gbl...
>I can't do the ROWLOCK hint becuase I've got some ADO code that does some
>complicated calculations and updates via recordsets instead of SQL.
> We don't have any clustered indexes.
> We need the ReadCommitted isolation.
> I'm guessing by your response that this kind of locking is not supposed to
> happen.
> I'll keep reading around and see if we can figure out what we are doing
> wrong.
Also, ROWLOCK would consume more memory which could potentially make the
issue worse.
As for not having any clustered indexes, any reason why not? (not saying
they would necessarily help here, but they do have their place and in
general are useful.)

> Thanks
> Brian
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html