4 ms·

I'm not familiar with PG but I am planning on using it in my current project, which is why I'm interested in understanding whether these are really DBMS issues.

by lusr 14y ago

I'm not familiar with PG but I am planning on using it in my current project, which is why I'm interested in understanding whether these are really DBMS issues. In the .NET world, for all the database adapters I've used (MSSQL, SQLCE, Oracle, MDB), individual statements are implicitly wrapped in transactions when no explicit transaction has been specified - not sessions/connections. I agree the "implicit transaction per session/connection" design is confusing and likely to result in bugs. While the standard .NET approach limits bugs, the downside is poorer performance when an unaware developer uses numerous implicit transactions for multiple statements where a single explicit transaction would suffice. Experts in the area appear to recommend always using explicitly managing transactions (e.g. http://ayende.com/blog/3775/nh-prof-alerts-use-of-implicit-transactions-is-discouraged http://ayende.com/blog/3775/nh-prof-alerts-use-of-implicit-t... ), and this is the approach I follow in my own work now.

jeltz 14y ago
The PostgreSQL official C client library, libpq, (which most language specific wrappers are based on) works the same way as you say the .NET adapters do. Every statement is by default implicitly wrapped in a transaction until you run "BEGIN;".
papsosouid 14y ago
>In the .NET world, for all the database adapters I've used (MSSQL, SQLCE, Oracle, MDB), individual statements are implicitly wrapped in transactions when no explicit transaction has been specified - not sessions/connections. That is how the postgresql API works as well. Most high level language modules built on top of it also work that way. Unfortunately python's DB API is not one of them, but you can just set a config option to "act like everyone expects".
j_s 14y ago
Can you point to any open source projects or other examples implementing this pattern? I think I understand where you're coming from on this but I have not seen many examples of this approach in the wild (except religiously using TransactionScope, eg. http://amnesia.codeplex.com/ http://amnesia.codeplex.com/ ). I'm wondering where it's best to manage the transactions... would they ever be in raw SQL or stored procedures instead of .NET code?

by lusr 14y ago

jeltz 14y ago

The PostgreSQL official C client library, libpq, (which most language specific wrappers are based on) works the same way as you say the .NET adapters do. Every statement is by default implicitly wrapped in a transaction until you run "BEGIN;".

papsosouid 14y ago

>In the .NET world, for all the database adapters I've used (MSSQL, SQLCE, Oracle, MDB), individual statements are implicitly wrapped in transactions when no explicit transaction has been specified - not sessions/connections. That is how the postgresql API works as well. Most high level language modules built on top of it also work that way. Unfortunately python's DB API is not one of them, but you can just set a config option to "act like everyone expects".

j_s 14y ago

Can you point to any open source projects or other examples implementing this pattern? I think I understand where you're coming from on this but I have not seen many examples of this approach in the wild (except religiously using TransactionScope, eg. http://amnesia.codeplex.com/ http://amnesia.codeplex.com/ ). I'm wondering where it's best to manage the transactions... would they ever be in raw SQL or stored procedures instead of .NET code?