[2005] Multiple people updating gridview

[SLH]

I have a gridview that displays invalid records.
On the same page i have the facility to edit these records, which makes them valid.
When they become valid the disappear from the view, and so disappear from the gridview.

The problem is introduced with these events:

User1 loads the gridview.
User2 loads the gridview.
User2 clicks 'edit' on the gridview
User1 makes a record valid and it's removed from the view. I rebind the datasource so their gridview now shows the record removed.
User2 makes the changes and clicks 'update'

It seems to create a duplicate record when the update happens, although i'm not 100% on that. What does happen though is that if they have something selected and inbetween postbacks a record is removed the selected record is no longer the same (since all records have moved up one).

How do people usually get round this issue??

[rjv_rnjn]

This kind of concurrency should be handled at database level. I would suggest looking at ROWLOCK & HOLDLOCK statements of SQL server.

[SLH]

Ahh, that made for some interesting reading, although i'm not too sure how i'd go about implimenting it.

However, if i lock individual rows then surely although that will prevent user2 overwriting user1's change but if user 1 makes a change that causes the row to disappear from the view i'd still have the problem (whenever user2 next did a page postback his gridview rows would all move up one).

I don't really want to have to lock the whole view/table so only one user can use it at a time.

[SLH]

I've found this code that can be used my multiple processes at the same time, with each process picking a different record.

BEGIN TRAN TRAN1
SELECT TOP 1 * 
FROM tTest WITH (updlock, readpast) 
-- account for delay in processing time 
WAITFOR DELAY '00:00:10'
COMMIT

My question though, is how can i get the transaction to last from when the sqldatasource does a select, to when the user updates the records OR navigates away from the page.

This page shows one method, but doesn't indicate whether there would be open transactions (and hence locked rows) if the user doesn't update anything and just navigates away.

http://www.codeproject.com/KB/aspnet...ansaction.aspx

Any info on whether sqldatasource automatically commits/rollsback a transaction when the user leaves / it times out etc. would be very very much appreciated as i can't find anything on the subject.

[rjv_rnjn]

While following up on your question I found this MS Word document that has locking in good detail. It shows a way to set locking time-out. But I've never implemented a web solution that involves a locking mechanism. The simplest solution I can think of is another field that will be used to set whether the row is locked for editing or not. And in the Global.asax file trap the Session_End event to clean up those records.

[brucevde]

My question though, is how can i get the transaction to last from when the sqldatasource does a select, to when the user updates the records OR navigates away from the page.

You can't. Web programming is a disconnected environment. Transactions cannot span through all the environments (client, web server, db server).

Any locks in SQL Server are automatically removed once the connection is closed/disconnected.

If Concurrency in a disconnected environment is an issue either implement Row Versioning (in SQL Server Row Versioning is handled via the T-SQL TimeStamp datatype) or use "Compare All Values" (use the ConflictDetection property of the different DataSource controls or the ConflictOption property of the DBCommandBuilder)

This page might help http://msdn.microsoft.com/en-us/library/aa289498.aspx

[SLH]

I've read up on row versioning, but that is a solution to a problem i'm not too worried about. Users won't generally be altering the same row at the same time (if they do i don't care that then 1st change is lost, that won't matter).

My issue is that when a user makes a change to a row that subsequently removes it from a view the other user sees all rows in the gridview below where that item was move up one, which is a very bad user experience.

[mendhak]

Not sure what your code looks like or does, but it shouldn't create a new row, since you presumably have a primary key which is what guides the database operation subsequent to the user's actions.