User input validation and database constraints

Assume we have the following database structure and accompanying web page:

The database consists of two tables, t1 and t2. The table t2 consists of two columns, one is called id_bar which is just a unique id, and the other is called name and is used to store some string used by the ui. The other table, t1, have two columns, one called id_foo which is just a unique id, and one called id_bar which must point to some row in t2 (i.e. it's a foreign key).

The relevant part from the web page is (in pseudocode):

Form form = new Form();
form.build_and_add_select_from_db_table("t2", "field1");
form.add_submit_button();

if (request.is_post() && form.is_valid(request.get_post_data())) {
    Add a new row to t1, using the data from the request
    response.redirect(<somewhere>);
}

print form.render(); // this will display validation errors as well

So, the Form class will render a form consisting of a select element with the rows from t2 as options, and an input used to submit the form.

Now to the problem, furthermore suppose that some other part of the web site allows users the delete rows from t2, then the following is possible:

1. User A requests the web page containing the form described above, with t2 consisting of the rows (1, "a"), (2, "b") and (3, "c") where e.g. (1, "a") means that id_bar = 1 and name = "a".
2. User A selects (1, "a") from the select element
3. User A submits the form
4. The server-side script start validating the data, and seeing that it's valid entering the true branch of the if statement (however, not yet having inserted the new row into the database)
5. Some other user B removes the selected row from t2 (that is, (1, "a")) using some other part of the web site
6. The server-script from step 5 continues and tries to insert the data into the database, but this results in a constraint violation because the id 1 no longer refers to a row in t2

So it seems that the server-script has to handle this scenario as well; that is, to re-render the form and inform user A that the selected option no longer is available.

There are other, similar, situations caused by this non-atomicity, e.g. if we have a table called user where each user is supposed have a unique username, by the same reasoning as above we cannot simply:

if (check if the username already exists in the database)
  display_error();
else
  insert_new_user_into_database();

One solution (in both cases) is to optimistically try to insert the new row and then in some way figuring out what constraint was violated, and why, and from this, by (I guess) parsing error messages and error codes, informing the user what went wrong. This feels very messy however:

try {
  if (request.is_post() && form.is_valid(request.get_post_data())) {
    Add a new row to t1, using the data from the request
    response.redirect(<somewhere>);
} catch (DatabaseException e) {
  if (e.is_some_constraint_violation()) {
     // Find out what field caused the violation
     // and generate an appropriate error message,
     // possibly also removing alternatives from some form fields, 
     // it will rethrow the exception or something if it can't
     // figure out what happened.
     form.handle_database_constraint_violation(e);
  } else
     throw;
}

Another solution might be some kind of locking?

if (request.is_post())
  lock_everything();

Build form ...

if (request.is_post() && form.is_valid(request.get_post_data())) {
  Insert the new row into the database
  unlock_everything();
  response.redirect(<some other page>)开发者_开发知识库;
} else
  unlock_everything();

This seems like a very common problem (e.g. requiring a unique username), so is there some well-known standard solution to situations like these presented here?

One way of doing it is to not actually delete records from tables but instead use a soft delete to mark them as deleted so that the UI (and other application layers) can tell they are not there.

ORM frameworks, such as Hibernate for example, handle this sort of "concurrent modification" problem by using a version field. Then any updates end up with a part of their where clause looking like where myVersion = dbVersion, and if then catch exceptions and figure out why things change.

This prevents changes from just blindly going through, and also does it in an atomic way so that the DB is in control of the atomicity of the transaction, not the application code. (locks in app code are messy)