Lets say i have a table with posts, and each post has index of topic it belongs to. And i have a table with topics, with integer field, representing number of posts in this topic. When i create new post, i increase this value by 1, and then i delete post, i decrease value by 1. I do it to not query database each time i need to count number of posts in certain topics. But i heared that this approach may not be safe to use and actual number of posts in table may not match stored value. Is there any ceratin info about how safe is it?
Advertisement
Answer
Without transactions, the primary issue is timing. Consider a delete and two users:
Time User 1 User 2 1 count = count - 1 2 update finishes How many posts? 3 delete post Count returned 4 delete finishes
Remember that actions such as updates and deletes take a finite amount of time — even if they take effect all at once. Because of this, User 2 will get the wrong number of posts. This is a race condition; and it may or may not be an issue in your application.
Transactions fix this particular problem, by ensuring that resetting the count and deleting the post both take effect “at the same time”.
A secondary issue is data quality. Your data consistency checks are outside the database. Someone can come directly into the database and say “Oh, these posts from user X should be removed”. That user might then delete those posts en masse — but “forget” or not know to change the associated values.
This can be a big issue. Triggers should solve this problem.