concurrent updates

Thread view:

VisionSet - 15 Dec 2003 12:29 GMT

I have a RDB table where many rows are for 1 employee.
The application reads the rows for a certain employee.
The rows are mapped to Objects and these objects are modified by the
application.
The objects are then persisted to the database.

However, this is a distributed application and those records need to be read
by and updated by other users. And locking the whole table for read & write
seems a little draconian!

Here is what I propose.
The application reads the rows for a certain employee.
The rows are mapped to Objects
A copy of these objects is made.
User thinks, time passes
One set is modified by the application.
When the user wants to commit the changes:
the table is locked,
the rows are re-read and mapped to objects.
The collection of objects are compared with the unmodified copy made
previously
if they are equal the modified objects overwrite the existing ones.
If they are not equal checks are run to see if it affects the proposed
update, if it does the user is informed for ammendments.
table is unlocked.

I'm new to concurrent updates, it all seems a bit of a nightmare!

Does this seem okay? any tips?

Signature

Mike W

Reply to this Message

Marvin - 19 Dec 2003 19:29 GMT

Hi Visionset,

> I have a RDB table where many rows are for 1 employee.
> The application reads the rows for a certain employee.
[quoted text clipped - 25 lines]
>
> Does this seem okay? any tips?

Yes, thats a nightmare, but one way to do it from the application
programmers point of view. But luckily thats a problem relational
databases can handle:-)
In most cases, your RDBMS should offer a feature called
"transactions", that should ensure, that concurrent accesses to
tables/tuples will be coordinated. The use of transactions will be
_much_ faster than your approach and much more practical. For MySQL
you could take a first look at

http://www.devshed.com/Server_Side/MySQL/MySQL_transactions/

but you can find a lot of documentation on the web.

Have fun,

Marvin

Reply to this Message

VisionSet - 22 Dec 2003 15:17 GMT

> Hi Visionset,
>
[quoted text clipped - 38 lines]
>
> http://www.devshed.com/Server_Side/MySQL/MySQL_transactions/

I don't see how transactions can help me here.

Transactions are useful when the application has all the information as to
whether it should commit the data.

Here I only know if I can commit the data if I read the database in the
state it will be in when I commit. In other words I must lock the table (or
at least the relevent set of records) in between that final read and the
commit, which will be less than a second.

I now have a slightly more java specific sequence of events

Initial read of 0-n rows
Map rows to objects and put in java.util.Set
Deep copy this Set1 to Set2
User modifies Set1, changes state on objects within, removes & adds.
User initiates update.
App locks table
Second read of same 0-n rows
Map rows to objects and put in Set3
Compare Set2 with Set3
if equal do update with Set1 & unlock table
if not see if it matters...

Signature

Mike W

Reply to this Message

concurrent updates

Free Magazines