Using TIMESTAMP datatype to reduce database corruption

There will be times when 2 people are trying to change the same record at the same time.  For example, a Human Resources Manager might want to change an Employee’s salary and an IT person might want to update the Employee’s password.  This is not an uncommon problem and applications can be written to help avoid this but there is no way it can be prevented 100% of the time.  Therefore, it is a good idea to use the tools provided by SQL to catch this situation and alert the application.  Let’s discuss one concept.

One method is to use a “TIMESTAMP” field in the database table.  The “TIMESTAMP” field is automatically updated each time the record is updated.  Then the UPDATE Stored Procedure would check to see if the “TIMESTAMP” has changed since the record was first read by the application.  If so, then it aborts the UPDATE and alerts the calling application.  The key is that this “TIMESTAMP” value must be always returned by the “SELECT” Stored Procedures so the calling application can provide it to the UPDATE Store Procedure for comparison.  For example, below is a short Update Stored Procedure that updates “Salary” or “Password” or both:

CREATE  PROCEDURE UpdEmployee
@PASSWORD VARCHAR(20) = NULL,
@Salary  DECIMAL(14,4) = NULL,
@EmployeeID INT,
@RecVer  BIGINT

DECLARE
@QueryCheck CHAR(20),
@ErrorNum INT,
@RowCount INT

BEGIN TRANSACTION

————————————————————————-
– Update the Employee record specified by the Primary Key
–  (@EmployeeID) with the values if specified.  Note the
– COALESCE statement allows the @Password and @Salary
– parameters to be optional and if not specified (NULL) then
– the original values will be written back.  Also notice the
– RecVer is compared to the existing RecVer in the table
– and if not the same then the update is not executed.  We
– cast the the RecVer from BIGINT to TIMESTAMP to make
– the comparison proper since we don’t pass TIMESTAMP as
– a parameter.  The SELECT procedure would return this value
– as a BIGINT to the caller since it is easier to store it as such
– in most programming languages.
————————————————————————–
UPDATE Employee
SET Password = COALESCE(@Password, Password),
 Salary = COALECE(@Salary, Salary)
WHERE EmployeeID = @EmployeeID
 AND RecVer = CAST(@RecVer AS timestamp)

SET @QueryCheck  = CAST(@@ERROR AS char(10)) +
                                            CAST(@@ROWCOUNT AS char(10))
SET @ErrorNum   = CAST(LEFT(@QueryCheck, 10) AS int)
SET @RowCount   = CAST(RIGHT(@QueryCheck, 10) AS int)

—————————————————————————
– Checking if we had an error.  We always do this for every
– UPDATE or INSERT but this has nothing to do with this
– article but I put it here for reference.
—————————————————————————
IF @ErrorNum <>  0
 BEGIN
  ROLLBACK TRANSACTION
  RAISERROR(@ErrorNum, 16, 1)
  RETURN @ErrorNum
 END
———————————————————————–
– If the specified row was not updated, @RowCount = 0,
– then the RecVer was not the same so alert the calling
– application so they can tell user and maybe refresh the
– data again in their application.
———————————————————————–
IF @RowCount <> 1
 BEGIN
  ROLLBACK TRANSACTION
  RAISERROR( ‘Update Problem’, 16, 2)
  RETURN 123456
 END
————————————————————
– If you got this far, everything is OK, so commit change
————————————————————
COMMIT TRANSACTION

AS

RETURN (0)

 The calling application can then just provide “TRY/CATCH” blocks around the call to the SQL Server Stored Procedure and if the procedure throws an error the Return value “123456′ would indicate that it was due to an attempt to modify data that had been modified since first reading.  The application has to be written to handle this situation, but at least the database provides safeguards against this sort of situation from corrupting your data by using the “TIMESTAMP” datatype.