![]() ![]() An UNDELETE action can be implemented using the ON_DELETE trigger on the DeletedMyData view to set the value to 0. UPDATE: The ON_DELETE trigger on the MyData view would just issue an update against the actual table, setting the Deleted value to 1. Since they are likely queried by joining with the undeleted view (the one with the original table name), records referring to "deleted" data will not be exposed but will be automatically re-exposed when data is undeleted. The child tables will probably require no changes at all. This code will be the only code directly accessing the table, setting the value of Deleted to 0 or 1 as appropriate. Implement a "soft delete" and "undelete" functionality within the app. ![]() Write triggers on the view to handle DML as if it was the original table.Ĭreate another view to expose the deleted rows. Create a view with the old name that exposes the data from the table exactly the way it looked before, but only for the "undeleted" rows.This is an excellent example of why I am so fond of views. Option 1 seems to be more effort up front, but more maintainable in the future, option 2 is not much work up front, minimal changes to web app, but very messy. I would likely need to mirror every child table as well. Move the record to another copy of the table, specifically for deleted records. The web app is old and not well written (repeated code) so there are many places that will require this change, so it is risky that one or more locations might be missed. This will require updating all selects on the table (and also, on it's child tables, as they are now orphaned) to include the clause where deleted = 0. It is a fairly large sized web application so I am looking for the least risky and lowest impact change so that it will require the minimum amount of changes to the web application.Īdd a column to the table deletedwith default 0, and 1 will indicate that the record has been deleted. I have a requirement to change the behavior of the "delete" function on a web application on a table that has 5 child foreign key relationships. I could not find this information in the documentation, so I would say that deletion order is not guaranteed (although it might be indirectly as an implementation detail). Is it possible that two concurrent but identical DELETE statements will delete rows in a different order? Is it possible to enforce the deletion order to avoid a deadlock? Now, coming to my question, what happens if the DELETE statement touches multiple rows? For example: session 1: begin The deadlock would not have occurred if both sessions deleted in the same order. Session 2: DELETE FROM my_table WHERE my_key = 1 ![]() Session 1: DELETE FROM my_table WHERE my_key = 2 Session 2: DELETE FROM my_table WHERE my_key = 2 Session 1: DELETE FROM my_table WHERE my_key = 1 The following sequence of statements can cause a deadlock: session 1: begin And my procedure has multiple 35 DML statments, there are around 15 DELETE statements for instance column with other columns( DELETE FROM table WHERE instance = AND col1 = 1:Īssume some_table has two rows, with primary key 1 and 2. Note I couldnt able to primary key as it is consuming time when I insert 10 million rows to the table (this for one transaction there are 20 concurrent transations). Please advice me which lock I can use in this scenario. Note: The nonclustered index is created on instance column. WHERE inserted <= DATEADD(hh, -10, GETDATE()) Query 2: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED Query 1: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED The below are the queries which will be executed concurrently, which causing the deadlock. One user request will have one unique id but may have million rows. The inserted column will be populated with GETDATE() to contain the time when each row was inserted and the instance column will contain the value from newid(). There are separate stored procedures to make DML operations to this table. The data will be processed to this table concurrently by multiple user requests. It is a temporary table used for reporting purposes. I have a table called Products with many columns. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |