CODEWITHSUNDEEP
phpmysqllocking
Martin Konecny
Martin Konecny

Reputation: 59691

MySQL locking question

I have something similar to an online chat client. When one user sends a message it will be stored in a MySQL database table with id=currentID+1. I have another client long polling and waiting for message 'id=currentID+1'.

After this exchange, that row is never used again.

Do I need a lock in this case? The thing I'm worried about is that the reading PHP side will be able to see the row and read its values before the writing PHP side finishes creating the row.

Upvotes: 1

Views: 198

Answers (3)

ovais.tariq
ovais.tariq

Reputation: 2625

the new row will only be seen by the select query after its inserted.

inserts are atomic

and yes myisam imploys table level locking, while innodb imploys row level locking.

and set of statements in a transaction are considered atmoic, there effect is not visible to any read until the transaction is committed, u can do a select shared to see uncommitted data.

Upvotes: 0

ryeguy
ryeguy

Reputation: 66911

Writes in MySQL are atomic. Other queries cannot "see" the row until it is completely written, assuming you're using a single INSERT statement to do this.

Upvotes: 0

ircmaxell
ircmaxell

Reputation: 165271

MySQL won't make the row available until it's done reading (it automatically acquires a table lock in the case of MyISAM, or a row lock in the case of INNODB. So no, you should be ok so long as you're only inserting the row (and not later calling updates on it, etc)...

Upvotes: 1

Related Questions