Reputation: 2891

Is MySQL "thread safe" from a php script?

If I have a php script which calls INSERT, UPDATE, DELETE, etc on a MySQL connection, and that script gets called at uncontrolled times by a POST operation, is it always "safe" (ie, will not result in corrupt tables or collisions during requests)?

For example, if 500 requests come during a 1-second period.

If so, how does php/mysql achieve this?

If not, what does one need to do to guarantee "serial" access or safe simultaneous access?

Upvotes: 9

Answers (6)

Marcus Adams

Reputation: 53860

MySQL uses locks for Isoloation, and transactions for Atomicity. Transactions require InnoDB or BDB. InnoDB supports full ACID support.

Locks and transactions, combined, will resolve your concurrency issue.

By default, MySQL has implicit transactions.

Definitely learn about these features to see if they fit the bill. MyISAM uses table locking, whereas InnoDB provides row level locking, so one may serve your needs better than the other.

Upvotes: 2

Bedo

Reputation: 937

Usually databases are solid to collisions, however there are important operations those must be completed or discarded. Think about a cash deposit on a bank account.

In order to achieve this result you could be interested in using transactions:

PHP + MySQL transactions examples

Upvotes: 1

Maxim Krizhanovsky

Reputation: 26699

MySQL uses locking (table-level for MyISAM or row-level for InnoDB), which does not allow 2 processes (2 calls to the script) to modify the same row. So the table won't crash*, but it's possible that MySQL can't handle the number of request in reasanoble time and the requests will wait. You should always optimize your queries to be as fast as possible.

*MyISAM could crash on insert/update intensive applications, but it has automatic recovery. However keep in mind that in such application, InnoDB has far better performance

Upvotes: 11

kosa

Reputation: 66647

I think the terminology you are looking for is transactions and isolation levels. If these are set according to your requirement, you don't need to worry about collisions. Here is tutorial on how it works.

Upvotes: 0