"Concurrency" issue: sloppy coding as a result.

[NeoTeq]

Hey people,

I'm messing around with Asp.net 1.1, and I've created a page that contains a datagrid which can be edited. The datagrid has a datatable as a source, which is stored in cache. I've got functions in place that get the table out of SQL Server and add it to cache when it's not there, to force refresh the table from SQL Server (to revert) and to update the table in SQL Server based on changes made to the datagrid (to update it).

This all works perfect. However, there is one major flaw in my code, that I know of:

When two users are editing simultaneously, I need some way to stop the second user from editing a row that's already been changed by the first user (since the update is only done when a user clicks on the 'Update' button). My first thought was to add a column to the datatable which contained the sessionid, which worked fine, but had the undesirable result of showing that ID in my datagrid (yes, autogeneratecolumns - I'm the lazy type). Now I haven't found a satisfactory way of keeping the sessionid yet - Though there is one way that works. I'm currently storing it in Row.RowError. Scary? Yes. Does it work? Unfortunately, yes.

Any ideas to keep my code neat and clean and not do this?

[VGR]

ok, I dunno the datagrid component of .Net, but if it even comes close to the Delphi one, it means it usually locks the rows when editing. You probably have to say "editmode" or the like when reading the dataset from the query.

SQL-Server should have row-level locking, I hope it has.

Else, you'll have to explicitly lock yourself the row being modified by the user until he/she presses "update", so that no concurrent update could run.

Alternatively (like with MySql), you could let the RDBMS do the job itself, it's made for that purpose after all. It ***knows*** if a record has to be locked of not.

The best (safest for you) solution IMHO is to explicitly LOCK the table while you update one row. SQL-Server, if SQL99-compliant, should support the LOCK command. Look into the documentation.

Best of luck ;-)

[NeoTeq]

As far as I can see, the asp.net datatable (the object which is in fact cached, and on which the grid is based) does not have the possibility to lock a row so that only a certain user (web user - means session id) can edit it. Any way, the whole point of the exercise is to edit rows in the asp.net cache so that database access is severely limited. Only in three situations does the application connect to the database:
- When the cache expires
- When a user forces a refresh from the database to the cache
- When a user forces an update from the cache to the database

Also, I want to keep this as generic as possible. So I'm not going to create custom objects that do support this behavior, and can save the current editor, etcetera. I will probably start over and leave this project, try to find some other way around it and avoid concurrency alltogether.