0

Quick question/clarification required here. I have a DB table that will quite possibly have simultaneous updates to a record. I am using Zend Framework for the application, and I have read about two directions to go to avoid this, first being table locking (LOCK TABLES test WRITE) or something like that, will go back and re-read how to do it exactly if that is the best solution. The second being transactions: $db->beginTransaction(); ... $db->commit();

Now 'assuming' I am using a transactional storage engine such as InnoDB, transactions seem like the more common solution. However does that avoid the following scenario:

User A is on a webpage -> submits data -> begin transaction -> read row -> calculate new value -> update row -> save -> commit

User B is on the same webpage at the same time and submits data at the same time, now lets just say it is almost simultaneous (User B calls the update function at a point between begin transaction and commit for User A's transaction) User B relies on the committed data from User A's transaction before it can achieve the accurate calculation for updating the record.

IE:

Opening value in database row : 5 User A submits a value of 5. (begin transaction -> read value (5) -> add submitted value (5+5=10) -> write the updated value -> save -> commit)

User B submits the value of 7. I need to make sure that the value of User B's transaction read is 10, and not 5 (if the update isn't done before read).

I know this is a long winded explanation, I apologize, I am not exactly sure of the correct terminology to simplify the question.

Thanks

Aaron Murray
  • 1,920
  • 3
  • 22
  • 38
  • 1
    Can you get away with doing something like `UPDATE table SET value=value+5`? Then transactions aren't necessary. – Michael Mior Nov 16 '11 at 05:32
  • That could work, would make my life easy for sure. Only one problem that I *might* encounter. I do the update, and then a second session does an update before I read the value, and I need to send the correct value back to the original user. So it would be an update and then a select to retrieve the current 'value'. Would hate for a second update to go through before the read took place, then the returned result would be invalid (or am I incorrect). The reason for my concern is that data must be 100% guaranteed correct when updated and returned to the user. – Aaron Murray Nov 16 '11 at 05:40
  • I don't imagine you can get 100% correct. Even if you ensure the correct value is read, the instant you send it the user and release the lock, it could be out of date. Unless you have an event-driven system receiving real-time database updates, you won't be guaranteed to have the current value. (Even then, there could be noticeable delay.) – Michael Mior Nov 16 '11 at 05:49
  • The more I think about it, for accuracy sake, maybe a better solution would be an audit style, where a record is inserted with the set value, then that record is read with the previous record and the sum of the previous record and the inserted record is returned to the user. I realize that the 'current' value would possibly be newer yet, but that is fine, so long as the calculated value is guaranteed correct, and an audit history on all additions would also be available as a side effect which could be useful. – Aaron Murray Nov 16 '11 at 05:58
  • That would work. In that case, you would want to use transactions. – Michael Mior Nov 16 '11 at 06:07
  • See this one: http://stackoverflow.com/questions/7618300/database-transactions-in-zend-framework-are-they-isolated – jack Dec 25 '12 at 10:19

1 Answers1

1

transactions doesn't ensure locking. The whole block in transaction is treated as atomic update to db (if anything fails in between all previous changes of this block are rollback). So, two transactions running in parallel can update same row.

You need to use both. 

Transaction do
 row.lock
 update row
end

see, if row level locking can make it easier for u.

Nakul
  • 1,574
  • 11
  • 13
  • I am going to accept this answer based purely on the fact that it answered my original question of (the quick version) "will transactions protect against race conditions", and the answer is no. I think the comments @MichaelMior helped me look at my issue from a different perspective and provided a more viable solution. So thank you both / Peace! – Aaron Murray Nov 17 '11 at 02:22