I am working on a web app with 2 level of users - admin and user.
The specs say that Admin can delete a type of collection.
It is possible that the admin deletes the collection at the same time that the user goes and edit, adds or deletes stuff from the collection. This can cause
problems if the collection is already deleted from table when user wants to add to it
The app is Tomcat/Java/SQLServer.
Would SQLServer handle this in any form? (table locks etc..)
If I am to handle it,since it is a Multithreaded app server, locking the table in the one request per thread model of tomcat makes no sense.
Can anyone tell me how to deal with this possible problem?

SR
Thursday, August 26, 2004

Just ignore the error message the SQL Server will return (it should be something like FOREIGN KEY violated or somesuch).

Captain McFly
Thursday, August 26, 2004

==> It is possible that the admin deletes the collection at the same time that the user goes and edit, adds or deletes stuff from the collection.

Databases would be pretty much useless without managing concurrency now wouldn't they? There is no "at the same time". The requests, as they come in to the server, come in to the server in a specific order. The operations will be serialized (?) and executed in a specific order. Data access would be absolute chaos if the DB engine didn't manage this.

==> This can cause problems if the collection is already deleted from table when user wants to add to it.

That's *always* a problem in *any* situation where there's shared data. It's your responsibility as the programmer to handle it. The easiest way is to throw an error and tell the user: Too bad! "ERROR: The administrator has deleted the <collection> you're currently working on. Your action has been cancelled. No further work on this <collection> will be possible. Have a nice day!"

==> Would SQLServer handle this in any form? (table locks etc..)

Sure it would. But you've got to tell it to -- you'd probably set up a 1:M relationship with tables (1) Collection, and (2) CollectionElement. By nature of referential integrity, when the user attempts to add a CollectionElement to a Collection that has been deleted, the server will thow an error to your client app. It's your responsibility to write code to catch the error from the server.

NOTE: this can be done in multiple other ways, but referential integrity constraints are easy to setup. As an example, you could write a trigger on the INSERT of the CollectionElement that errors when it detects that the Collection has been deleted. There's other ways too. They all boil down to the server side throwing an error back to your client to tell it: Sorry, can't save -- the collection has been deleted.

==>If I am to handle it,since it is a Multithreaded app server, locking the table in the one request per thread model of tomcat makes no sense.

Locking an entire table rarely, if ever, makes sense. Home grown (non) "locking" schemes are a hack too. They never work. The SQL Server has what you need to handle this. You just need to be able to react in your client code when you discover that a situation like this has occurred.

==> Can anyone tell me how to deal with this possible problem?

See above.

Sgt. Sausage
Thursday, August 26, 2004

A good start may be to read the Concurrency chapter of Fowler's Patterns of Enterprise Application Architecture. Of course, the solutions may be heavier than you need. But you'll still learn a lot ;)

Mike Swieton
Thursday, August 26, 2004

Most web apps I've seen don't even worry about stuff like this. generally, it's not a common enough event to worry about...

VoidIfRemoved
Thursday, August 26, 2004

Cheap & cheerful solution:

Add a "sequence", int column to the tables, default to zero.

When saving a record, check current value (from web form) against the record in the database.

If different, raise error (usually "record has been modified by another user").

If same, increment current value and save all required data.

Sometimes you'll just have to break the rules, e.g. there are times when you need to use the sequence value from 1 record when updating multiple children.

CAVEAT: This is not a solution for high volume transactions, but it works fine for admin stuff. Not sure how well it works with deletions, as we do "soft" deletion here (where you mark a record as deleted, rather than phyisically deleting it).

Justin
Friday, August 27, 2004

Another vote for Fowler's PEAA.
But given the level of the question, most of it may be over the OP's head.