best practices for database + resultset concurrency (both UI and control issues)

Question

I'm working on a viewer program that formats the contents of a database. So far it's all been read-only, and I have a Refresh button that re-queries the database if I want to make sure to use current data.

Now I'm looking at changing the viewer to an editor (read-write) which involves writing back to the database, and am realizing there are potential concurrency issues: if more than one user is working on the database then there are the possibilities of stale data & other concurrency bugaboos.

What I'm wondering is, what are appropriate design patterns both for the database and the application UI to avoid concurrency problems?

To be bulletproof, I could force the user to use an explicit transaction (e.g. it's in read-only mode most of the time, then they have to push an Edit button to start a transaction, then Commit and Revert buttons to commit or revert the transaction) but that seems clunky and wouldn't work well with large sets of changes (Edit, then 1 hour's worth of changes yields an overly large transaction and may prevent other people from making changes). Also it would suck if someone's making a bunch of changes and then it fails -- then what should they do to avoid losing that work?

It seems like I'd want to notify the user when the relevant data is being changed so that the granularity to changes is small and they get cued to refresh from the database & get in the habit of doing so.

Also, if there are updates, should I automatically bring them into the application display? (assuming they don't clobber what the user is working on) Or should the user be forced to explicitly refresh?

A great example, which is sort of close to the situation I'm working on, is filesystem explorers (e.g. Windows Explorer) which show a hierarchy of folders/directories and a list of objects within them. Windows Explorer lets you refresh, but there's also some notification from the filesystem to the Explorer window, so that if a new file is created, it will just appear in the viewport without you having to hit F5 to refresh.

I found these StackOverflow posts, but they're not exactly the same question:

• Web services and database concurrency
• Distributed Concurrency Control
• C# Database Application

Answer 1

1. Only display one record for editing at a time.

2. Submit new values conditionally, after applying whatever domain-specific validation is appropriate. If the record has changed in the meantime (most DAL-type software will throw an exception so you don't need to check manually), display the current (changed) values, advise the user, and accept another try (or abandon). You may want to indicate the source and timestamp of the change you are displaying.

That's the simplest reliable standard pattern I know of. Trying to induce the user to explicitly choose "Display" vs. "Edit" mode is problematic. It locks the record for some indeterminate amount of time, and it's not always reliable that you know when the user (for instance) gives up, turns off their computer, and goes home.

If you have a case where you have a parent record with editable child records (e.g. the line items on a purchase order), it gets more complex but let's worry about that later? There are patterns for those too.

Answer 2

Optimistic locking works fine for most cases where your records are composed of short simple fields (e.g., a short string or single numeric value per field), giving users the greatest access to the data, and not forcing them to worry about locks and stuff. Apply a write lock only when actually in the process of saving a record. No records are locked while anyone is merely editing. If the app finds a record it's trying to save is already locked, then the app simply retries a short time (<500 ms) later. There’s no need to alert the user (other than maybe hourglass/pointer feedback if last longer than 500 ms) since no lock is ever in place long enough to matter to the user. When User A saves a record, the database only updates the fields that User A has changed (along with any other fields that depend on those changed values). This avoids overwriting with old values the fields changed by User B since User A retrieved the record.

The implicit assumption is that whoever edits a field of a record last has the final say, which not an unreasonable way of doing business. For example, User A retrieves a record and edits a field, then User B retrieves a record and edits the same field. User B saves, then User A saves. User A’s changes over-write User B. User B’s work was “a waste,” but that sort of thing is going to happen anyway when users share data. Locks can only prevent wasted work when users happen to try to edit the same record in the same thin slice of time. However, the more likely event is that User B edits the record’s the field and saves, then User A edits the field and saves, again wasting User B’s work. There’s nothing you can do with locks to prevent that. If there’s really a high chance of wasted work by user interactions, it’s better to prevent it through the design of the business process rather than database locks.

As for the UI, there are two server styles I recommend: (1) Real Time, and (2) Transactional.

• In Real Time style, the users’ displays automatically correspond as closely as practical to what’s in the database. Refreshes are automatic either being based on a short period (every fives seconds), or “pushed” to the user when changes are made by others. When the user enters a field and makes an edit, the app suppresses refreshes for that field, but continues to refresh other fields and records. There is no Save button or menu item. The app saves a record anytime a user edits a field and then leaves it or hits Enter. When the user starts to edit a field, the app changes the field's appearance to indicate that things are tentative (e.g., changing the border around the field to a dashed line) in order to encourage the user to hit Enter or Tab when done.

• In Transactional, the users' displays are presented as a snapshot of what's in the database. The user must explicitly save and manually refresh data with buttons or menu items, except the app should automatically refresh a record when the user starts to edit it or after the user saves it. The user can edit any number of fields or records before saving. However, you can encourage frequent saves by changing the appearance of edited fields to indicate their tentative state, like recommended for Real Time. You can also display a timestamp or other indication of the last refresh to encourage users to refresh frequently.

Generally, Real Time is preferred. Users don’t have to worry about stale data or losing a lot of work by forgetting to save. However, use Transactional if it is necessary to maintain sufficient database performance. You probably don’t want Real Time if updating a field typically takes more than 1.0 second for server response. You should also consider Transactional if users’ edits trigger major events that are difficult to reverse or can produce wasted work (e.g., changing a budget value triggers notice to superior for approval). An explicit Save command is good for saying, “Okay, I’ve checked my work, let ‘er rip.”

Answer 3

a good working way i use: don't open tran until really applying changes to db (after user presses Save button) don't even need to refresh record before beginning user's edit dialog. but just before applying changes, check if record is changed by another user in your app code. it's done trough a select statement just before update statement. if record with old field values (in DataSet) not exists in database, alert user that 'record is changed by another user' and user must close dialog, refresh record and start editing again. else open tran and the rest.