10 ms·
How SQLite Helps You Do ACID
- deleted 4y ago[deleted]
- simonw 4y agoThis was so useful to me. I feel like I have a pretty solid idea of how journal mode actually works now. I cannot wait for the follow-up explaining WAL mode!
- benbjohnson 4y agoThanks! The WAL mode is really interesting because it just changes the design just a little bit but you get a ton of additional benefits. I have that post in the works right now.
- mikhael28 4y ago
- ziddoap 4y agoThey helped people do acid, too. Not sure what the relevance is though.
- mikhael28 4y agoIf Andreas Reuter and Theo Härder didn't want people to make any jokes and live humorless lives, they would have named it DICA. Or CIDA. But no, they named the concept ACID. Why do you think they did that? Do you really think they aren't in on the joke?
- enw 4y agoJournaling with big DICA energy.
- chrsig 4y agowhen you put it like that, i'm glad they went with acid.
- speed_spread 4y agoNow, don't be ADIC.
- mikhael28 4y agoBeautiful. Life is better with humor.
- im3w1l 4y agoGood article but on a lighter note the thought of undoing a half-made sandwich is surreal to me. I always start by buttering the bread!
- benbjohnson 4y agoThe SQLite Sandwich Shop is condiment free. :)
- cp9 4y agoif you're not using vinegar I'm not sure what your source of ACID is
- kretaceous 4y agoLoved the article. Whenever I read articles or releases by Fly.io, it makes me want to work with them. As a newbie who's interested in databases and networking, I reckon it would be an amazing experience in the team. Unfortunately, they do not hire interns or likewise (from their job page) and I assume, from the amazing work they do, they aren't looking for beginners. =) EDIT: Thanks to people encouraging me to apply. I'll definitely try!
- krono 4y agoIf there's something you want, it might work better to just straight up ask them for it.
- corobo 4y agoMore likely to succeed with an application than an assumption of failure :)
- davnicwil 4y agoStart the application transaction. If it fails you'll just roll back to current state in your career log (resumé). Nothing ventured nothing gained.
- klysm 4y agoJust make sure you are in at least read committed isolation
- spike021 4y agoNot only just apply, but definitely try to network with people on the team. Works well if they're active on twitter. I got my first internship by building a rapport with someone on twitter and then they worked with their org to find an internship opening for me (still had to interview and stuff) that wasn't on their careers site.
- michaeldwan 4y agoWe do hire entry level people, in fact we're wrapping up our first cohort of interns right now. Exclusively hiring experienced dudes from the tech bubble is a failure mode in the long run. We're invested in hiring folks with diverse backgrounds and experience levels, and we need to talk about that more for sure. Our jobs page is sparse right now because we’re focusing on hiring EMs to help grow a healthy eng org. We’ll have more openings before long, and you should absolutely apply when something sounds right. Feel free to hunt me down online in the mean time!
- nonameking2026 4y agoLove the title. Just waiting for "How PHP makes you do heroin"
- deleted 4y ago[deleted]
- doublerabbit 4y agoAlso follows: "How not to code Perl on crack"
- hunter2_ 4y agoAt least it's not the Personal Content Preprocessor, I suppose.
- barking_biscuit 4y ago"From PHP to PCP in one easy step"
- liuliu 4y agoThe BEGIN CONCURRENT and wal2 are new things? Seems the former is only added in 3.39.0? Didn't get call out in the release note.
- benbjohnson 4y agoI think `BEGIN CONCURRENT` is on its own branch: https://www.sqlite.org/src/vdiff?branch=begin-concurrent https://www.sqlite.org/src/vdiff?branch=begin-concurrent The `wal2` mode is on its own branch and you have to compile it specifically. It'd be great if they merged it into the main branch though. https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md
- liuliu 4y agoOh, great! These are two big changes I am very interested in. Will look out when it is merged :)
- agildehaus 4y agoA company I work for runs MySQL on an IoT base station product (Beaglebone-like hardware). Tables seems to corrupt every so often, which typically are repairable with a "mysqlcheck --auto-repair" which they have as part of the boot sequence, but not always. These corruptions seem to be due to bad batteries or the field team holding down the power button too long. Would sqlite be less prone to table corruption?
- bob1029 4y agoI think yes - If you configured something like: PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; This should be very safe & still reasonably fast. See: https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous and https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- RedShift1 4y agoI run about 100 computers that regularly receive power cuts and have not have databases go corrupt. However we also write to the database every minute or so, all data is packed into compressed chunks that contain a minute of data.
- eli 4y agoYou should take a look at your mysql settings e.g. `innodb_flush_log_at_trx_commit` but yeah mysql isn't really built for frequent sudden shutdowns.
- slt2021 4y agoproblem is in application layer and mysql settings. you can try to switch from myisam engine to innodb engine, tune innodb settings, write inserts in batches, rather than continuously, and wrap insert into transaction
- eis 4y agoI recommend the excellent articles by the SQLite authors themselves like How To Corrupt An SQLite Database File: https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html Atomic Commit In SQLite: https://www.sqlite.org/atomiccommit.html https://www.sqlite.org/atomiccommit.html and more under the "Technical and Design Documentation" section of their docs at https://www.sqlite.org/docs.html https://www.sqlite.org/docs.html They go into the details of challenges SQLite faces due to how hardware and OSs work or not work and how they solve them.
- ghusbands 4y ago> The database needs to ensure each transaction has a snapshot view of the database for its entire duration. This is called isolation. That's called snapshot isolation, but is not full isolation. Wikipedia has a good example of the write skew that can result [1]. [1] https://en.wikipedia.org/wiki/Snapshot_isolation https://en.wikipedia.org/wiki/Snapshot_isolation
- benbjohnson 4y agoMaybe I'm misinterpreting your comment but SQLite operates with serializable isolation[1] so I don't think write skew is possible. For both the rollback journal and the WAL, SQLite only allows a single write transaction at a time so you can't have two transactions updating with different snapshots. Also, from that Wikipedia article: > Were the system serializable, such an anomaly would be impossible [1] https://www.sqlite.org/isolation.html https://www.sqlite.org/isolation.html
- ghusbands 4y agoFor someone reading the article fresh, without other articles or outside knowledge, the quoted text pretty much states that snapshot isolation is sufficient for isolation in general, and it's a common enough incorrect belief that it's worth pointing out as untrue. I'm glad to hear that SQLite has chosen serializable isolation as the one true way, even with the newer BEGIN CONCURRENT functionality.
- simonz05 4y agoFor people interested in this topic I can recommend "Can Applications Recover from fsync Failures?" by A. Rebello et al[1]. The paper analyzes how file systems and PostgreSQL, LMDB, LevelDB, SQLite, and Redis react to fsync failures. It shows that although applications use many failure-handling strategies, none are sufficient: fsync failures can cause catastrophic outcomes such as data loss and corruption. - [1] https://www.usenix.org/conference/atc20/presentation/rebello https://www.usenix.org/conference/atc20/presentation/rebello
- AdamProut 4y agoA nice outcome here for distributed SQL databases that store multiple copies of the data by default is that they can just failover over on an fsync failure and not try to "handle it". If fsync starts failing with EIO there is a good chance the disk is going to die soon anyways.
- simonz05 4y agoThat depends. Replicated state machines need to distinguish between a crash and corruption. Most systems don't do that. It can be disastrous to truncate the journal when encountering a checksum mismatch for instance. See "Protocol-Aware Recovery for Consensus-Based Storage" for more research on that topic. [1][2] - [1] https://www.usenix.org/system/files/conference/fast18/fast18-alagappan.pdf https://www.usenix.org/system/files/conference/fast18/fast18... - [2] https://www.youtube.com/watch?v=fDY6Wi0GcPs https://www.youtube.com/watch?v=fDY6Wi0GcPs
- AdamProut 4y agoAn fsync() failing doesn't necessarily mean there is a disk corruption. I agree all logging and recovery protocols should have different handling for a corruption vs a torn tail of the log for example, but I view that as mostly orthogonal. I'm talking about exiting the process if fsync() fails and letting the distributed databases normal failover processing do its thing. This is a normal scenario for a failover (i.e, its the same as process crashing or getting OOM killed by linux, etc).
- EGreg 4y agoI clicked hoping to know how I could better drop acid while I'm using SQLite. But I only dropped some tables.
- LocalH 4y agolittle bobby tables just grew up
- iddan 4y agoI love the writing style. Technical blogs take notes
- Aperocky 4y agominimalism and simplicity always bring great joy. I wonder what is the largest server side system supported by SQLite as its primary data storage.
- ripley12 4y ago> After that, 510 bytes are used for the SHARED lock. A byte range is used here to accommodate older Windows versions with mandatory locks. I was curious how old, and... wow, that code is for Windows versions that predate the NT kernel (Win95/98/ME). I'm surprised that it's still around, but the comment does a great job of explaining it. https://github.com/sqlite/sqlite/blob/3cf46ee508e97b46736a2607ded9c84c2c16229f/src/os.h#L104-L158 https://github.com/sqlite/sqlite/blob/3cf46ee508e97b46736a26...
- icedchai 4y agoThe NT kernel is older than you think and predates Windows 95. NT 3.1 was released way back in 93!
- ripley12 4y agoTrue, I misspoke; I was clumsily trying to describe the time before the NT kernel was used in consumer versions of Windows.
- icedchai 4y agoYes. And I personally also consider NT 4.0 the first "mainstream" NT, even though it wasn't quite consumer. I didn't see a lot of NT 3.x in the wild.
- pachico 4y agoDon't get me wrong, I run SQLite in production with millions of records, but it seems people have discovered it just very recently. All of the sudden, there's always a post about it with hundreds of comments. What changed?
- likeabbas 4y agoNoSQL is no longer cool
- EMM_386 4y agoIt seems to come in waves, there was another round of SQLite posts hitting the front page around 6 months ago as I recall. I'm not sure why, SQLite is the most widely deployed database engine on the planet, so it's not as if the technology is really "gaining" in popularity ... it's already there. I was always curious if this is due to the huge number of new developers, many front-end, who have joined IT over the past decade or so, now venturing out and beginning to learn about relational databases and their benefits.
- benbjohnson 4y agoAuthor here. I agree there's been a resurgence lately. My working theory is that the performance of the underlying hardware has increased so much in the last decade that the simplicity of SQLite has become a more important tradeoff than the extra performance boost you may see with Postgres/MySQL. Also, network overhead tends to dominate small queries so SQLite can actually end up a lot faster since it's in-process. I wrote up a longer version here: https://fly.io/blog/all-in-on-sqlite-litestream/ https://fly.io/blog/all-in-on-sqlite-litestream/
- Karupan 4y agoQuestion for people who actually use SQLite in production: when do you start seeing performance degrade to the extent that you have to migrate to a "proper" DB? Can a transactional table scale to say 10 million+ records with a couple of indexes before it becomes a problem?
- didip 4y agoI can't be the only one who doesn't understand the relationship between SQLite and apps deployed on Fly.io, right? Normal apps typically need MySQL or PostgreSQL, no?
- andyferris 4y agoTypically, yes. In this case fly.io (and others) are trying to change that by doing interesting things with storage of the database and the transaction logs. It works by hooking into various SQLite features (like virtual file systems and post transaction hooks) so your application doesn’t need to care about that layer and you just write you app with standard SQLite code. The theory is you get best of both worlds - it could be pretty cool if it works out well.
- tptacek 4y agoThe blog post isn't about Fly.io, it's about SQLite. Ben works here, but exclusively on Litestream (we sort of "acquired" Litestream some months ago). He's not writing about our product; he's writing about his project, and about SQLite. We have Postgres as part of the platform (`fly postgres`), and not SQLite. But SQLite is super interesting to us, especially with Litestream, because it plays nicely with apps that are deployed close to their users. A good starting point: https://fly.io/blog/globally-distributed-postgres/ https://fly.io/blog/globally-distributed-postgres/ --- now, substitute an incarnation of SQLite with good read replicas, and in-process performance.
- truth_seeker 4y agoF2fs (Flash Friendly file system based on LSM) natively supports Atomic writes. https://github.com/sqlite/sqlite/blob/master/doc/F2FS.txt https://github.com/sqlite/sqlite/blob/master/doc/F2FS.txt https://lore.kernel.org/lkml/1411707287-21760-2-git-send-email-jaegeuk@kernel.org/ https://lore.kernel.org/lkml/1411707287-21760-2-git-send-ema...
- todd8 4y agoI've done research on verification of distributed programs and worked for IBM as an architect of distributed file-systems. It was all so long ago that I'm not up to date on the literature so I really enjoy the discussion here. This is the best thing about HN, it has such an interesting and knowledgeable community. Thanks HN.