61–70 of 70 posts
For all interested in this topic, I highly recommend the book Designing Data Intensive Applications https://www.goodreads.com/book/show/23463279-designing-data-... . It goes into not only different isolation levels, but also some ambiguity in the traditional ACID definition. I believe a 2nd edition is imminent.
I like to think of transactions, in an MVCC system like Postgres, as being like snapshots in copy-on-write filesystems like btrfs or zfs. When you BEGIN a transaction, the DB takes a snapshot of your data, so now there are two versions of the data, the snapshot (visible to everyone else) and the "private" version visible only to your transaction. Then as you run UPDATEs, the new data is written to the private copy, b…
Not quite. Databases use both branching and locking. Two transactions that conflict can cause one thread to block, rather than rolling back. SELECT followed by an update is the most usual case for a block. (I have to code one today, and I want to see if I can rewrite it as one MySQL statement.)
One way to think about transactions, as I wrote in an earlier comment, would be to think of them as being like snapshots in a copy-on-write filesystem like btrfs or zfs. But another way to think of them is being like Git branches. When you BEGIN a transaction, you're creating a branch in Git. Everyone else continues to work on the master branch, perhaps making their own branches (transactions) off of it while you're…
I think this is a great post to have but I'm going to make a critical usability suggestion: * the videos should have "pause" and a "step at a time" control * Even at the "half speed", without a deep knowledge of the context, the videos move way too fast for me to read the syntax that's invoking and line it up with the data on the left side. I (and im definitely not the only one) need to be able to sit on one step and…
I appreciate this feedback, and then you read through it with enough rigor to notice.
Seems like a frequent surprise is that Postgres and MySQL don't default to serializable (so not fully I in ACID). They do read-committed. I didn't see this article mention that, but maybe I missed it. The article says read-committed provides "slightly" better performance, but it's been way faster in my experience. Forget where, but I think they said they chose this default for that reason. Using read-committed ofc me…
> Postgres and MySQL don't default to serializable Oracle and SQL Server also default to read committed, not serializable. Serializable looks good in text books but is rarely used in practice.
The best implementation of serializable transactions I've seen is in FoundationDB but it comes with serious costs. Transactions are limited in size and duration to a point where many normal database operations are disallowed by the system and require app-layer workarounds (at which point, of course, you lose serializability). And in many cases you do need cluster locks for other purposes anyway.
We built an entire project for a client-side project with millions of SQL rows and thousands of users without adding a single transaction. :/
Earlier quoted context omitted.
> Postgres and MySQL don't default to serializable Oracle and SQL Server also default to read committed, not serializable. Serializable looks good in text books but is rarely used in practice.
One reason Oracle uses it is because this mode scales horizontally whilst allowing very large transactions. You can just keep adding write masters. The best implementation of serializable transactions I've seen is in FoundationDB but it comes with serious costs. Transactions are limited in size and duration to a point where many normal database operations are disallowed by the system and require app-layer workarounds…
Earlier quoted context omitted.
One reason Oracle uses it is because this mode scales horizontally whilst allowing very large transactions. You can just keep adding write masters. The best implementation of serializable transactions I've seen is in FoundationDB but it comes with serious costs. Transactions are limited in size and duration to a point where many normal database operations are disallowed by the system and require app-layer workarounds…
Spanner has similar limitations on xact size, maybe for this reason?
Earlier quoted context omitted.
Spanner has similar limitations on xact size, maybe for this reason?
Probably. I've seen it argued that TX size limits are a good practice anyway, and not having them is a design fault of SQL, but it's an argument on thin ice. Transaction size and scope is usually defined by the nature of the business logic, it's not something you can just define to be whatever you want without consequence. An RDBMS can do atomic and correct changes to an entire very large table without any developer…
Earlier quoted context omitted.
Probably. I've seen it argued that TX size limits are a good practice anyway, and not having them is a design fault of SQL, but it's an argument on thin ice. Transaction size and scope is usually defined by the nature of the business logic, it's not something you can just define to be whatever you want without consequence. An RDBMS can do atomic and correct changes to an entire very large table without any developer…
I'll keep my xacts small until that one time we have to do some big manual fix or migration. But you don't even have to do anything that wild to hit the Spanner 100MB limit.