I thought this was pretty good, not least because it attempts to explain isolation levels, something I always found pretty tricky when teaching SQL. Mind you, I was only teaching SQL, and so isolation, as part of C and C++ courses so that our clients could do useful stuff, but explaining what levels to use was always tuff.
What is a database transaction?
31–40 of 70 posts
Re: What is a database transaction?
#32Seems 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…
The issue with SERIALIZABLE, aside from performance, is that transactions can fail due to conflicts/deadlocks/timeouts, so application code must be prepared to recognize those cases and have a strategy to retry the transactions.
Right. So my code had a helper to run some inner func in a serializable xact, in rw or ro mode, which would retry with backoff. Like the TransactionRunner in Spanner. But even with no retries occurring, it was very slow.
Re: What is a database transaction?
#33We built an entire project for a client-side project with millions of SQL rows and thousands of users without adding a single transaction. :/
If you have no explicit transactions, every insert/update is its own transaction (aka auto-commit). Depending on what you do, you might not need more. It’s still important to know that these execute as a transaction.
Yep, there have been times I get through a whole project without any explicit transactions. In fact it can be a sign of not fully normalized schema design if you rely on those a lot (which can ofc be fine if you deliberately wanted that).
Re: What is a database transaction?
#34Seems 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.
Re: What is a database transaction?
#35We built an entire project for a client-side project with millions of SQL rows and thousands of users without adding a single transaction. :/
Did you use SELECT FOR UPDATE at all, or just never had to update dependent data? If the complex operations are implemented using stored functions / procedures then the a transaction is implicit.
If the data is fairly straightforward like just one-to-many CRUD with no circular references, you would be able to do it without transactions, just table relationships would be enough to ensure consistency.
Re: What is a database transaction?
#36I’ve found this article lacking. Like some other articles in this space, it introduces isolation levels through the lens of the phenomena described in the SQL standard, but I find that there’s a different, more intuitive approach. I think it’s more tractable to define this problem space starting from the concept of (strict) serializability, which is really a generalization of the concept of thread safety. Every softw…
> concept of (strict) serializability [("S")], which is really a generalization of the concept of thread safety
Unsure why "strict" (L + S) is in braces: Linearizability ("L") is what resembles safety in SMP systems the most?
Re: What is a database transaction?
#37We built an entire project for a client-side project with millions of SQL rows and thousands of users without adding a single transaction. :/
How nice for you. But since you totally neglected to say anything about your use-case or schema or query patterns, it’s impossible to know what this even means. Some use cases can trivially be done without any explicit transactions and you’re not giving anything up. For others (usually, something where you need to enforce invariants under high concurrency writes or writes+reads on the same data across multiple tables), transactions are pretty critical. So, it depends.
Re: What is a database transaction?
#38Seems 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.
Yeah, the only examples I know of it being default are Spanner and Cockroach, which are for a different use case.
Re: What is a database transaction?
#39> At this stage, it has nothing to do with xmin and xmax, but rather because other transactions cannot see uncommitted data Am I missing something or this statement is incomplete? Also I find the visualization of commit weird, it “points to” the header of the table, but then xmax gets updated “behind the scenes”? Isnt xmax/xmin “the mechanism behind how the database knows what is committed/not committed”? Also, there…
I also think the article glossed/skipped over the xmax/xmin concepts. And they are fundamental to understand how different isolation levels actually work. It's quite jarring to the point I'm wondering if a whole section got accidentally dropped from the article.
Re: What is a database transaction?
#40In the section about serializable read TFA gets the `accounts` `balance` wrong.