A problem which often arises when working with database applications is that of concurrency control, IOW, preventing race conditions such as the classic: you load a user's info page, someone else loads it, you change his name and save it, the other person changes his telephone and save it, losing the change you made.

"Traditional" two-tier applications which run the app logic and interface on the user's computer and use a database for storage had an easy way out: open a transaction when the user's record is opened and keep it locked for updates. However, in three and four tier web applications that is not feasible. A few alternatives arise:

— Keep track of records being edited with an "in use" field recording who's editing it. Problems arise in cases the editor goes away or waits too long to unlock.

— Add a version counter to each record, incremented in each update, and have your update fail (... AND vcounter=10 ...) if someone else changed it while you weren't looking. Simple and effective.

— Keep a change history in the form of a table containing records of all changes to all tables for a given time frame, and when a race condition is detected then reconcile the changes so that none's lost. Best approach, but requires a lot of work.

So, those are my ideas. Anyone got another approach?

P.S.: Saying that you should only update fields that actually changed, while a valid approach, doesn't count since it doesn't solve the problem. Two people could be updating a credit or stock total, for instance.

<sig>ESC[78;89;13p ESC[110;121;13p</sig>

In reply to Concurrency control in web applications by cbraga

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.