A miserable little pile of queries! But enough talk, have at you!
What is a database transaction?
51–60 of 70 posts
Re: What is a database transaction?
#52> A phantom read is one where a transaction runs the same SELECT multiple times, but sees different results the second time around
> Under the SQL standard, the repeatable read level allows phantom reads, though in Postgres they still aren't possible.
This is bad wording which could lead to an impression that a repeatable read may show different values. Values in rows will be the same but new rows may be added to the second result set. New rows is important as no previously read rows can be either changed or deleted — otherwise there will be no repetition for those rows second time around.
Re: What is a database transaction?
#53I’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…
Most RDBMSs offer serializable isolation if you need it. Often you don't need it. The downside of using serializable isolation unnecessarily is reduced concurrency and throughput due to increased coordination between transactions.
Still, I think it’s the right default to start with serializable. Then when you have performance issues you can think long and hard about whether relaxed isolation levels will work in a big free way. Better to start with a correct application.
Re: What is a database transaction?
#54Earlier quoted context omitted.
Most RDBMSs offer serializable isolation if you need it. Often you don't need it. The downside of using serializable isolation unnecessarily is reduced concurrency and throughput due to increased coordination between transactions.
Still, I think it’s the right default to start with serializable. Then when you have performance issues you can think long and hard about whether relaxed isolation levels will work in a big free way. Better to start with a correct application.
Starting with serializable is not free, there is a coding cost to pay to handle all the concurrency errors.
Re: What is a database transaction?
#55A lot of database tools these days prioritize instant sharing of updates over transactions and ACID properties. Example: Airtable. As soon as you update a field the update shows up on your coworkers screen who also has the same table open. The downside of this is that Airtable doesn't do transactions. And the downside of not doing transactions is potentially dangerous data inconsistencies. More about that here: https…
Not so subtle product promo while taking a swipe at your competition.
Re: What is a database transaction?
#56I 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, but everyone else continues to work with the snapshot. (And might be creating their own private copies for other transactions).
If you do a ROLLBACK, then your private copy of the data is discarded, and its changes never make it into the official copy. But if you do a COMMIT, then your private snapshot is made public and is the new, official, copy for everyone else to read from. (Except those who started a transaction before you ran COMMIT: they made their private copies from the older snapshot and don't have a copy of your changes).
This is probably obvious to nearly everyone here, but I figured I'd write it anyway. You never know who might read an analogy like this and have that lightbulb moment where it suddenly makes sense.
P.S. Another analogy would be Git branches, but I'll write that in a different comment.
Re: What is a database transaction?
#57One 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 working. Every UPDATE command you run inside the transaction is a commit pushed to your branch. If you do a ROLLBACK, then you're deleting the branch unmerged, and its changes will be discarded without ever ending up in the master branch. But if you instead do a COMMIT, then that's a `git merge` command, and your changes will be merged into the master branch. If they merge cleanly, then all is well. If they do NOT merge cleanly, because someone else merged their own branch (committed their own transaction) that touched the same files that you touched (updated rows in the same table), then the DB will go through the file line by line (go through the table row by row) to try to get a clean merge. If it can successfully merge both changes without conflict, great. If it can't, then what happens depends on the transaction settings you chose. You can, when you start the transaction, tell the DB "If this doesn't merge cleanly, roll it back". Or you can say "If this doesn't merge cleanly, I don't care, just make sure it gets merged and I don't care if the conflict resolution ends up picking the "wrong" value, because for my use case there is no wrong value." This is like using "READ UNCOMMITTED" vs "SERIALIZABLE" transaction settings (isolation levels): you would use "READ UNCOMMITTED" if you don't care about merge conflicts in this particular table, and just want a quick merge. You would use "SERIALIZABLE" for tables with data that must, MUST, be correct, e.g. account balances. And there are two more levels in between for subtle differences in your use case's requirements.
As with my previous comment, this is probably obvious to 98.5% of people here. But maybe it'll help someone get that "ah-ha!" moment and understand transactions better.
Re: What is a database transaction?
#58Earlier quoted context omitted.
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.
VoltDB took this to an extreme - the way you interact with it is by sending it some code which does a mix of queries and logic, and it automatically retries the code as many times as necessary if there's a conflict. Because it all happens inside the DBMS, it's transparent and fast. I thought that was really clever. I'm using the past tense here, but VoltDB is still going. I don't think it's as well-known as it deserv…
Interesting. How is that faster than just having the code running on the same machine as the DB? Guess it could be smarter about conflicts than random backoff.
Re: What is a database transaction?
#59I 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.)
Re: What is a database transaction?
#60Earlier quoted context omitted.
Still, I think it’s the right default to start with serializable. Then when you have performance issues you can think long and hard about whether relaxed isolation levels will work in a big free way. Better to start with a correct application.
Starting with serializable is not free, there is a coding cost to pay to handle all the concurrency errors.
Sybase SQLAnywhere implements (or at least did) strict serialization by taking an exclusive row lock on all rows... which you can imagine scales horribly for a table with a reasonable row count.
I found out the hard way at work. I had assumed it took an exclusive lock on the table level only, the documentation didn't really spell out the details of how it enforced the serialized access.
I changed it to a retry loop which worked fine and was fairly easy to implement all things considered. Not gonna reach for strict serialization again unless I have to.