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

[Josef Meile]

Hi Dieter,

>>I know it may sound strange, but I don't want the DA to abort the
>>transaction when trying to insert a duplicate record.
> 
> 
> Indeed.
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.

 > 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

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

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

> As your system is inherently concurrent, the direct implementation
> will be able to reduce the likelyhood of conflicts but
> cannot garantee to exclude them alltogether.
> 
> For the remaining conflicts, I would use the standard transaction
> behaviour: abort (and maybe retry automatically).
Yes, but how would you do this? If I'm not wrong, there will be the same 
problems
again when trying to catch the exceptions on zope.

Thanks,
Josef