[HN Gopher] How SQLite Helps You Do ACID
___________________________________________________________________
How SQLite Helps You Do ACID
Author : eatonphil
Score : 241 points
Date : 2022-08-10 14:47 UTC (8 hours ago)
(HTM) web link (fly.io)
(TXT) w3m dump (fly.io)
| simonw wrote:
| This 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 wrote:
| Thanks! 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.
| carvking wrote:
| Love the title.
|
| Just waiting for "How PHP makes you do heroin"
| hunter2_ wrote:
| At least it's not the Personal Content Preprocessor, I suppose.
| [deleted]
| doublerabbit wrote:
| Also follows: "How not to code Perl on crack"
| mikhael28 wrote:
| Pretty sure the Silk Road ran on SQLite.
| ziddoap wrote:
| They helped people do acid, too. Not sure what the relevance is
| though.
| mikhael28 wrote:
| If Andreas Reuter and Theo Harder 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 wrote:
| Journaling with big DICA energy.
| speed_spread wrote:
| Now, don't be ADIC.
| mikhael28 wrote:
| Beautiful. Life is better with humor.
| chrsig wrote:
| when you put it like that, i'm glad they went with acid.
| agildehaus wrote:
| A 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?
| eli wrote:
| You 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.
| RedShift1 wrote:
| I 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.
| bob1029 wrote:
| I 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 and
| https://www.sqlite.org/wal.html
| slt2021 wrote:
| problem 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
| ripley12 wrote:
| > 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/3cf46ee508e97b46736a26...
| [deleted]
| kretaceous wrote:
| Loved 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!
| michaeldwan wrote:
| We 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!
| spike021 wrote:
| Not 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.
| corobo wrote:
| More likely to succeed with an application than an assumption
| of failure :)
| davnicwil wrote:
| Start the application transaction. If it fails you'll just roll
| back to current state in your career log (resume). Nothing
| ventured nothing gained.
| klysm wrote:
| Just make sure you are in at least read committed isolation
| krono wrote:
| If there's something you want, it might work better to just
| straight up ask them for it.
| pachico wrote:
| Don'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?
| benbjohnson wrote:
| Author 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/
| likeabbas wrote:
| NoSQL is no longer cool
| lopatin wrote:
| What about NewSQL?
| likeabbas wrote:
| That's just SQL with extra (distributed) steps
| TylerE wrote:
| Notice how all these articles have the same author?
| EMM_386 wrote:
| It 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.
| tyingq wrote:
| I suspect part of it is things like litestream, dqlite, rqlite,
| etc. Opens it up to people who have to make it work in some
| distributed fashion.
| iddan wrote:
| I love the writing style. Technical blogs take notes
| eis wrote:
| I recommend the excellent articles by the SQLite authors
| themselves like
|
| How To Corrupt An SQLite Database File:
| https://www.sqlite.org/howtocorrupt.html
|
| Atomic Commit In SQLite: 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
|
| They go into the details of challenges SQLite faces due to how
| hardware and OSs work or not work and how they solve them.
| im3w1l wrote:
| Good 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 wrote:
| The SQLite Sandwich Shop is condiment free. :)
| cp9 wrote:
| if you're not using vinegar I'm not sure what your source of
| ACID is
| lenocinor wrote:
| Pickles of course (too bad the codebase isn't in Python or
| it could be a great pun in this circumstance).
| sophacles wrote:
| A slice of tomato, and/or the neighborhood drug dealer can
| get you the ACID you need.
| Aperocky wrote:
| minimalism and simplicity always bring great joy.
|
| I wonder what is the largest server side system supported by
| SQLite as its primary data storage.
| liuliu wrote:
| The 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 wrote:
| I think `BEGIN CONCURRENT` is on its own branch:
| 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
| liuliu wrote:
| Oh, great! These are two big changes I am very interested in.
| Will look out when it is merged :)
| simonz05 wrote:
| For 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
| AdamProut wrote:
| A 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 wrote:
| That 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/f
| ast18... - [2] https://www.youtube.com/watch?v=fDY6Wi0GcPs
| AdamProut wrote:
| An 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).
| ryanworl wrote:
| You're probably aware of this, but for the sake of others
| reading:
|
| Crashing after an fsync failure isn't sufficient if
| you're using buffered IO either. Dirty pages in the page
| cache could cause your consensus implementation to e.g.
| allow voting for two different leaders for the same term
| if at some future point that machine crashes and the
| dirty page contained the log record for that vote. Your
| process would restart after the machine restarts and no
| longer have access to the dirty page and potentially vote
| again if asked.
| ghusbands wrote:
| > 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
| benbjohnson wrote:
| Maybe 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
| ghusbands wrote:
| For 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.
| EGreg wrote:
| I clicked hoping to know how I could better drop acid while I'm
| using SQLite. But I only dropped some tables.
___________________________________________________________________
(page generated 2022-08-10 23:00 UTC)