0

I know the basics of PHP and MySQL. If I have a table called "Table1" having the "id", "name", "count". When the user submits messages, it will be made pending so that moderators have to approve it. So, there would be several moderators in my site and when two or more mods accepts the messages of the same user at the same time(which will update the message table as well as the "Table1" - increment "count" by 1), it cause problems. Am I correct ?

So, how to solve this ?

Edit1:

table1 contains the details of the registered users. another table("Table2") is used to store the messages which will store the user id, unique msg id, message, date, etc

Edit2:

the mod would update a field in the message table for those messages that he accepted (update table2 set approved=1 where msgid = 123). and also, when he accept it, an update statement will be executed for table1 (update table1 set count = count+1 where userid = 5). like that, i can eliminate the calling of count() - select count(msgid) from table2 where userid = 5

Vpp Man
  • 2,384
  • 8
  • 43
  • 74
  • using transactions , http://dev.mysql.com/doc/refman/5.0/en/commit.html – Haim Evgi Nov 14 '11 at 06:27
  • what is "Table1" having the "id", "name", "count"? the approvals? or the messages? Do you have one or two tables? explain better the structure of the two tables in case. – stivlo Nov 14 '11 at 06:27
  • 1
    why would a moderator not jsut set the approved column to "1" from 0 –  Nov 14 '11 at 06:28
  • possible duplicate of [MySQL concurrency , how does it work and do I need to handle it in my application](http://stackoverflow.com/questions/4828490/mysql-concurrency-how-does-it-work-and-do-i-need-to-handle-it-in-my-applicatio) –  Nov 14 '11 at 06:30
  • thank you table1 contains the details of the registered users. another table is used to store the messages which will store the user id, unique msg id, message, date, etc. – Vpp Man Nov 14 '11 at 06:31
  • the mod would update a field in the message table for those messages that he accepted (`update table2 set approved=1 where msgid = 123`). and also, when he accept it, an update statement will be executed for table1 (`update table1 set count = count+1 where userid = 5`). like that, i can eliminate the calling of count() - `select count(msgid) from table2 where userid = 5` – Vpp Man Nov 14 '11 at 06:39

3 Answers3

0

There is no concurrency issue when issuing UPDATE table1 SET count = count + 1 WHERE id = ? see the possible duplicate link I provided for more reading on mysql & concurrency.

  • thank you. so i dont have to explicitly do locking or take care of the concurrency ? the current scenario is, update the count when multiple moderators accepts several message of the same user at the same time. also, at that time this count will be accessed by viewers of my site. so a "select" query will also be performed at the same time. – Vpp Man Nov 14 '11 at 06:46
0

Use Lock, unlock *tables* in mysql.

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

Refer : http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

  • so when the table is locked and when a query is performed, it will wait ? – Vpp Man Nov 14 '11 at 06:49
  • Please explain not getting what is ur doubt? –  Nov 14 '11 at 06:57
  • i mean, if i had locked the table and perform the operation, and at that time another user tries to access that locked table, then that user's query will be in wait ? and how much time will it take if i am locking a table and performing the update then unlock the tables ? – Vpp Man Nov 14 '11 at 07:03
  • in practice, this often ends up being the shortest portion of the operation. –  Nov 14 '11 at 07:26
  • means, it is very quick ? also, what is the difference between transaction and locking ? when performing a transaction, will it implicitly locks the table ? – Vpp Man Nov 14 '11 at 07:29
0

You Should use Concurrency (Multi User access) and Locking. In fact Designing application code to take account of concurrency issues is essential.

You can Find The best online tutorial for Locks in mySql here.

ScoRpion
  • 11,364
  • 24
  • 66
  • 89
  • what happens when a table is locked ? same as transaction, except it will not roll back changes ? – Vpp Man Nov 14 '11 at 06:47