Re: Lost updates vs resumable connections/transactions

On 12/16/2004 8:52 AM, Jens Lechtenboerger wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
>
>> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>>
>>> Even applications that have statefull enduser terminals (like SAP R/3 for
>>> example) never allow an open transaction over user interaction.
>>
>> I'm not sure using SAP as your paragon of design excellence is a wise choice
>> here. From what I understand SAP implemented its own locking system because
>> the database it was based on didn't offer any locking at all.
>>
>> But your basic point is sound. For a web site I would definitely avoid using
>> anything like database locks and even avoid doing anything with application
>> locks if possible.
>
> Well, I don't necessarily have to use locks. I want any form of
> concurrency control that ensures serializability. Optimistic
> approaches would be fine as well.
>
>> If you really really want to expose the database session state I think he's on
>> the right track using SQLRelay. This would let him handle reconnecting a user
>> with her session even if she's connecting to a different Apache process.
>
> But why should I have SQLRelay between me and the database?
> I don't plan to use any of its "real" features. It would just be a
> proxy with a known address that maintains a database connection.
> Obviously, the database server itself has a known address and
> maintains database connections...

Because ...

actually knowing how the connection and session works on the PostgreSQL
server side will help understanding it.

When your client application (in your case an Apache work process
executing a PHP script) connects to the DB via libpq PQconnect(), it
internally does socket(2), connect(2). On the server side, the
postmaster, which had done socket(2), bind(2), listen(3) and currently
wating on a select(2) will see that the server socket is ready. It will
now call accept(2) to get the file descriptor of the server side end of
the connection. After that it will fork(2) off a new process that will
become your database server backend process handling your session.

Now the one (and only that one) Apache process, that is running your
script, and that newly created backend (and only that one) have a socket
each that are the connection for this session. No other process (well,
we're not getting into IP spoofing or other hacker stuff please) can
send or receive messages transmitted over this bidirectional channel.
And there is no portable way to hand either side of the socket
connection over to another process.

Now your PHP script ends. It can close the connection, which would
result in an EOF condition on the server side, or leave it around for
later reuse (persistent connection in PHP).

It doesn't really matter what it does, because now your user slams on
the next submit button and "another" Apache work process will handle
this new script request. Note that during your developer-is-single-user
testing, you sometimes get the same Apache work process over and over
again. But this isn't the case on a real production server under load.

And now what? How can this other Apache work process establish a new or
take over the existing (persistent) connection that was/is used by the
first Apache work process, which might not even exist any more because
Apache dynamically adjusts the number of work processes to the current
workload. Or that Apache process might be busy serving a 20MB PDF
download for some time ... or whatever it does.

It doesn't help if you tell how the API call would preferrably look
like. We need to know what you think this new libpq functions would do
internally. What are the system calls that actually make this wonder happen?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #