How to handle race condition for generate counter in application?

Yii 1.1 General Discussion
1

I have a table that depend on composite primary key and a increment type like below :

ROW : (Year, Id Brach, Id Number, DATA)

PK : year, id Brach, (Id Number->expected to be increment)

Now this is User workflow that i want when inputing a data :

  1. Put only DATA cols (year, Id Brach and id Number will be provided by system)

  2. Application store the data in database ( perhaps :D )

  3. Application show all data (data) with (id), so user can write the [id number] in some real paper form

Now the problem, how handle race condition ?

Which is better should the application keep the id number of last counter, and then store the data? (thus eliminate second workflow) in other word the application will handle counter number, where in original workflow database is the one who handle counter.

Because below constraint, currently i cant depend on database serial counter (anyway is it even possible to use different set counter for different branch in database ? (mine using PostgreSQL))

Another constraint is each branch will have different counter set where at each new year the counter will reset to 1, see example of some data :


Year  Id Brach  Id Number Data

-------------------------------

2011  18        2456      "This is a test"

2011  18        2457      "Hey thats my food"

2011  14        1023      "Just another test"

2011  26        1         "Hey i am new branch"

2011  14        1024      "Nice to meet you branch 26"

2011  18        2458      "Hey same here branch 26, welcome"

2011  26        2         "Thanks for warm welcome <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/smile.gif' class='bbc_emoticon' alt=':)' />"

2012  18        1         "Happy new year guys"

2012  26        1         "Ya Hoo, goodbye 2011"

And using application to handle counter just scare me out because possible race condition

(or even its actually safe? me just being too paranoid. After all there is no one in same brach will input at same time so that application will screwup when handle the counter, thus resuting “Duplicate primary key” error in database, right? :) )

Thank You

2

Well, I can think about two solutions currently:

  1. you can store data in db in step 2, but set some status/confirm field to 0, and get generated id, and show that id to user. If user save this data later, you save just additional data, and set status to 1. If you are using transactional tables, use those queries inside transaction, so you can always roll back

  2. Another way is to use posgree sql serial data type, so you can use this data type for your id field.

  3. you could eventually generate random id, but option 2 is much better, as you r db already support serial data type.