PHP infinite loop to update database

[h3rm3s]

Hello everyone!

 

I'm (trying to) writing a web application. To this point, all changes to the (MySQL) database were initiated by an action from a user via a web page. But now, I have to write some scripts that should update a complete database each 2 seconds (lots of numbers need to be updated).

 

Now, I'm wondering about a few things:

 

1) When a user updates a field in the database at the same point that the PHP looping script tries to update that same field, what happens?

2) Is it true that the fields more in the beginning of the database will be updated sooner that the last few, and how to avoid this?

3) What if the process of updating all fields takes longer than 2 seconds (2 seconds is the interval between each update)?

 

So these are a few questions I have. If I'm not clear about something, please tell me.

 

Thanks!

[Guy_guy]

Performance depends on the DB size, and it's going do decrease as your DB grows, but an app that requires to fully update a DB every 2s sounds like a bad concept overall imo.

 

Can you explain why the 2s update is a requirement ?

[h3rm3s]

10 minutes ago, Guy_guy said:

Performance depends on the DB size, and it's going do decrease as your DB grows, but an app that requires to fully update a DB every 2s sounds like a bad concept overall imo.

 

Can you explain why the 2s update is a requirement ?

So basically, it's some kind of online "game" / simulator. It will simulate multiple "galactic empires". One of the things it will simulate is spacecraft flying routes (progression in those flights). And lots of "bank accounts", which in most cases will be paying of debt.

 

There are ways to calculate those things based on begin and end dates. But I feel like that would make programming the whole app much harder. since everything is pretty much interconnected. It would make lots of stuff incredibly complicated.

 

But I could try, nothing is impossible.

[Brenz]

This really isn't what a database is designed to do and it certainly will not work well doing this.

 

Instead you should have a server calculating all of the required information constantly with occasional writes to the DB to use in case the server ever crashes or needs to be restarted.

 

On 20/02/2016 at 7:25 PM, h3rm3s said:

1) When a user updates a field in the database at the same point that the PHP looping script tries to update that same field, what happens?

Whichever UPDATE query that was executed last will be saved.

 

at 7:25 PM, h3rm3s said:

2) Is it true that the fields more in the beginning of the database will be updated sooner that the last few, and how to avoid this?

 

Yes, you cannot simply replace thousands or even millions of rows in an instant. It might not take long but they won't all change at the same time, this cannot be avoided.

 

at 7:25 PM, h3rm3s said:

3) What if the process of updating all fields takes longer than 2 seconds (2 seconds is the interval between each update)?

Well if the script waited for the update to finish before it started over again the delay would stretch beyond 2 seconds. If each run of the script was separate then the second script could begin to update data in the database, the problem here is every overlap adds more load onto the database which makes each run slower and slower until eventually the script is running so many times it consumes all the database connections, the server crashes and the whole thing falls over.

[h3rm3s]

2 hours ago, Brenz said:

This really isn't what a database is designed to do and it certainly will not work well doing this.

 

Instead you should have a server calculating all of the required information constantly with occasional writes to the DB to use in case the server ever crashes or needs to be restarted.

 

Whichever UPDATE query that was executed last will be saved.

 

 

Yes, you cannot simply replace thousands or even millions of rows in an instant. It might not take long but they won't all change at the same time, this cannot be avoided.

 

Well if the script waited for the update to finish before it started over again the delay would stretch beyond 2 seconds. If each run of the script was separate then the second script could begin to update data in the database, the problem here is every overlap adds more load onto the database which makes each run slower and slower until eventually the script is running so many times it consumes all the database connections, the server crashes and the whole thing falls over.

Thanks to your answer (and some others), I've come to the conclusion that my idea was bad. But like you said, I'll be doing it in a better way with occasional writes to the database. Thanks! You did help me answering my questions!