"dc_it" wrote:

> Bracket your user actions between BEGIN TRANS...COMMIT TRANS and to an
> UPDATE WITH(ROWLOCK) setting a dateModified field to getDate()
> immediately after BEGIN. All subsequent modifications will be queued
> until you fire your update and commit. You could also do a check on the
> dateModified field and simply warn the user if it has incremented since
> they read the record, but doing a field-by-field comparison is more
> work than is necessary.
>
> The warning is almost always the preferable solution, since it is
> dangerous to assume that such a collision should proceed in whichever
> more or less arbitrary order when human intervention is not only
> possible, but desirable.
>
>

> I just upsized my Access application (mail-order processing system)
> and linked to SQL server 2000 via ODBC tables links from an Access
> 2000 front end.
> By doing so the standard optimistic locking of SQL server 2000 comes
> into force and the option "lock edited record" on the Access Client
> gets ignored. I cannot work with optimistic locking because if e.g.
> an order gets entered in a table and e.g. a stock allocation job runs
> in the background that updates the order header,
> all the entered data will be lost (or must be copied to the clipboard
> and edited ...).
> Do you know how to overcome this problem - problems like this should
> actually be quite common ...
> Several years ago I used to work with a database called DataFlex.
> This database had the best locking mechanism I every have experienced.
> If one enters dat in a form the pulled record does not get locked
> (like optimistic locking).
> It only gets locked shortly before the update - it workes like this:
>
> read a record from the table and display in the form
> user modifies data in form
> user clicks update button
> record gets locked and reread into the record buffer
> client program compares every form field with the record buffer if
> any data was changed
> if a field was changed the record buffer was overwritten with the
> data the user entered
> the record gets saved and unlocked
>
> This way only the changed data fields get updated and changes of
> other users do not get overwritten!
>
> Is there a way on SQL server to do something similar or how do you
> deal with this problem?
> I also thought of writting an INSTEAD OF UPDATE trigger and check if
> any column is different from the Inserted to the "REREAD" record
> buffer and apply the above mentioned logic of DataFlex but this
> streches my SQL knowledge just a bit too far ...
> May be you can help?
> Any comments are appreciated!
> Thanks in advance.
>
> Oliver

"David Gugick" wrote:

> Oliver wrote:
> > I just upsized my Access application (mail-order processing system)
> > and linked to SQL server 2000 via ODBC tables links from an Access
> > 2000 front end.
> > By doing so the standard optimistic locking of SQL server 2000 comes
> > into force and the option "lock edited record" on the Access Client
> > gets ignored. I cannot work with optimistic locking because if e.g.
> > an order gets entered in a table and e.g. a stock allocation job runs
> > in the background that updates the order header,
> > all the entered data will be lost (or must be copied to the clipboard
> > and edited ...).
> > Do you know how to overcome this problem - problems like this should
> > actually be quite common ...
> > Several years ago I used to work with a database called DataFlex.
> > This database had the best locking mechanism I every have experienced.
> > If one enters dat in a form the pulled record does not get locked
> > (like optimistic locking).
> > It only gets locked shortly before the update - it workes like this:
> >
> > read a record from the table and display in the form
> > user modifies data in form
> > user clicks update button
> > record gets locked and reread into the record buffer
> > client program compares every form field with the record buffer if
> > any data was changed
> > if a field was changed the record buffer was overwritten with the
> > data the user entered
> > the record gets saved and unlocked
> >
> > This way only the changed data fields get updated and changes of
> > other users do not get overwritten!
> >
> > Is there a way on SQL server to do something similar or how do you
> > deal with this problem?
> > I also thought of writting an INSTEAD OF UPDATE trigger and check if
> > any column is different from the Inserted to the "REREAD" record
> > buffer and apply the above mentioned logic of DataFlex but this
> > streches my SQL knowledge just a bit too far ...
> > May be you can help?
> > Any comments are appreciated!
> > Thanks in advance.
> >
> > Oliver
>
> Yes. This is easy. Add a TIMESTAMP column to each table where you need
> this support. A timestamp column is not a date, but a column that SQL
> Server automatically changes each time a row is updated. When you
> initially query the row data, select the TIMESTAMP as well. When you
> save the data from your stored procedure (ideally, you 'll be using
> stored procedures), compare the TIMESTAMP you selected with the
> timestamp currently in the row. If they are different, then you know the
> data was changed in the interim and can raise an error and have the
> client application automatically re-query the data. Your update
> statement can look something like this:
>
> Update dbo.MyTable
> Set
> Col1 = @Col1,
> Col2 = @Col2
> Where
> ColPK = @ColPK
> and
> timestamp = @timestamp
>
> If @@ROWCOUNT != 1 -- either the row was changed or it no longer exists
>   RAISERROR ...
> Else
>   -- Everything is good to go
>
>
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
>

