Re: [pylons-discuss] How to handle DB transaction failures/request race conditions

[Mikko Ohtamaa]

Hi Jens,

You also might find some background information here:

https://websauna.org/docs/narrative/modelling/occ.html

Br,
Mikko

On 27 November 2017 at 11:00,  wrote:

> Hello,
>
> Looking at the SQLAlchemy cookie cutter
> , a request gets
> its own transaction-backed db session, and on its way out a response
> commits that transaction and closes the session. There are activation
> 
> and veto
> 
> hooks too.
>
> I'm trying to understand the details behind the request/response ↔ db
> session/transaction interaction.
>
> During ordinary operation, everything should work just fine and all
> modifications to ORM objects during a view functions commit with the
> response. But what happens when a transaction fails, for example because of
> a CHECK constraint?
>
> How exactly are race conditions handled: suppose two requests to the same
> endpoint at the same time, both create the same resource, one of which
> fails (well, should!) to commit. When and how is that handled?
>
> Thank you for pointers and tips and hints in advance 邏
> Jens
>
> --
> You received this message because you are subscribed to the Google Groups
> "pylons-discuss" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to pylons-discuss+unsubscr...@googlegroups.com.
> To post to this group, send email to pylons-discuss@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/
> msgid/pylons-discuss/2a39e5b2-d8fa-43be-891c-4b1cd48d465e%
> 40googlegroups.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>



-- 
Mikko Ohtamaa
http://opensourcehacker.com
http://twitter.com/moo9000

-- 
You received this message because you are subscribed to the Google Groups 
"pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to pylons-discuss+unsubscr...@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/CAK8RCUscUcSc-SpJ-neW2BrU7eqKr3-2uTgGPbMDv0JR_dkM0Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [pylons-discuss] How to handle DB transaction failures/request race conditions

[Brian Sutherland]

On Mon, Nov 27, 2017 at 01:00:00AM -0800, jens.troe...@gmail.com wrote:
> Hello,
> 
> Looking at the SQLAlchemy cookie cutter 
> , a request gets 
> its own transaction-backed db session, and on its way out a response 
> commits that transaction and closes the session. There are activation 
> 
>  
> and veto 
> 
>  
> hooks too.
> 
> I'm trying to understand the details behind the request/response ↔ db 
> session/transaction interaction.
> 
> During ordinary operation, everything should work just fine and all 
> modifications to ORM objects during a view functions commit with the 
> response. But what happens when a transaction fails, for example because of 
> a CHECK constraint?

The details depend on the database you're using, but it should raise an
exception and roll back the transaction.

Ideally the database should prevent the transaction from being committed
once an error occurs on the SQL level. In case someone puts a try:
except: on the python level to catch the exception.

> How exactly are race conditions handled: suppose two requests to the same 
> endpoint at the same time, both create the same resource, one of which 
> fails (well, should!) to commit. When and how is that handled?

The same as above.

For other cases the details get quite hairy. For PostgreSQL, I've found
this documentation page to be useful whenever I've needed to dive into
the details:

https://www.postgresql.org/docs/current/static/transaction-iso.html

> Thank you for pointers and tips and hints in advance 邏
> Jens
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "pylons-discuss" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to pylons-discuss+unsubscr...@googlegroups.com.
> To post to this group, send email to pylons-discuss@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/pylons-discuss/2a39e5b2-d8fa-43be-891c-4b1cd48d465e%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.


-- 
Brian Sutherland

-- 
You received this message because you are subscribed to the Google Groups 
"pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to pylons-discuss+unsubscr...@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/20171127113412.qtmrqknghkss6hxg%40mobilista.local.
For more options, visit https://groups.google.com/d/optout.