3 ms·

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 c

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.
saurik 5y ago
I would instead look at it as turning autocommit "off" is more like turning "automatic transaction begin" "on", which is just a strange semantic. I could appreciate a mechanism that insisted "transactions must always be explicit", but that isn't what autocommit controls, on or off. (You also wouldn't want such unless you were assured that you could put the transaction boundaries inside of multi-statement statements, as you really really want to minimize round trips there as otherwise you can end up--even on the same machine--drastically harming your lock contention by having transactions that last thousands of times longer than the single statement they wrapped... at which point the API is going to get awkward trying to enforce the transaction boundaries... autocommit is essentially just what you should want as the solution to all of this.)
quietbritishjim 5y ago
I think this illustrates my point ... but I'm not 100% sure what you mean by "autocommit mode" in your comment! You said "autocommit feature in DB drivers" but I think you mean in the DB itself by the sounds of it. AFAIK autocommit mode just applies if you don't explicitly open a transaction, so what would you propose instead? That a transaction is silently opened but not committed (potentially leaving a dangling transaction open for a long time after SELECT statement) (like Python's odd default behaviour)? Or an immediate error? I can certainly see that if you are indeed wrapping every individual statement in their own BEGIN/COMMIT pairs that you'd wonder why the database can't help you for such simple use cases. You can still selectively open an explicit transaction on those statements that do need to be grouped together, so I don't see that you've given up any control.

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.

saurik 5y ago

I would instead look at it as turning autocommit "off" is more like turning "automatic transaction begin" "on", which is just a strange semantic. I could appreciate a mechanism that insisted "transactions must always be explicit", but that isn't what autocommit controls, on or off. (You also wouldn't want such unless you were assured that you could put the transaction boundaries inside of multi-statement statements, as you really really want to minimize round trips there as otherwise you can end up--even on the same machine--drastically harming your lock contention by having transactions that last thousands of times longer than the single statement they wrapped... at which point the API is going to get awkward trying to enforce the transaction boundaries... autocommit is essentially just what you should want as the solution to all of this.)

quietbritishjim 5y ago

I think this illustrates my point ... but I'm not 100% sure what you mean by "autocommit mode" in your comment! You said "autocommit feature in DB drivers" but I think you mean in the DB itself by the sounds of it. AFAIK autocommit mode just applies if you don't explicitly open a transaction, so what would you propose instead? That a transaction is silently opened but not committed (potentially leaving a dangling transaction open for a long time after SELECT statement) (like Python's odd default behaviour)? Or an immediate error? I can certainly see that if you are indeed wrapping every individual statement in their own BEGIN/COMMIT pairs that you'd wonder why the database can't help you for such simple use cases. You can still selectively open an explicit transaction on those statements that do need to be grouped together, so I don't see that you've given up any control.