Re: Confusion about database updates

• Previous message: Silvio Bierman: "Re: Servlet To Java Application Communication"
• In reply to: Burhan Khalid: "Re: Confusion about database updates"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Tue, 13 Apr 2004 15:02:44 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

On Mon, 12 Apr 2004 19:22:17 +0300, Burhan Khalid
<newsgroups@meidomus[dot]com> wrote:

>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: Silvio Bierman: "Re: Servlet To Java Application Communication"
• In reply to: Burhan Khalid: "Re: Confusion about database updates"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]