[Catalyst] How safe are database transactions?

[Perrin Harkins]

Xavier Robin wrote:
> I mean, my application died once before the rollback and everything went 
> wrong. I think this is because Catalyst connects only once, so all the work 
> is done under the same connexion.

That doesn't really make sense.  It can't use the same connection from 
different processes, and within one process you usually want it to use 
the same connection.  I don't think that's the problem.

When a database error happens in most DBI drivers, an automatic rollback 
is issued.  What you have to watch out for is your perl code dying and 
leaving a transaction open, without the database knowing about it.  This 
can lead to accidental commits of partial data when the next request 
comes along, or to locks being held and blocking other requests.

Using an eval block to trap exceptions should be enough to prevent this 
problem.  For additional safety, I also add cleanup handler to mod_perl 
to issue a rollback on the open connections after each request is done.

> I will be more careful from now, but could 
> it still happen that some data of another user is being lost if he sends a 
> command at the same time?

Due to transactions?  No.  When you use transactions and two connections 
try to update the same data, one of them ends up waiting for the other. 
  That's a major purpose of transactions.  You do have to write your SQL 
correctly though.  In some cases, you have to declare your intention to 
modify some data that you read within a transaction, in order to prevent 
anyone else from touching it until you're done, using things like SELECT 
... FOR UPDATE.

It's still possible to lose data by doing things that the database 
doesn't know about.  This gets into concepts like optimistic locking and 
versioning, which are way beyond the scope of your original question, so 
I'll leave it there.

- Perrin