Database doesn't use transactions, or for any other reason requests which should be queued synchronously are not, therefore based on workload any requests from 1 to X (where X is the number of queued requests at given time) will execute properly.
Something like:
We have 130 points, we quit 2 giveaways for 20 and 10 points.
We expect to have 130 + 20 + 10 = 160 points, but we got only 140.
It can be also abused when spending points, if the mechanism uses same logic (should be).
Comment has been collapsed.
I don't know what it's using, I say how it looks like to me. Even if they use something new like InnoDB, it's still possible that a guy who coded the site doesn't know how to start and end SQL transation. Single SQL transation is locked, but two independent ones are not and it's totally possible that if you execute 1-2-3-4 asynchronously you may actually get 3-4-1-2 or other combinations.
Some people also disable transactions for performance reasons, sigh.
Comment has been collapsed.
Yeah concurrency is a tricky part of programming. Always a great source of mishap.. Transactions need to be manually initated and completed. The DB designer might not be aware of that. The clever would use stored procedures to let the db subtract/add values so db works on a sane base. So even when asyncronously hit (and transactions locking down too long/too much) they would keep the state clean. That might be a solution.
I too can't stand the idea of sacrficing integrity for speed. Thats a tradeoff that will backfire eventually.
[update] Still asking myselves if a "update set points = least( points + value, 300) where id = userid & value < 300" could do the trick too as also keeps the data integer by not querying it before. And its a single query only -> fire and forget :)
Comment has been collapsed.
It doesn't work like that, that query includes subquery, and transaction holds only one (write) lock. Until you explicitly declare that you want to hold the whole (exclusive) lock for entire table during procedure/query execution, it's totally possible that I'll update points in between your subquery and main query. And holding exclusive lock is expensive, therefore it's better to write independent queries and lock them together where it's essential.
For example:
UPDATE users SET points = points + 20 WHERE user_id=XXX;And a trigger BEFORE_UPDATE:
IF NEW.points > 300 THEN
SET NEW.points = 300
END IF;Above example is lock-safe as long as normal transaction lock is held during UPDATE statement.
Comment has been collapsed.
Using triggers seems rationally the best way to do. Didn't even thought of it. Why not suggesting that solution to cg... he seems to be do maintaining work on the database anyways as of now.
Thanks for pointing out that my update query did trigger a subquery - wasn't aware of that. That would lead to the dilemma i was trying to solve.
As a sidenote... i thought that db systems would do that on their own behalf - i mean aggregating queries and combine those who can be combined. Or does that need another hint given through the syntax?
Comment has been collapsed.
It depends on specific DBMS, and we have plenty of those. I don't know what SG is using, so I can't give you specific answer, I can just suggest checking documentation of the ones you're interested in.
Yes, they SHOULD do that, but avoiding subqueries is essential also for performance reasons. People tend to use easier WHERE XXX IN statements instead of e.g. INNER JOINs. I'm not an expert when it comes to databases, I just know enough to write fairly efficient queries and not struggle in concurrency problems (for longer than it's needed).
Speaking of MySQL/MariaDB for example, there is only one default lock - the write lock, which ensures that 2 update/insert/delete queries executed at the same time will be executed properly. However, there is no default read lock, so if you use e.g. UPDATE with hidden SELECT subquery, it's possible that I can change the value after your select, and before your update.
Comment has been collapsed.
Comment has been collapsed.
366 Comments - Last post 9 minutes ago by Birdie0
0 Comments - Created 24 minutes ago by Chris76de
36 Comments - Last post 1 hour ago by OneManArmyStar
8,474 Comments - Last post 1 hour ago by FranckCastle
15,404 Comments - Last post 3 hours ago by vlbastos
64 Comments - Last post 4 hours ago by romana1994
19 Comments - Last post 5 hours ago by Seibitsu
110 Comments - Last post 3 minutes ago by m0r1arty
60 Comments - Last post 8 minutes ago by hbarkas
36 Comments - Last post 38 minutes ago by Hedgehogman
15,141 Comments - Last post 51 minutes ago by gonsi
99 Comments - Last post 54 minutes ago by Cece09
2,389 Comments - Last post 1 hour ago by JMM72
92 Comments - Last post 1 hour ago by Lachdanan
Left several entries in order to free up points to enter a new game... none of those show up in the points overview - so effectively kicked me out of the ga's but 0p returned... i'm lacking now 103 points!! I cannot post these giveaways here as they are all hidden ga's.
Comment has been collapsed.