Confusion about database updates

Thread view:

Burhan Khalid - 12 Apr 2004 09:32 GMT

Greetings:

I'm developing an application that will be running on many different
clients, all connecting to the same database server (MySQL). The
application is still in its initial phase, and is a conversion from a
rather unmanagable PHP application.

I have a few concerns about updating databases. Currently, when a new
record is inserted, a unique id, which is auto-updated is generated in
the database. This works well, since there is very little possiblity of
two or more clients submitting a request at the same point in time.

The problem occurs when connection to the database server is lost.
In this situation, the application should still continue to work in
offline" mode, using either an internal database (hsqldb) or writing to
a XML file. I have not decided what yet. Now when the connection to the
database server is restored, how would I update the "main" database
server with records from each client?

Consider the following scenario :

User A and User B are both connected to the database server, and the
current record id is 200. Now the database connection is lost. Both User
A and User B create a new record (internally). Both now are on record id
201. When the database connection is restored, how would I insert the
records of both clients into the database? There cannot be two records
with the same primary key of 201.

I considered using a timestamp check and updating the database
accordingly. This however would not work because the record id is used
in other parts of the application, and it needs to be unique. Once the
record is generated (and printed), all references to that record are
made using its record id. If I were to change the record id the the
database, it would invalidate the printed copy.

Any suggestions, ideas?

Thanks again,
Burhan

Reply to this Message

David Harper - 12 Apr 2004 15:03 GMT

> Greetings:
>
> I'm developing an application that will be running on many different
> clients, all connecting to the same database server (MySQL).

That sounds good. MySQL can easily handle many simultaneous clients. I'm
currently running speed/resilience tests where eight nodes of an HP
AlphaCluster all open multiple connections to a MySQL server running on
a Pentium/Linux box and collectively throw about 2 gigabytes of data at
it. The server barely breaks into a sweat, and even with a somewhat
complicated set of tables, it loads the data in under an hour.

> I have a few concerns about updating databases. Currently, when a new
> record is inserted, a unique id, which is auto-updated is generated in
> the database. This works well, since there is very little possiblity of
> two or more clients submitting a request at the same point in time.

I'm assuming that when you say "a unique id, which is auto-updated", you
actually mean that you've defined primary key with the auto_increment
qualifier. In this case, MySQL will allocate each new record an ID that
is unique within the table. Your clients can retrieve the value of the
ID using the getGeneratedKeys method of the java.sql.Statement which
executed the insert operation.

> The problem occurs when connection to the database server is lost. In
> this situation, the application should still continue to work in
[quoted text clipped - 11 lines]
> records of both clients into the database? There cannot be two records
> with the same primary key of 201.

The answer is *not* to allocate an ID to the records that you are
storing internally. Let the MySQL server do that when your client
application manages to re-establish a connection.

After all, unless I've misunderstood you, User A and User B won't be
aware of one another's records until both client programs are able to
re-connect to the MySQL server and insert the records that they have
been keeping "on hold". The database is the only way for one user to
know *anything* about the data that other users are holding.

But taking a broader view, you have to ask yourself what scenarios are
likely to lead to your client programs losing their connection to the
database server?

The MySQL server itself could crash, though this is *very* unlikely.
Speaking from personal experience, I've run MySQL servers for nine
months at a stretch, and only had to shut them down because our sysops
wanted to upgrade the operating system on the host machine.

The client program could crash, but in that case you don't have time to
write the unsaved records to an XML file or alternate database anyhow!

You could lose the network connection between the client program and the
MySQL server. Is your application running on a corporate intranet? Or
across the global Internet? [And do you have crazed back-hoe operators
in your neighbourhood? ;-)] In this case, both the MySQL server and the
client program are still running, so you *can* write the unsaved records
to an alternate datastore.

David Harper
Cambridge, England

Reply to this Message

Burhan Khalid - 12 Apr 2004 17:22 GMT

>> Greetings:
>>
[quoted text clipped - 42 lines]
> storing internally. Let the MySQL server do that when your client
> application manages to re-establish a connection.

I considered this option. The problem here is that the primary key,
which is auto incremented, is part of the printed record for each
transcation.

Consider an invoicing application. The primary key would be the invoice
number. So when a customer requests a printed invoice, we have to
generate an invoice number. It wouldn't be practical to not print the
invoice at the time of purchase, nevermind the state of the connection
between the client application and the database server.

> After all, unless I've misunderstood you, User A and User B won't be
> aware of one another's records until both client programs are able to
[quoted text clipped - 20 lines]
> client program are still running, so you *can* write the unsaved records
> to an alternate datastore.

The reason I am worried about connectivity is because the database
server is located offsite in a datacenter, accessible via the internet
only. I would hate to rely on the internet connection to be constantly on.

I considered the possibility of having a local database server. The
issue here is that there are two separate office locations that will be
needing access to the application. In addition, the application will
also be used by the sales force when visiting client sites. The
solution for the sales force is simple enough -- a web interface that
links with the same java "code base". However, the same issue with
connectivity remains with the two remote offices.

Thank you for your suggestions though, I appreciate your time :)

> David Harper
> Cambridge, England

Reply to this Message

David Harper - 12 Apr 2004 18:47 GMT

> The problem here is that the primary key, which is auto incremented,
> is part of the printed record for each transcation.
>
> Consider an invoicing application. The primary key would be the
> invoice number. So when a customer requests a printed invoice, we
> have to generate an invoice number.

Here's a possible solution, which doesn't depend upon your sales people
being able to connect to the master database at your head office at all
whilst they are travelling and making sales.

I'm guessing that your sales people have laptops. You can run a MySQL
server on a laptop running Windows. Design your application so that it
stores invoice information to the MySQL server on the laptop. That way,
all of the transactions stay in the laptop.

Also, give each of your sales people a unique username, and require them
to "login" whenever they start up the client application. Their username
is logged to the laptop's database as part of the invoice information.

When your sales people visit your head office, they run a second
application which transfers all of the new invoices from the MySQL
database on their laptop to the master MySQL database.

> Thank you for your suggestions though, I appreciate your time

You're very welcome. We're here to help :-)

David Harper
Cambridge, England

Reply to this Message

Glacial Spain - 13 Apr 2004 16:02 GMT

Instead of simply auto-generating the new record ID, can you can use a
client ID as part of the new record ID?

Jim dJ

>>> Greetings:
>>>
[quoted text clipped - 94 lines]
>> David Harper
>> Cambridge, England

Reply to this Message

Confusion about database updates

Free Magazines