Is a MySQL procedure thread safe?

I am developing some websites that need to interact with a database. I will not bring here a complic开发者_StackOverflow社区ated example. My question actually comes down to: Is a MySQL procedure thread safe? If one client on my site triggers a procedure, can I assume it is atomic, or could it interfere with another request from another user?

Depends on if you're using SQL transactions. Its possible, without the appropriate use of transactions and the actual serialization level, that a procedure can expose some data in a write call, for instance, that is visible to other queries / procedures before the complete procedure has completed.

in short: a given procedure will only be atomic if you use the appropriate transaction level

The database will handle concurrency for you. This is normally done via transactions - any set of statements within a transaction is considered atomic and isolated from other processes. In some databases, a stored procedure will be in an implicit transaction (so you don't need to declare one) - read the documentation for your RDBMS.

Sometimes this will mean that records are locked while another process tries to use them.

You will need to write your application so it can detect such occurrences and retry.

It really depends on how your server is configured to use transactions. There are tradeoff to consider depending on how your data is used and whether or not dirty, non-repeatable, or phantom reads are acceptable for your application.

Yes.

It's the DB's job to ensure thread safety among its worker threads, and it's your job to ensure thread safety among your application threads. Since there's a separation between the DB server, and your application, you don't need to worry about thread safety in this case. MySQL's data locking mechanisms will prevent you from corrupting the data in the DB due to simultaneous access from multiple threads in your own app.

Thread safety is more about modifying data in-memory, that is also shared among multiple threads within your app. Since the DB server is its own, separate application, it basically protects you from the scenario you've outlined above.