August 27, 2002 at 6:56 am
I have an application/database migrated from Access. The database is highly
Hierarchical with Accounts, then Policies then Policy Items and Sub Policy
Items.
(i.e. 1 to many relationship from Accounts to policies, 1 to many from
policy to multiple Policy Item tables, 1 to many relationship from some
Policy Item tables to Sub Policy Item Tables) All enforced via Direct
Referential Integrity (DRI).
When I attempt to delete an account with no dependent polices in a single
user environment through Query Analyzer it takes 10 minutes. During this
time I can see exclusive TABLE locks (and intent for exclusive locks) being
placed on some policy Item tables and Sub Policy Item Tables. So far via
the VB/ADO application the Delete appears to work, but the delete gets
rolled back on connection close and the account "reappears" on next login.
I also locked up the production database (no users could even login and do a
select on the account table) in attempting to delete 50 (out of 2000)
accounts.
This database is planned for an OLTP environment with 100-150 concurrent
users. Exclusive table locks are unacceptable. Any suggestions on why this
may be occurring or how to fix it.
This database and database design has been working in Access for over a year
so this excessive locking and slow response by SQL Server has me puzzled.
Brian Freeman
Carnegie Technologies/Bluewave Computing
http://www.carnegie.com and http://www.bluewave-computing.com
August 28, 2002 at 2:58 am
There could be two reasons for deleting problem:
1. SET IMPLICIT_TRANSACTIONS { ON | OFF } setting in set to "on" and you are not commiting the transaction
2. You are starting a transaction and closing the application without commiting the transaction.
It looks like you are deleting records after beginning transaction and that's why table is getting locked for the period.
10 minutes to delete gives a hint that it's an indexing problem, so create an index on filter colums.
Cheers...........
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
August 28, 2002 at 4:08 am
Slow deletes can also be a sign of to many indexes as well.
Also, check to see if you have "truncate log on checkpoint" or "auto shrink" on. Both of these database option could be an issue.
As for the deleted records return you may need to setup Profiler to catch an instance of this occurring to see why.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply