Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MegaJar
Starting Member
6 Posts |
 Posted - 2007-11-06 : 16:56:56
|
| Hiya. I'm currently a developer on a web application with about 3000 users. It uses SQL Server 2005. This app supports lots of different functions -- uploading documents, discussion threads, diagrams, and so forth. The whole thing is subdivided into communities -- users are assigned roles and permissions within a given community, and all operations occur within a community (i.e. you upload a document to a specific community, etc).We occasionally have instances where a user accidentally deletes a community, and wants us to restore it. This is extremely time-consuming, since deleting a community causes ripple effects throughout the entire application. It's a VERY complex operation, with many steps, spanning many tables; and each of those steps has to be undone, in reverse order, BY HAND. It's an utter pain. So I've been given the task of automating this process to make our lives easier.We had previously set up a logging system using triggers; whenever a change is made to any table in our main database, that change automatically gets mirrored to a log database. It also stores a "transaction ID" that gets assigned to all automatic changes within a single multi-step operation, so that we know they were all part of the same operation. (It's not really a transaction in the usual sense, but we couldn't think of a better name.) My job is to use this log table to restore a deleted community, and any extra baggage that was deleted along with it.Now, as I said, this operation spans many tables, all inter-linked with foreign key constraints. It would be an extremely delicate operation trying to restore the data to each table, one-by-one, while maintaining all the FK constraints. Additionally, many of these tables use IDENTITY columns, which means I can't restore the deleted rows with their original IDs.The best way I can think of to get around this problem is to temporarily disable the FK constraints (by using "ALTER TABLE tblName NOCHECK CONSTRAINT fkName") and the IDENTITY columns (by using "SET IDENTITY_INSERT tblName ON"), then restore all the deleted data at once, and then re-enable the FK's and IDENTITY's.Now, here's my problem. As I said above, this application has about 3000 users. If I disable the FK's and IDENTITY's while restoring a community, it leaves the tables in a vulnerable state. It's possible that a user may simultaneously be doing something to cause further changes; and with those constraints disabled, it could result in bogus data, violated referential integrity, duplicated rows, etc.My question is, how can I get around this problem? Should I try to "freeze" the database, blocking anyone else from accessing it until the operation is done? And if so, how?Any help would be most appreciated. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-06 : 17:22:19
|
| My immediate reaction is to suggest that you change the way a delete works.The fact that you are looking to "automate the undelete process" suggests that inadvertent delete happens somewhat-often ...How about if you Flag a record as deleted, and then physically delete it sometime later - perhaps after a month. Undelete then becomes easy and is perhaps something you can allow the user to do for themselves ... perhaps after inserting their credit card and paying a fine!This would require changing the application to "ignore" records flagged as deleted.Or by renaming the relevant table(s) and creating a view with the original table's name with:CREATE VIEW MyOriginalTableNameASSELECT *FROM NewTableNameWHERE IsDeleted = 0no doubt this will also require some adjustment to indexes to keep the efficiency good, and changing the application to directly use the new table names for all new queries, and re-factoring old code which is heavily used.Just a thought ...Kristen |
 |
|
|
dbaerwald
Starting Member
5 Posts |
Posted - 2007-11-06 : 18:29:14
|
| It sounds like Kristen has an idea there. Could you just flag the records as "deleted" with an additional field? Modify the queries in the application to only look at records where the "Deleted" field is 0? When you need to undo a delete then just set that field back to 0 (where 1 is "deleted").That way you may never need to actually delete the records.This maybe a gross oversimplification of yours needs, but I think it might be a good solution.Good luck with this project. It sounds very challenging. |
|
|
|
MegaJar
Starting Member
6 Posts |
Posted - 2007-11-07 : 08:49:49
|
| Thanks for the advice. Actually, we already implemented a feature where users can mark a community as "Hidden", which makes it inaccessible to all users, but doesn't actually delete it. That's working fine. This, however, is a separate issue altogether.Basically, each community has a designated "owner" who's responsible for it. That owner is allowed to hide/unhide a community, add sub-communities to it, add/remove users to it, and delete it altogether. Unfortunately, the old adage "Users Are Stupid" seems to apply quite well here, since every so often (maybe once a month or so) we get someone who deleted their community by accident, and needs us to restore it. That's where I come in.Personally, I think this is WAY too complex an operation to be able to completely undo; there'll probably be at least some steps that can't be undone and have to be skipped. In my opinion, we should just remove the Delete option entirely. However, that would constitute a change in the basic privileges of the community owner, and thus would violate the "business rules" for the application; that decision would have to be made by my project lead, not me. And he's on vacation for 2 weeks, so I can't ask him.Right now, I have to try to implement this auto-restore function as best I can; that's my assignment, and I have to at least give it my best shot.So, is there any way to do what I said in my original post ("freezing" the database)? Or is there a better way to protect the database from unwanted changes while the FK's and IDENTITY columns are disabled? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-07 : 09:05:44
|
| "So, is there any way to do what I said in my original post ("freezing" the database)? Or is there a better way to protect the database from unwanted changes while the FK's and IDENTITY columns are disabled?"You can probably lock all the tables for the duration.But IMHO that's the wrong solution; to easy to screw up something unforeseen during the "undo" process and wreck the database in spectacular fashion.Kristen |
|
|
|
MegaJar
Starting Member
6 Posts |
Posted - 2007-11-07 : 09:16:57
|
| A thought occurs. Basically, I just want to disable FK and IDENTITY constraints while I'm restoring the deleted data, and then re-enable them. Since I'm restoring deleted data which (originally) satisfied all the constraints, there should be no discernible problems afterwards. The best scenario would be if those changes happened ONLY in the current session; i.e. the FK's and IDENTITY's were only disabled for the current user, and only for the duration of the current session, but remained enabled for everyone else. Nobody else would have to know that they were ever disabled at all.Now, our application is implemented using Java Struts (Java/JSP), and connects to the database via JDBC calls. If I call "Connection.setAutoCommit(false)" before I start, then **NOTHING** I do will actually take effect in the database until I call "Connection.commit()", correct? Would this achieve the "disabled in current session only" effect I'm looking for here? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-07 : 09:38:04
|
| "the FK's and IDENTITY's were only disabled for the current user, and only for the duration of the current session, but remained enabled for everyone else"I can't think of a way to achieve that for FKs. I think that SET IDENTITY_INSERT MyTable ON is local to the sessionKristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-11-07 : 10:21:46
|
| Well if you've already implemented the "hidden" featureThe logical delete feature is same thing, except it's for everyoneTo get it back just set the flag back to true?In any case, it sounds like a "feature" that users should not haveGoy a link to the web page?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
MegaJar
Starting Member
6 Posts |
Posted - 2007-11-07 : 10:33:49
|
quote:
Well if you've already implemented the "hidden" featureThe logical delete feature is same thing, except it's for everyoneTo get it back just set the flag back to true?In any case, it sounds like a "feature" that users should not have
I reiterate: removing this feature would violate our business rules, and that's not my decision to make. For the moment at least, I have to attempt to implement this undo feature; I CAN'T remove the delete function altogether, until my boss comes back and I can bring it up with him.Now, do you think what I mentioned above (with the Java) would do what I need, or not? Do you have any other suggestions (besides removing the delete function)?quote:
Got a link to the web page?
Sorry, but that wouldn't help. The application is for a specific (albeit large) group of users, so you wouldn't be able to get an account there anyway.  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-07 : 10:53:04
|
| I imagine that you can drop an FK constraint within a transaction, sort the data out, and then re-create the constraint before committing ... or rollback |
|
|
|
MegaJar
Starting Member
6 Posts |
Posted - 2007-11-07 : 12:06:23
|
| Right, that's what I was thinking. But my question is, will dropping the FK constraint be limited JUST to that transaction? Or will it also affect anyone else using the table at the same time? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-07 : 12:14:55
|
| I imagine the table will be locked until you commit (or rollback) and thus there will be no other transaction processed against the table.But you can test that so as to be sure |
|
|
|
MegaJar
Starting Member
6 Posts |
Posted - 2007-11-07 : 15:41:51
|
| Ok, thanks for the info. |
|
|
|
|
|
|
|
|
Please help -- undoing complex delete