CODEWITHSUNDEEP
mysqlselecttransactions
Paul Green
Paul Green

Reputation: 65

Is a transaction what I require here?

I have a table that is collecting data input by users.

I want to carry out the following SQL statements:

SELECT statement 1

SELECT statement 2

UPDATE table rows that I've read out in the 2 select statements

I want to guard against the possibility of another user inputting new data in between any of the statements.

I've read the MySQL manual and it seems that I could lock the tables first, but I'm more familiar with transactions and I would like to know if wrapping a transaction around the 3 statements would achieve what I want. I've found it quite hard to be certain this will work from reading the manuals (or maybe it's just me....)

Upvotes: 4

Views: 72

Answers (1)

Adrian
Adrian

Reputation: 46432

There are two possible problem scopes here; transactions (if you're using an engine that supports transactions, like InnoDB) will solve one of them.

Transactions keep all of your queries operating on a snapshot of database state when the transaction was started, and any modifications are applied all-or-nothing when the transaction is completed. This effectively solves interleaving and race conditions with the queries.

However, you stated that you want to prevent a user inputting new data in between any of the statements. If this is a situation where you want to ensure that a user submitting a request is starting from current data, you'll need to implement your own locking mechanism, or at least a way to trap cases where interleaving between requests is causing an issue.

Basically, transactions will only help with queries running in concurrent requests. If this scenario would be a problem:

  1. User1 requests data
  2. User2 requests data
  3. User1 submits modifications
  4. User2 submits modifications

Where User2 was able to submit their changes without knowing about the changes made by User1, you need your own locking system; transactions aren't going to help. This is coming from a web development background where each step is a separate web request in a separate transaction.

Upvotes: 2

Related Questions