-1

say I had a survey where there are two radio buttons. Yes/No. obviously I don't want to store a thousand "yes's" and "no's" in a database. I just want a single number to count the "yes's" and a single number to count the "no's". it look like this is the easiest method

UPDATE `databasename`.`tablename` SET fieldB = fieldB + 1 WHERE fieldA='#';

but I'm wonder if 2 people submit a survey at the same time would each look at the database see 500 "yes's" and both update it to 501. or is MySQL set up in away that one would have to wait fir the other to finish first.

Gumbo
  • 643,351
  • 109
  • 780
  • 844
mike
  • 43
  • 1
  • 7
  • 1
    possible duplicate of [concurrent READ and WRITE on MySQL Table](http://stackoverflow.com/questions/15637532/concurrent-read-and-write-on-mysql-table) – Nima Nov 30 '14 at 18:29

1 Answers1

0

Your existing sql will work and should not have any race conditions.

Transactions block so that two transactions are not being executed at the same time. By default each query is a transaction.

Take a look at different types of database tables for more specifics: myisam vs innodb

There exists possibilities for race conditions if you have complex backend replication that does not have guarantee row locking between.

In order to ensure a separation of concerns between infrastructure and code you should consider your original insert row solution. With proper a multi column index your read should be really quick. Also as your applications scales inserts are cheaper (execution time) than updates and when you application becomes more complex your data is already more normalized so it will be easier to make your responses more complex than yes/no. See this wikipedia page for some explanations of why normalized data is helpful.

Community
  • 1
  • 1
Ryan Taylor
  • 71
  • 5