[Zope-DB] Re: Trying to catching ZPsycopg exceptions aborts the transaction

[Charlie Clark]

On 2004-11-30 at 13:12:39 [+0100], Josef Meile <jmeile at hotmail.com> wrote:
> Actually, I would like going this way because it is faster than doing a
> pre select and you won't have to repeat the transaction.

Every time I see FASTER in connection with databases I get sceptical. FASTER 
in what sense? Execution speed is in my experience not generally an issue 
(of course, there are lots of cases where it is important), FASTER 
maintenance, is.

I think you need to rethink your application. CONSTRAINTS is to enforce data 
integrity and should not be part of an application. There are times for 
wanting to go outside Zope's transaction machinery but this is easy enough 
using an external method and well-documented in these archives. But that 
isn't the issue, I think.

>  > It was my impression that transaction safety is especially
>  > essential for reservation systems.
> Yes, I agree. But with what I'm trying to do, theoretically there
> wouldn't be inconsistencies on the DB. But on the practice, this isn't
> possible because of the transaction abortion, which as I have read is
> an standard behaviour.
> 
> Just think about it:
> 
> user A want's to reserve a room from 12 to 15 and user B wants to
> reserve the same room from 14 to 17. If both come at the same time,
> that's what may happen with my method (supposing that the transaction
> isn't aborted):
> 
> User A                                User B
> ------                                ------
> begin transaction                     begin transaction
> try to insert time slot 12            try to insert time slot 14
> try to insert time slot 13            try to insert time slot 15
> try to insert time slot 14 ->Fails    try to insert time slot 16
> insert reservation from 12 to 14**    insert reservation from 14 to 17
> commit transaction                    commit transaction

This seems unnecessary complex and, dare I say it, redundant and thus likely 
to cause problems.

> ** At this point it is safe to insert the reservation because the time
> slots from 12 to 14 where successfully inserted. But as I said before,
> this only works theoretically because on the pratice, the transaction
> will be rollebacked by postgresql and a new transaction will be started.
> I did a test with ZMysql and it behaves as I expected: it continued the
> transaction, but as I understand, this isn't a standard behaviour, so,
> perhaps the Oracle DA will do the same as the postgresql one.
> 
> With the preselect method (like most users do it):
> User A                              User B
> ------                              ------
> begin transaction                   begin transaction
> select records between 12 and 15    select records between 14 and 17
> 0 records returned                  0 records returned
> insert time slot 12                 insert time slot 14
> insert time slot 13                 insert time slot 15
> insert time slot 14 ->Fails         insert time slot 16
> Transaction aborted                 insert reservation from 14 to 17
>                                      commit transaction
 
> The second method will fail for user A and it will last more time than
> the first one, which is only theoretically possible.

Why will it take longer? How much longer will it take? Does it really matter 
here? What about if these requests are running concurrently?

You need to consider your model carefully and check for the existance of 
others - there must be because this is a standard situation.

As Dieter suggests explicit implentation of your logic: checking for 
availability before booking makes more sense. You can then return 
appropriate information to the user to act upon.
"RESOURCE XYZ is not available for the requested time.
It is available from AA:BB hours to CC:DD hours"

Actually you might want to consider the more general issue and whether a 
rules engine might not be helpful. After all: how do hotels deal with this 
problem?
 
> > I suggest you implement your logic directly (i.e. by queries) and not via
> > database generated exceptions (consistency violations).
> So, I guess I will have to follow this way. I have seen some threads on
> relational databases newsgroups and almost nobody has came with a 100%
> safe solution. The only good solution would be with savepoints, but
> unfortunatelly this feature isn't yet supported by the stable version
> of postgresql (mysql supports it already from 4.0.14 and 4.1.1). Only
> the beta version 8 of postgresql has it, but I read, it is a  recent
> patch, so, it may not be stable enough.

I don't think you solve this problem in the database, it's a not a data 
management issue.

Good luck!

Charlie
-- 
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::