![]() For example, SQLite does not need SELECT FOR UPDATE because initiating a transaction locks the entire database. Often, the reason for this is related to the way different systems handle transactions more broadly. For example, MySQL supports SELECT FOR UPDATE, but SQLite does not. ![]() However, different ‘flavors’ of SQL handle SELECT FOR UPDATE somewhat differently, and some do not support it at all. In general, SELECT FOR UPDATE is useful for any transactional workload where multiple transactions might attempt to access the same rows at the same time. In other words: SELECT FOR UPDATE makes contended transactions process more smoothly (which generally also means they process more quickly and efficiently). Any time multiple transactions are likely to be working with the same rows at roughly the same time, SELECT FOR UPDATE can be used to increase throughput and decrease tail latency (compared to what you would see without using it). This is useful because it prevents the thrashing and unnecessary transaction retries that would otherwise occur when multiple transactions are attempting to read those same rows. Other transactions attempting to access those rows are placed into a time-based queue to wait, and are executed chronologically after the first transaction is completed. It allows you to “lock” the rows returned by a SELECT query until the entire transaction that query is part of has been committed. ![]() SELECT FOR UPDATE is a SQL command that’s useful in the context of transactional workloads. Luckily, in many SQL databases there’s a solution for that: SELECT FOR UPDATE. But things can get messy when multiple transactions start trying to access the same data at the same time. Relational databases are great for transactional workloads. Learn the SQL you need (and none of what you don't). ORMs are powerful tools, but they aren’t perfect.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |