0

I'm currently developing a website and while working on database design, i had some concern on concurrency issue, I'm considering using timestamping to avoid this.

My understanding in timestamping is that it works this way:

  1. There is a field for let's say "DateModified" wherein be updated every update on that specific row.
  2. Then whenever there are 1 or more users accessing that row like reading first then eventually update it.
  3. In my understaning of timestamping for this to work, I need a condition that will read first the "DateModified" like in my code.

readdatemodified = Select DateModified From Transaction where ID = ?

datemodified = Select DateModified From Transaction where ID = ?

IF datemodified == readdatemodified UPDATE Transaction where ID = ? ELSE Message "There's someone updated the record. Please try again".

IF: UPDATE the record successfully

ELSE: Here the record will be retrieve again by accessing the database to ensure that the record is the updated one.

I solved the concurrency issue here but my new concern is how I access the database. I will accessed the database multiple times every update?

Is there a way wherein I could minimize the database access using timestamping?

Echusen
  • 321
  • 3
  • 10

2 Answers2

0

Have you considered not using timestamping, but using transactions and locking reads:

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

This might be a better solution to your problem. MySQL 'select for update' behaviour shows some examples of behaviour in the question.

The capabilities provided by locking are dependent on the underlying database engine you use:

  • MyISAM - Table level locking
  • InnoDB - Row level locking

A good overal description of capabilities and advantages can be found on the MySQL site here: http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

Community
  • 1
  • 1
ModulusJoe
  • 1,416
  • 10
  • 17
  • I just want to ask, so the idea of transactions is it locks the table/data based on ID or it locks all the data/table? – Echusen Oct 30 '13 at 03:17
  • It is dependent on the underlying database engine. I have updated my answer to include a reference to the mysql locking description. Generally Row locks will lock each row involved in a transaction, which in this instance relates to an ID as, I assume, you will only have a single ID per row. If you have 'ID's spanning multiple rows then you would need to ensure that you locked all the rows required with row level locking. If you are using MyISAM and table level locking the whole table would be locked. You would need to weigh up how much impact a table level lock would have on your application. – ModulusJoe Oct 30 '13 at 11:56
0

If you want the concurrency / checks in application logic, then try a CAS (Check And Set) algorithm, if you want concurrent changes to not happen, use transactions (as mentioned by Acyclic Tau)

Ronald Swets
  • 1,669
  • 10
  • 16