5 ms·

Beware that for strict compliance with Python's DB-API 2.0 the database driver needs to obey weird rules about transactions: * All statements start a transacti

Beware that for strict compliance with Python's DB-API 2.0 the database driver needs to obey weird rules about transactions: * All statements start a transaction automatically if one isn't already open. * You need to call .commit() or .rollback() on the connection object to do the commit or rollback. * The driver can optionally support an "autocommit mode" where, instead of the above rules, every individual statement is wrapped in its own transaction and committed immediately. That's the theory. Many database drivers, including the built-in SQLite driver, differ in practice (for example, it doesn't automatically start a transaction for a SELECT statement). The biggest common practical difference is an implementation detail of so-called "autocommit mode". Often that is NOT implemented by actually issuing a "BEGIN TRANSACTION" before and "COMMIT" after every statement. Instead, it often just issues the statement without any automatic transaction statements whatsoever, relying on the fact that the underlying database engine will immediately commit any statements outside an explicit transaction (i.e. has its own autocommit mode). That means if you do want complete manual control of transactions in a Python DB-API driver (including sqlite), then - confusingly - you need to set the autocommit option to true! It's not clear to me whether/how this applies to pg8000 driver in the OP. There is a section on autocommit but it doesn't explicitly say what it does.

jeltz 5y ago
Personally I think this was q huge mistake since almost all of the rest of the world uses autocommit by default.
- muxator 5y ago
  The existence of the autocommit feature in DB drivers is something I never really understood: why an application developer would want to relinquish the direct control of one of the most powerful features of a relational DB? Unless one's interaction with the database are really basic (i.e. a single SELECT statement), one would want to be as explicit as possible regarding where transaction boundaries are. Having a default that hides this aspect from my code always seemed a false convenience to me. edit: this is just a tangential reflection inspired by your comment, not an answer / nor I wanted to detract from your fact statement.
  - roenxi 5y ago
    There are a lot of people who use databases without really understanding anything about them. They mostly won't understand why it is important to control time and execute multiple statements at the same logical moment in database world. That is a difficult concept to intuit naturally, people need to be told and they often don't have time or a teller. Databases that only do the technically-correct thing are weeded out because they lose the big supportive cloud of novices that experts grow out of. So the good databases support the intuitive way and the the correct way of manipulating data. There are more computing concepts to learn than there are hours to learn it in. People use systems they don't understand and systems need to support that.
  - crad 5y ago
    Scaling for one. If 95% of your workload is non-transactional reads, why introduce transactions? If your writes are single table and don't require transactional safety, why introduce transactions? If you need complex, multiple-insert or update writes, transactions are great, but that can also be accomplished in a stored proc. In a high-velocity environment, avoiding transactions are a great way to get more throughput out of your database server with lower latencies. Combine Postgres with pgBouncer in statement mode and you can scale it pretty dramatically.
  - fulafel 5y ago
    Up until 10ish years ago, the most popular SQL DB didn't support transactions in the default setup. So lots of apps got written without relying on having transactions, either for compatibility reasons or because the devs grew up on said nontransactional DB. So autocommit was an easy way to emulate transactionless SQL on the rest of the DBs.
    - samuel 5y ago
      I wouldn't blame MySQL for that. JDBC's default mode since 1.0 is autocommit set to true, and surely predates MySQL's success.
  - jeltz 5y ago
    In practice autocommit works very well. In many workloads most transactions consist of just one query. Additional if you have autocommit it is harder to accidentally create a long running transaction which is a good thing since long running transactions can really tank your performance.
tlocke 5y ago
I maintain pg8000, and it has two APIs: the standard DB-API 2 accessed with pg8000.dbapi and its own native API at pg8000.native. The native API is a lot simpler, and so I'd use that if you can. The native API is really my suggestion for the next version of DB-API, so DB-API 3. I'd be interested to know people's thoughts on this, and maybe we could put together a draft specification?

jeltz 5y ago

Personally I think this was q huge mistake since almost all of the rest of the world uses autocommit by default.

muxator 5y ago
The existence of the autocommit feature in DB drivers is something I never really understood: why an application developer would want to relinquish the direct control of one of the most powerful features of a relational DB? Unless one's interaction with the database are really basic (i.e. a single SELECT statement), one would want to be as explicit as possible regarding where transaction boundaries are. Having a default that hides this aspect from my code always seemed a false convenience to me. edit: this is just a tangential reflection inspired by your comment, not an answer / nor I wanted to detract from your fact statement.
- roenxi 5y ago
  There are a lot of people who use databases without really understanding anything about them. They mostly won't understand why it is important to control time and execute multiple statements at the same logical moment in database world. That is a difficult concept to intuit naturally, people need to be told and they often don't have time or a teller. Databases that only do the technically-correct thing are weeded out because they lose the big supportive cloud of novices that experts grow out of. So the good databases support the intuitive way and the the correct way of manipulating data. There are more computing concepts to learn than there are hours to learn it in. People use systems they don't understand and systems need to support that.
- crad 5y ago
  Scaling for one. If 95% of your workload is non-transactional reads, why introduce transactions? If your writes are single table and don't require transactional safety, why introduce transactions? If you need complex, multiple-insert or update writes, transactions are great, but that can also be accomplished in a stored proc. In a high-velocity environment, avoiding transactions are a great way to get more throughput out of your database server with lower latencies. Combine Postgres with pgBouncer in statement mode and you can scale it pretty dramatically.
- fulafel 5y ago
  Up until 10ish years ago, the most popular SQL DB didn't support transactions in the default setup. So lots of apps got written without relying on having transactions, either for compatibility reasons or because the devs grew up on said nontransactional DB. So autocommit was an easy way to emulate transactionless SQL on the rest of the DBs.
  - samuel 5y ago
    I wouldn't blame MySQL for that. JDBC's default mode since 1.0 is autocommit set to true, and surely predates MySQL's success.
- jeltz 5y ago
  In practice autocommit works very well. In many workloads most transactions consist of just one query. Additional if you have autocommit it is harder to accidentally create a long running transaction which is a good thing since long running transactions can really tank your performance.

tlocke 5y ago

I maintain pg8000, and it has two APIs: the standard DB-API 2 accessed with pg8000.dbapi and its own native API at pg8000.native. The native API is a lot simpler, and so I'd use that if you can. The native API is really my suggestion for the next version of DB-API, so DB-API 3. I'd be interested to know people's thoughts on this, and maybe we could put together a draft specification?