Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item is instanced I need to increment usage of the specific item.

My first implementation is:

statistics(
  id      integer NOT NULL,
  name    character varying(255) NOT NULL,
  usage   integer NOT NULL DEFAULT 0,
);


UPDATE statistics 
  SET usage = usage + 1
WHERE name = '<name>';

My concerns are about performance and concurrency. The update process will be instantiated by several tens (maybe 80-120) devices and could occur several times per second, so my questions are:

1) will this method preserve concurrency? (ie if more than one devices request the update "at same time", will every request be counted?)

2) can you suggest a best way to achieve the result? I expect to have load in writing the updates, while the reads would me much more frequent. Does it exist a specific function to increment values? I'm looking at "sequence" but I'm not sure if that is the right way...

Thank you a lot in advance for any advice

share|improve this question

1 Answer

up vote 2 down vote

The second update would wait for the previous update on the same rows to be comitted, but will then see the committed value.

Assume two concurrent transactions updating the same row with an initial value of 0

Time Transation 1  T1 value    Transaction 2  T2 value
--------------------------------------------------------------
1    update ...    1                          0
2                  1            update ..     "undefined"
                                (waits) 
3    commit        1                          2
4                  1            commit        2
5                  2                          2

"T1 Value" and "T2 Value" means the value that that transaction sees.

If you want to make sure you catch situations where there are "incompatible" changes (e.g. one transaction setting the usage column to a specific value, rather than just incrementing it) you can put all transactions into the "serializable" isolation level. But you will need to prepare for error handling then.

Updates to different names can run concurrently without any waits (because different rows are affected).

SELECTs will never be blocked but will only see comitted values.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.