4 ms·
The transaction-free approach still has potential issues though. Consider: select isbn, copies_in_stock from books where isbn = "12345678" an
by JoshuaDavid 7y ago
The transaction-free approach still has potential issues though. Consider:
select isbn, copies_in_stock
from books
where isbn = "12345678"
and copies_in_stock >= 1;
isbn: 12345678
copies_in_stock: 1
update books
set copies_in_stock = copies_in_stock - 1
where isbn = "12345678"
If you have two of the above requests happen at the same time such that the execution order is SELECT SELECT UPDATE UPDATE, you'll end up with -1 copies_in_stock. You can try adding an "and copies_in_stock >= 1" to the UPDATE and then look at the number of affected rows, and perform recovery logic if the number of affected rows is 0, but at that point you've basically implemented a transaction, probably badly if it isn't what you set out to do.
- pmontra 7y agoI understand that this is an example so I try to build some context around those queries. The issue here is that you (apparently) want to guarantee that whoever sees the result of the select (probably a customer) can safely buy the last available book with the update. This can't be solved in a simple way because minutes can pass between that select and that update. This means that you can't put those queries in a transaction. If you could you should use select for update https://www.postgresql.org/docs/current/sql-select.html https://www.postgresql.org/docs/current/sql-select.html I used code like update table set counter = counter + 1 / - 1 many times without transactions when all I have to do is to update a counter and nothing else in the system really cares about the value of the counter. If this is not the case, the queries accessing the counter should use some form of locking. A transaction is not enough. This is the first good guide I found googling for examples https://vladmihalcea.com/a-beginners-guide-to-database-locking-and-the-lost-update-phenomena/ https://vladmihalcea.com/a-beginners-guide-to-database-locki...
- Sacho 7y agoI don't see how a transaction saves you here(without locking the row), can you elaborate? In EF projects, this problem is solved through optimistic concurrency, similar to the fashion you described - a column that changes on each update is used as a canary to confirm that you're applying changes to the record you expect. https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting... This also has the added benefit of allowing you to issue only 1 query(just the update), instead of select + update.
- ivanhoe 7y agoDon't mix transactions and locks. Transaction just ensures that all queries within it were run successfully and atomically. Locks are used to prevent race conditions. And in your particular example neither a transaction, nor lock would help, you need to check explicitly in UPDATE if you've got any books left.
- balfirevic 7y agoRace conditions absolutely are among the set of things transactions are supposed to help with and locks are one of the several ways they do it. It's the I from ACID, which stands for Isolation. Transactions are not omnipotent in that regard and they fail in several well-researched ways, but it is in their purview.