> Hi David,
>
> Thanks for your tips but this sounds like a lot of programming to
> overcome
> a problem that actually is a server's job.
>
> Raising an error message when data changed in the background can only
> be useful if there is a user at the other end.
>
> What happens if a program gets caught out by a user e.g. the user
> changes data during the time the program read the row to update one
> column?
>
> You will have to program some code to get arround the problem for
> every transaction you are trying to do in a job like stock
> allocation, release orders for delivery... - that would be too much
> work for me as my application is big!
>
> I think it would be much better if the server could check in an update
> trigger if there was a concurrent update of columns and just updates
> the column(s) that were changed by the current transaction. This way
> all changes other transactions did will be kept and nobody has to
> decide which data/changes to keep.
>
> I am quite new to SQL server programming and do not know all the ins
> and
> outs of trigger transaction programming.
> Could you or somebody else suggest some code how to achieve this?
>
> Thanking you in advance.
>
> Oliver

"David Gugick" wrote:

> Oliver wrote:
> > Hi David,
> >
> > Thanks for your tips but this sounds like a lot of programming to
> > overcome
> > a problem that actually is a server's job.
> >
> > Raising an error message when data changed in the background can only
> > be useful if there is a user at the other end.
> >
> > What happens if a program gets caught out by a user e.g. the user
> > changes data during the time the program read the row to update one
> > column?
> >
> > You will have to program some code to get arround the problem for
> > every transaction you are trying to do in a job like stock
> > allocation, release orders for delivery... - that would be too much
> > work for me as my application is big!
> >
> > I think it would be much better if the server could check in an update
> > trigger if there was a concurrent update of columns and just updates
> > the column(s) that were changed by the current transaction. This way
> > all changes other transactions did will be kept and nobody has to
> > decide which data/changes to keep.
> >
> > I am quite new to SQL server programming and do not know all the ins
> > and
> > outs of trigger transaction programming.
> > Could you or somebody else suggest some code how to achieve this?
> >
> > Thanking you in advance.
> >
> > Oliver
>
> I don't agree with your assessment. This is an application programming
> issue and not one for the database to manage on its own. You're
> suggesting that the server update a row that has been updated in the
> interim by another process just because the columns being updated are
> not the same. I would argue that there's no way for SQL Server to know
> if changing a single column value in a row would somehow affect business
> rules and know whether or not the proposed row changes are valid.
>
> Many applications can deal with this scenario by assuming the last
> update should be the most current. In that scenario, there is no
> additional programming required. If you need to manage concurrency and
> changes to a row by another session to avoid overwriting those changes,
> you should use a timestamp. You pass the timestamp value with the other
> column values to your stored procedure and the procedure does the work.
> The application should be able to handle the condition where the row was
> not updated because an error was raised and then requery the data and
> inform the end-user. All your DML should be done in stored procedures.
> In the case where there is no end-user, your application code should log
> an error condition that can be managed manually in an interactive
> fashion or it could requery the data, assuming this is something it can
> work around.
>
> For a nightly batch process, you could lock the entire table within your
> transaction.
>
> > What happens if a program gets caught out by a user e.g. the user
> > changes data during the time the program read the row to update one
> > column?
>
> I'm not sure what you are describing here. A change is a change.
> Presumably, you have a stored procedure to manage each type of change to
> your data. Maybe you can elaborate on this part a little more.
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
>