February 16, 2006 at 6:58 am
Hi, apologies if this is a dumb-ass question but I'm way out of my depth on a SQL Server database I'm running as part of my business and I could do with some wise advice.
In essence the situation is that I have a table in my database that half a dozen users are inserting records into and deleting records from simultaneously (from a web front end). That table is essentially a "category" table and there are a score or so "sub-category" tables that hang off it.
So if someone wants to insert a record the Server runs a procedure and that opens a transaction, sticks a record in the main table, populates any relevant sub-category tables and then closes the transaction.
If someone wants to delete a record it does the whole thing in perfect reverse, open the transaction, delete the information from the sub-category tables, delete the record from the master table and then close the transaction.
Everything is lovely except that occasionally it just hangs. The database is running on a friends commercial SQL Server so I don't have proper sysadmin access to it. So having read some books about what I'm doing wrong, I am assuming the "obvious", that I am getting a deadlock problem caused by an insert transaction locking the master table and waiting to get a lock on a sub-category table while a delete transaction locks the sub-category table and waits to get a lock on the master table.
It's seems "obvious" that that is the cause but still surprises me a bit. It's extremely unlikely that the users are deleting each other's records. Every record is coded to a specific user. If the system were only locking the individual rows it needed to delete I can't see why this would lock up against the insert process. And equally it seems, naively perhaps, that it ought to be possible to insert a record into the main table and only lock that record not the whole table. More than likely I suspect I'm just exposing my ignorance and inexperience with locks here, but anyway.
So having read more than I understand it seems that there are a few ways to resolve the matter. The obvious one is to make the "delete process" grab the main table and lock that first. That way it won't be able to get deadlocked against anything else. I'm not actually clear on how to do that but presume that books online will tell me if I go looking hard enough.
But it occurred to me that the "cheap" solution is to apply cascade delete to all the links between my category table and my sub-category table. That way I can issue a delete action against the main table, it will automatically delete any sub-category table and all the tables will "lock-up" in the right order (the same order they presumably lock up in when I do an insert). Of course this assumes that SQL Server doesn't calculate a cascade delete by locking all the sub-category tables first for you...
It seems easy. It even has the advantage that I don't have to maintain or check any code to handle my deletions anymore (as new sub-category tables get added). Just issue one delete against the main table and bang... problem solved.
Except that lots of books advise you to avoid cascade updates/delete. There are always vague but dire warnings about what can go wrong when a database is deleting data without you expressly instructing it to. It feels "wrong" to allow SQL Server to go deleting sub-category data without explicitly handling all that myself. However given that I haven't made too good a job of it so far, I'm now wondering if the "right" answer isn't the quick and easy one.
I have considered a few other solutions. One is go the opposite way and remove the transaction and try to handle all the errors directly, deleting half complete insert records and so forth. That seems overly ambitious and likely to cause yet more locks anyway. I’ve also considered just dumping the transactions and living with the fact that I get some data loss. (The actual data isn’t that critical, which is why I’m pretty casual about this).
Any advice appreciated.
February 17, 2006 at 2:38 pm
I think cascading deletes is exactly what you need.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply