1

I have asked a few questions today as I try to think through to the solution of a problem.

We have a complex data structure where all of the various entities are tightly interconnected, with almost all entities heavily reliant/dependant upon entities of other types.

The project is a website (MVC3, .NET 4), and all of the logic is implemented using LINQ-to-SQL (2008) in the business layer.

What we need to do is have a user "lock" the system while they make their changes (there are other reasons for this which I won't go into here that are not database related). While this user is making their changes we want to be able to show them the original state of entities which they are updating, as well as a "preview" of the changes they have made. When finished, they need to be able to rollback/commit.

We have considered these options:

  1. Holding open a transaction for the length of time a user takes to make multiple changes stinks, so that's out.
  2. Holding a copy of all the data in memory (or cached to disk) is an option but there is heck of a lot of it, so seems unreasonable.
  3. Maintaining a set of secondary tables, or attempting to use session state to store changes, but this is complex and difficult to maintain.
  4. Using two databases, flipping between them by connection string, and using T-SQL to manage replication, putting them back in sync after commit/rollback. I.e. switching on/off, forcing snapshot, reversing direction etc.

We're a bit stumped for a solution that is relatively easy to maintain. Any suggestions?

Community
  • 1
  • 1
enashnash
  • 1,578
  • 1
  • 15
  • 36

4 Answers4

1

Our solution to a similar problem is to use a locking table that holds locks per entity type in our system. When the client application wants to edit an entity, we do a "GetWithLock" which gets the client the most up-to-date version of the entity's data as well as obtaining a lock (a GUID that is stored in the lock table along with the entity type and the entity ID). This prevents other users from editing the same entity. When you commit your changes with an update, you release the lock by deleting the lock record from the lock table. Since stored procedures are the api we use for interacting with the database, this allows a very straight forward way to lock/unlock access to specific entities.

On the client side, we implement IEditableObject on the UI model classes. Our model classes hold a reference to the instance of the service entity that was retrieved on the service call. This allows the UI to do a Begin/End/Cancel Edit and do the commit or rollback as necessary. By holding the instance of the original service entity, we are able to see the original and current data, which would allow the user to get that "preview" you're looking for.

While our solution does not implement LINQ, I don't believe there's anything unique in our approach that would prevent you from using LINQ as well.

HTH

imapcgeek
  • 26
  • 2
  • Doesn't exactly fit his requirements, but lots of good ideas here. – Philip Kelley Jan 18 '11 at 15:11
  • Good definitely, but not quite what we're after. This is essentially option 2 in my list, since we require locking a (very) large data set. – enashnash Jan 18 '11 at 15:16
  • Despite my comment above, this is the solution I ended up using, so I've marked it as the answer. I'm not sure whether this answer or the one from @Quassnoi is the most _correct_ though, but it works best in my case. – enashnash Feb 04 '11 at 10:15
0

Consider this:

  1. Long transactions makes system less scalable. If you do UPDATE command, update locks last until commit/rollback, preventing other transaction to proceed.
  2. Second tables/database can be modified by concurent transactions, so you cannot rely on data in tables. Only way is to lock it => see no1.
  3. Serializable transaction in some data engines uses versions of data in your tables. So after first cmd is executed, transaction can see exact data available in cmd execution time. This might help you to show changes made by user, but you have no guarantee to save them back into storage.
  4. DataSets contains old/new version of data. But that is unfortunatelly out of your technology aim.
Peposh
  • 172
  • 7
  • The reason we were considering option 1 is that there will only ever be one user at a time modifying the data that will be locked. But it smells. Regarding 3 in your list, I'll update my question to clarify what I mean. – enashnash Jan 18 '11 at 15:19
0

Use a set of secondary tables.

The problem is that your connection should see two versions of data while the other connections should see only one (or two, one of them being their own).

While it is possible theoretically and is implemented in Oracle using flashbacks, SQL Server does not support it natively, since it has no means to query previous versions of the records.

You can issue a query like this:

SELECT  *
FROM    mytable
AS OF TIMESTAMP
        TO_TIMESTAMP('2010-01-17')

in Oracle but not in SQL Server.

This means that you need to implement this functionality yourself (placing the new versions of rows into your own tables).

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
0

Sounds like an ugly problem, and raises a whole lot of questions you won't be able to go into on SO. I got the following idea while reading your problem, and while it "smells" as bad as the others you list, it may help you work up an eventual solution.

First, have some kind of locking system, as described by @user580122, to flag/record the fact that one of these transactions is going on. (Be sure to include some kind of periodic automated check, to test for lost or abandoned transactions!)

Next, for every change you make to the database, log it somehow, either in the application or in a dedicated table somewhere. The idea is, given a copy of the database at state X, you could re-run the steps submitted by the user at any time.

Next up is figuring out how to use database snapshots. Read up on these in BOL; the general idea is you create a point-in-time snapshot of the database, do whatever you want with it, and eventually throw it away. (Only available in SQL 2005 and up, Enterprise edition only.)

So:

  • A user comes along and initiates one of these meta-transactions.
  • A flag is marked in the database showing what is going on. A new transaction cannot be started if one is already in process. (Again, check for lost transactions now and then!)
  • Every change made to the database is tracked and recorded in such a fashion that it could be repeated.
  • If the user decides to cancel the transaction, you just drop the snapshot, and nothing is changed.
  • If the user decides to keep the transaction, you drop the snapshot, and then immediately re-apply the logged changes to the "real" database. This should work, since your requirements imply that, while someone is working on one of these, no one else can touch the related parts of the database.

Yep, this sure smells, and it may not apply to well to your problem. Hopefully the ideas here help you work something out.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92