JSON Strings – Storing Large Data for Many Users

If you don't use transactions, then yes, there is a minor race condition that can result in having the incorrect value for the total time spent for a brief while. Consider for example the following timing of events:

[task 1 added]
[total selected]
                [task 2 added]
[total updated]
                [total selected]
                [total updated]

Now the client 1 selects the total amount, but a new task is added after this and for a brief while, the total contains an incorrect value. However, soon after the client 2 will update the total to the correct value.

Can the tasks be deleted in addition to adding them? If so, the situation might become more complex and requires further analysis.

You should really use transactions in this use case. This ensures that the updating of the total and the addition of the task happen simultaneously when viewed from outside of the transaction. Using a transaction with a proper isolation level eliminates the minor race condition.

You should note, however, that while transactions help in this use case there are various transaction isolation levels and all potential race conditions in don't disappear unless your database truly supports the SERIALIZABLE isolation level and you are actually using it. Many databases have the default isolation level set to something else than SERIALIZABLE and do not actually support the true SERIALIZABLE isolation level while they silently accept it and offer something worse. To read about transaction isolation levels, see e.g. http://www.postgresql.org/docs/9.1/static/transaction-iso.html (this is for PostgreSQL, not MySQL)

For an example of the case when the race condition can occur despite using transactions, see this timing:

[transaction 1 begins]
                       [transaction 2 begins]
[task 1 added]
                       [task 2 added]
[total selected]
                       [total selected, doesn't yet see transaction 1]
[total updated]
                       [total updated, waits for lock of transaction 1]
[transaction 1 commits]
                       [transaction 2 commits]

Now transaction 2 updates the total but doesn't yet see the row added by transaction 1, and thus the total ends up containing only the value of task 2 but not the value of task 1. This happens if you're not using the SERIALIZABLE isolation level. If, on the other hand, you are using the SERIALIZABLE isolation level on recent PostgreSQL, one of the transactions would fail due to serialization failure and you would need to be prepared to retry.

To fix this without using SERIALIZABLE, you need to select a locking row for update (the SQL command SELECT ... FOR UPDATE):

[transaction 1 begins]
                       [transaction 2 begins]
[select for update]
                       [select for update, waits for transaction 1]
[task 1 added]
[total selected]
[total updated]
[transaction 1 commits]
                       [select for update wait done]
                       [task 2 added]
                       [total selected, doesn't yet see transaction 1]
                       [total updated, waits for lock of transaction 1]
                       [transaction 2 commits]

I'm not sure if MySQL supports the true SERIALIZABLE isolation level or if it's just snapshot isolation. There was at least some work put into it by Michael Cahill, but I'm not sure if that work got ever merged to the MySQL distribution.

Do note that for transactions to work properly, you need to use a transactional database engine. I.e. InnoDB instead of MyISAM.

runs every minute

First I'd make sure if that's required. Querying an API aggressively will sometimes throttle you or drop your queries. (Depends on who is running it).

Regarding PHP and cURL that should be fine. I've written a similar system that queried over 200K character stats that way, but I just did it hourly in blocks of like 500 characters I think it was.

20 users each request

Find the minimum amount of data you need and try to query the maximum amount of characters you can query and use that. If 20 is it then that seems very low. Ensure you're building the query optimally also for their system. Sometimes, depending on the API, rather than querying for individual characters or groups of characters it's much faster to query for all the characters sorted by id (with a last login time > than say a few months back) with a start (offset from the beginning) and limit. (If the API supports that feature). This tends to allow you to query large blocks of characters quickly.

Also if possible query for different data at different rates. There's usually a lot of API data that rarely changes.

Lastly look for or request a WebSocket based API. A lot of games with real-time data are setting them up. These usually function like a message queue where you can subscribe to certain stat changes.

(Also one last comment. Look for their API discussion forum. Often the person managing it will post there and just tell you the optimal way they wish people to use the API. Your use case is fairly common for a lot of sites).