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 :
Put only DATA cols (year, Id Brach and id Number will be provided by system)
Application store the data in database ( perhaps )
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? )