Does your app have this bug? SQL bug you won't see during development

I discovered a bug which I've seen in almost every web app. Let's walk through the two real-world SQL examples and how to fix them.
Chat with me live for free code reviews, lessons, and career advice: / strager
stethoscope clipart PNG Designed By 颖 from Pngtree.com
Database icon made by Pixel perfect (www.flaticon.com/authors/pixe...) from www.flaticon.com

[@MaxCoplan]

Wow, I have learned so much from this series. Can't wait for the SQL

[@professortrog7742]

Pah dum stsjj

[@gdargdar91]

If you absolutely need to fetch the row before incrementing then use a SELECT … FOR UPDATE query

[@strager_]

Cool, I didn't know about this feature! (I'm normally a SQLite guy, and SQLite doesn't have this feature.)

[@nenadjovanovski1461]

you my good sir have earned my subscription.

[@alexgorodecky1661]

I think that it will work only on serializable isolation level. Otherwise you should use select for update or optimistic lock / retry. For this task you should use sequence, btw

[@ijlijl]

Nice work!

[@ES-eb6pb]

I would love more videos like this one!! I see so much software that pretends that everything is single threaded and that there is no horizontal scaling, there is so much software that uses ORMs that facilitate these kinds of errors... What would you do if you could not make the "code" column of the "URLs" table a unique key (due to restrictions)? A friend of mine had a similar problem where he couldn't make a column unique because there were duplicates in the table and these duplicates could not be deleted due to business restrictions. His solution was to add a Redis instance due to it being singlethreaded and ask Redis if the value exists. While this works, I wonder if there is a simpler method.

[@strager_]

I haven't thought about your problem much, but here's another approach: * Add a new column called url_disambiguator_for_legacy_reasons. It's an integer which is normally 0. * For those weird rows which need a duplicate URL, set url_disambiguator_for_legacy_reasons for those rows to different numbers (such as 0, 1, 2, 3, and 4). * Add a UNIQUE constraint on (url, url_disambiguator_for_legacy_reasons). * On INSERT, always set url_disambiguator_for_legacy_reasons=0.

[@yasscat5484]

great content

[@HenryLoenwind]

I think the "use a lock" suggestion was for a database lock. That would work, but locking a row that every single page access needs to get at some point represents a big bottleneck. On the other hand, using an inline update will also do a row lock, only faster as it's all inside the database engine. But it's still a bottleneck. And it's the kind of bottleneck you will notice exactly at the moment your site gets popular (do people still know what "slashdotted" means?). A better way would be to queue the update, either with a microservice or inside the database by spreading out the updates over as many rows as there are service workers and having a scheduled process that consolidates the data into the master row from time to time (e.g. once a minute). (start transaction; lock table counters in share mode; update counters set (max, last) = (select sum(max), max(last) from counters) where owner is "master"; delete from counters where owner is not "master"; commit;) BTW: Who doesn't anyone use transactions anymore? Do people really like corrupted data from processes running into an error halfway through updating some data? Or have people nowadays lost all respect for SQL and think everyone can do it without any training on-the-fly just because they can code?

[@strager_]

I don't really know much about SQL. I was out of my comfort zone with this video. 😳 > Who doesn't anyone use transactions anymore? Transactions are tricky and counterintuitive, but they *seem* simple and magic. Non-linearizing transactions (which I think is the default everywhere) almost feel like atomics in C++.

[@alexgorodecky1661]

Simplest way for high load for this case is to pre-aggregate on backend-side with atomic integer, and flush into db every N seconds / minutes in separate thread

[@AceixSmart]

Nice! How often do you stream?

[@strager_]

Every week day!

[@sdegueldre]

Doesn't the solution you proposed for part 1 only work with the correct transaction isolation level? (read commited)

[@strager_]

Hmm, do issues happen for weaker isolation levels even for a single row? I thought reordering issues happen only when dealing with multiple rows. (I'm not SQL expert though, so you could be on to something!) Perhaps I should make a test program to experiment. Maybe I'll learn something interesting! =O

[@sdegueldre]

@@strager_ If you do test it keep me posted, what I read in the postgreSQL docs seems to suggest that update x = x + 1 may not be coherent on "read uncommited" transaction isolation level

[@bobbycrosby9765]

@@sdegueldre postgres doesn't really support read uncommitted - it just treats it as read committed. I generally prefer repeatable read, but read committed is OK too. And if you're going to go with something like read uncommitted, you better know your stuff. If you can't solve race conditions in read committed, you have no chance in read uncommitted.

[@newmonengineering]

You are running an update statement so the dB will force all transactions to process sequentially. Read uncommitted is for reads has nothing to do with update statements. At least for SQL, DB2 and Oracle which are the large ones I use everyday.

[@emonymph6911]

How do you even learn this shit? You're a GOD!

[@strager_]

Make software.

[@loc4725]

ISO says it's pronounced "Ess-Que-El"; SEQUEL was the incompatible but similar predecessor. Then again you're probably using MSSQL (embrace-extend-extinguish) so I understand.

[@strager_]

ISO probably says it's pronounced "eye ess oh". 🤣

[@loc4725]

@@strager_ I could be wrong but I think the most likely reason why they called it "Ess-Que-El" was because the original designers called it that, to differentiate it from SEQUEL, which was a different product.

[@marcs9451]

It's pronounced "squeal"

[@loc4725]

@@marcs9451 MS Squeal. 😄

[@slr150]

This is pretty fundamental stuff. Not sure you need to be an expert to know this.