List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:June 18 2008 9:34pm
Subject:Re: How to guarantee an integrity in this case?
View as plain text  
Hi,

> I want to do this:
>
> [1] read a row from table table_1 with select
> [2] change a row from [1] with update
> [3] change another row in table table_1 with update
>
> But queries [2] and [3] in the time of invoking, assume that row from
query
> [1] wasn't changed.
>
> Should I use LOCK TABLES? - I've tried that in query browser, but that
> doesn't prevent another thread to modify locked table.

One of the easiest ways is to start a read consistent transaction,
read the values for the rows you want to update.

Next, update the rows, but not just include the primary key to
locate the row, but also use the values you want to modify.

eg:

I want to update a customer record and change his address.

<read address info>

update customer
set address = 'New Address'
where customer_id = 121
and address = 'Old Address'

If this update fails to locate rows, you know that someone else
changed the value in the Address field before you were able
to update it.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Thread
How to guarantee an integrity in this case?tmk18 Jun
  • Re: How to guarantee an integrity in this case?Martijn Tonies18 Jun