Best way to avoid multiple user conflict?

[LadyReader]

I have a VB.net windows app that's been in production a few years. There's a main page with a datagrid showing rows of items. The user can click on an row to display another page with the selected item's detail.

Recently 2 users displayed the same row concurrently; the first user clicked on a button to email the item detail to a 3rd party outside the company while the other user was still making changes to the detail.

To prevent this kind of contention, I thought of adding a field to the SQL backend where I would store the name if the user who retrieves that record. If the field is populated than I would disallow any other user from updating the record or emailing it. I would also display the name of the first user if a subsequent user tried to see that record (forcing a read-only status). The SQL field would be reset to NULL when the first user closes the detail window.

Is there a better way?

Thank you.

 

[jmcilhinney]

You wouldn't implement anything like that yourself. Proper databases support locking of record ands tables, which you would implement in your SQL code.

That said, it is often undesirable to lock data, which is exactly why ADO.NET doesn't do it by default. If a user retrieves a bunch of data and locks it then no-one else will be able to access that data until the lock is released. If the user doesn't end up making any changes to the data, or all the data, then other users have been excluded from accessing it without need.

ADO.NET works on the assumption that everyone should be allowed access to the data and then, if collisions occur, you handle them at that point. That's not to say that there may not be times where locking records is appropriate, but you should check the documentation for your database to see how it's implemented because your own system just won't work properly in all cases.

 

[LadyReader]

Thank you for replying, jmcilhinney.

Actually, I designed the db, too. You can lock records on SQL during a transaction but for the problem described here, there may be no SQL transaction actually executing during the time period when another user tries to email the record. The first user may just have it displayed on his/her desktop.

The kind of contention I'm talking about is not at the db level, that's why I am considering handling the locking manually.