Re:Simultaneus edits. locks, multiuser controling MS SQL6.5,
There are several methods you can use to deal with this problem. A
common one is to use optimistic locking. The basic idea is that each
record has a timestamp field which gets automatically updated when the
record is changed. So you get the record along with the timestamp, do
your editing, and then post the changes with a check to see if the
timestamp has changed (using tsequal() ) since you last fetched it. If
the timestamp has changed, you know the record was updated by someone
sometime after you fetched it for editing. In order for this to work,
the table must have a primary key or unique index, so that rows can be
uniquely identified in a where clause. If the table has no primary key,
then you can always use an identity column.
Example:
select ZipCode from Customers where CustId = :CId
<edit the fields: change the zip code>
update Customers set ZipCode = :Zip where CustId = :CId and
tsequal(:TimestampValue)
You should get an error if the timestamp has changed.
This should be covered in most decent SQL Server books. One to try is
SLQ Server 6.5 Unleashed, second edition by SAMS publishing. ISBN
0-672-30956-4
I recommend going to the bookstore and looking up 'optimistic locking'
in various SQL Server books until you find what you need.
Doug
Quote
Darko Hozjan wrote:
> Hi,
> We're developing program for MS SQL 6.5 database.
> We use Delphi 4 C/S.
> here is the situation:
> I have TQuery with TupdateSQL compnents. (using cached updates)
> We're posting changes to database with
> applyupdates command.
> what happenes is...
> When two users open for edit same record in table
> they can change it. That is ok. but..
> Now 1st user changes record and posts it to database.
> 2nd user changes record too, and in posts it to database
> without problem.
> That is wrong. Since 1st user changed record, 2nd user
> doesn't see changes. When he posts his changes to
> database, database (or BDE) should return error, that
> another user changed the current record and post should be
> canceled.
> BUT NOTHING HAPPENES, and 2nd user succesfully
> posts changes to database, what means that 2nd user
> overwrote changes 1st user made...
> HOW CAN I PREVENT THAT ??
> Thanks very much. DARKO
