What is a database transaction? • Hacker News Board

Re: What is a database transaction?

#21

Post by unkulunkulu » Sun, Feb 22, 2026, 5:13 PM UTC

> 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 could be subtransactions, which make this statement even more contradictory?

I enjoyed the visualizations and explanations otherwise, thanks!

Re: What is a database transaction?

#22

Post by zadikian » Sun, Feb 22, 2026, 5:29 PM UTC

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 means having to keep locking details in mind. Like, UNIQUE doesn't just guard against bad data entry, it can also be necessary for avoiding race conditions. But now that I know, I'd rather do that than take the serializable performance hit, and also have to retry xacts and deal with the other caveats at the bottom of https://www.postgresql.org/docs/current/transaction-iso.html

Re: What is a database transaction?

#23

Post by Quarrelsome » Sun, Feb 22, 2026, 5:35 PM UTC

I have learned about the beauty of predicate locks. That's such a sexy way of dealing with the issue instead of just blithely funneling all writes.

Re: What is a database transaction?

#24

Post by airstrike » Sun, Feb 22, 2026, 5:49 PM UTC

bddicken wrote: post #7

rishabhaiover wrote: post #2

It's an absolute pleasure reading planetscale blogs. I'm curious about what tool is used to make these visualizations?

Author here. Thank you! These visuals are built with js + gsap ( https://gsap.com )

Thought it was going to be a blog post about Jeopardy for a sec

Re: What is a database transaction?

#25

Post by nkzd » Sun, Feb 22, 2026, 6:12 PM UTC

Have you ever seen anyone changing transaction isolation levels in code? I think pessimistic or optimistic locking is preferred way to handle transaction concurrency.

Re: What is a database transaction?

#26

Post by lasgawe » Sun, Feb 22, 2026, 6:13 PM UTC

We built an entire project for a client-side project with millions of SQL rows and thousands of users without adding a single transaction. :/

Re: What is a database transaction?

#27

Post by shalabhc » Sun, Feb 22, 2026, 6:33 PM UTC

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.

Re: What is a database transaction?

#28

Post by layer8 » Sun, Feb 22, 2026, 6:51 PM UTC

zadikian wrote: post #22

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…

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.

Re: What is a database transaction?

#29

Post by layer8 » Sun, Feb 22, 2026, 6:55 PM UTC

lasgawe wrote: post #26

We 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.

Re: What is a database transaction?

#30

Post by layer8 » Sun, Feb 22, 2026, 6:58 PM UTC

jascha_eng wrote: post #16

This actually used to be one of my favorite interview questions for backend engineers. Everyone has used transactions but depending on your seniority you'd understand it to different degrees. And no I'd never expect people to know the isolation levels by heart, but if you know there are different ones and they behave differntly that's pretty good and tells me you are curious about how things work under the hood.

The nominally same isolation levels can also behave differently on different database systems, so in general you have to investigate the details on a case-by-case basis anyway.