Editing and enhancing database documents by numerous customers

I have actually made database tables (normalised, on an MS SQL server) and also developed a standalone windows front end for an application that will certainly be made use of by a handful of customers to add and also modify details. We will certainly add an internet user interface to permit looking accross our manufacturing location at a later day.

I am worried that if 2 customers start editing and enhancing the very same document after that the last to devote the upgrade would certainly be the 'victor' and also vital details might be shed. A variety of remedies enter your mind yet I'm not exactly sure if I am mosting likely to create a larger frustration.

  1. Not do anything and also wish that 2 customers are never ever mosting likely to be editing and enhancing the very same document at the very same time. - Might never ever happed yet what happens if it does?
  2. Editing and enhancing regimen can store a duplicate of the initial information along with the updates and afterwards contrast when the customer has actually ended up editing and enhancing. If they vary show customer and also comfirm upgrade - Would call for 2 duplicates of information to be saved.
  3. Add last upgraded DATETIME column and also examine it matches when we upgrade, otherwise after that show distinctions. - calls for new column in each of the pertinent tables.
  4. Create an editing and enhancing table that signs up when customers start editing and enhancing a document that will certainly be examined and also protect against various other customers from editing and enhancing very same document. - would certainly call for carful idea of program circulation to stop predicaments and also documents coming to be secured if a customer collisions out of the program.

Exist any kind of far better remedies or should I go with among these?

2019-05-03 22:44:06
Source Share
Answers: 4

Another alternative is to examine that the values in the document that you are transforming are the still the like they were when you began :

    customer_nm AS customer_nm_orig
FROM demo_customer
WHERE customer_id = @p_customer_id

(display screen the customer_nm area and also the customer transforms it)

UPDATE demo_customer
SET customer_nm = @p_customer_name_new
WHERE customer_id = @p_customer_id
AND customer_name = @p_customer_nm_old

    RAISERROR( 'Update failed: Data changed' );

You do not need to add a new column to your table (and also maintain it approximately day), yet you do need to create even more verbose SQL declarations and also pass new and also old areas to the saved procedure.

It additionally has the benefit that you are not securing the documents - due to the fact that most of us recognize that documents will certainly wind up remaining secured when they need to not be ...

2019-12-02 00:15:05

@ Mark Harrison : SQL Server does not sustain that syntax (SELECT ... FOR UPDATE ).

The SQL Server matching is the SELECT declaration hint UPDLOCK.

See SQL Server Books Online for additional information.

2019-05-07 19:33:29

If you anticipate seldom crashes, Optimistic Concurrency is possibly your best choice.

Scott Mitchell created a thorough tutorial on applying that pattern :
Implementing Optimistic Concurrency

2019-05-07 17:42:50

The database will certainly do this for you. Consider "select ... for upgrade", which is made simply for this example. It will certainly offer you a write lock on the picked rows, which you can after that devote or curtail.

2019-05-07 17:23:59