Re: Confusion about database updates

• Previous message: David Harper: "Re: Confusion about database updates"
• In reply to: David Harper: "Re: Confusion about database updates"
• Next in thread: David Harper: "Re: Confusion about database updates"
• Reply: David Harper: "Re: Confusion about database updates"
• Reply: Glacial Spain: "Re: Confusion about database updates"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Mon, 12 Apr 2004 19:22:17 +0300

David Harper wrote:
> Burhan Khalid wrote:
>
>> 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
>> 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.
>
>
> 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.

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
> 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.

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
>

• Previous message: David Harper: "Re: Confusion about database updates"
• In reply to: David Harper: "Re: Confusion about database updates"
• Next in thread: David Harper: "Re: Confusion about database updates"
• Reply: David Harper: "Re: Confusion about database updates"
• Reply: Glacial Spain: "Re: Confusion about database updates"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]