1

Just a quick question - I am writing a PHP app that relies heavily on SQL databases. I am slightly worried that many users all accessing the same database will cause errors. If so, how can I overcome this, what can be done?

I am not sure how many users at the end of the day will be using it, not hundreds, but maybe 5 or 10 or even 20. And even then its unlikely that they will be updating at the EXACT same time, but still. Any thoughts?

Chud37
  • 4,907
  • 13
  • 64
  • 116
  • What kind of errors are you expecting? – JJJ Jun 30 '12 at 14:25
  • Well I just wonder what exactly happens if say two people update the SAME table at exactly the SAME time, surely that would produce an error? – Chud37 Jun 30 '12 at 14:27
  • http://stackoverflow.com/questions/5524182/what-would-happen-if-2-or-more-people-attempt-to-update-the-same-mysql-table-at - http://stackoverflow.com/questions/4399191/web-inserts-at-the-same-time - http://stackoverflow.com/questions/11099890/2-users-updating-a-record-at-the-same-time – Mike B Jun 30 '12 at 14:29
  • Database servers handle concurrency access, reading/writing lock. Don't worry :) – Francois Jun 30 '12 at 14:30
  • so the solution.. is locking the tables? it seems to say that mySql is very good at preventing deadlocks? – Chud37 Jun 30 '12 at 14:33
  • @FrancoisB. So I dont need to modify my SQL then to LOCK tables? It should do it by itself? – Chud37 Jun 30 '12 at 14:34
  • is the question to update different rows in the same table or update the same row in the same table? - updating different rows at absolut the exact same time is 100% not a problem - this is a default usecase for database server, updating the same row at exactly the same time is a bit tricky and here locking would be a good idea. BUT if you lock for user A and user B is writting something you should say user A that there is a change - thats the tricky part – Martin Holzhauer Jun 30 '12 at 15:50
  • As proposed by **Struna**, use MySQL's transactions and some OpenSessionInView pattern: at the beginning of the request (http request), open a transaction, do the job, then commit if ok/rollback if error, and close the transaction. – Francois Jun 30 '12 at 15:51

1 Answers1

1

MySQL Transactions is what you're looking for.
Then PHP PDO extension with its transactions management.

Taz
  • 3,718
  • 2
  • 37
  • 59
  • Thanks. Yeah but i'm saying, there seems to be two thoughts: one is, with mySql its not needed, it handles concurrency in its own way, and two, start locking your tables. So which is it? – Chud37 Jun 30 '12 at 14:40
  • Oh yes, it's needed with MySQL. By default it does not handle the concurrency the safe way. This is why transactions are provided where there's a need. There's another question on SO that may answer your question: [mySQL Transactions vs Locking Table](http://stackoverflow.com/questions/4226766/mysql-transactions-vs-locking-tables). – Taz Jun 30 '12 at 14:54