title: SQL Server: Avoid simultaneous updates increasing a column value over its target
body: <p>I have a SQL Server table called <code>AD</code> where ad's to be viewed are stored as</p>
  
  <pre><code>create table Sponsors.AD
  (
      ADID varchar(40) primary key,
      SponsorID varchar(30),
      PurchasedViews int , --How many views the ad must reach before it is disabled
      CurrentViewCount int, --Keeps track of how many views the ad has gotten
  {...}
      Active bit -- for easier checking of whether the AD still has clicks to give
  )
  </code></pre>
  
  <p>This feeds into a webpage where, to access a feature, users first need to view an ad. Users can pick one ad from a menu that displays three options [they pick one, the ad's media is displayed and the feature is unlocked at the conclusion].
  After they view the ad, its <code>CurrentViewCount</code> should be updated (increased by 1).</p>
  
  <p>This is handled by a stored procedure that includes an update call for the table - separate from the stored procedure that fetches 3 ads at random for the option menu - but I'm looking for suggestions on how to solve the problem of synchronizing all concurrent <code>AD</code> views - as it could happen that</p>
  
  <ol>
  <li>two or more users have the same ad in their 3-choice-menu</li>
  <li>two or more users view the same ad at the same time</li>
  </ol>
  
  <p>1 and 2 are not a problem on their own but they could be if the ad is one click away from it's set maximum.</p>
  
  <p>One way I've thought to solve this is to set the active flag as false if the ad is one click away from it's target when it is displayed in the 3-option menu, and if the user does not click it, the flag will be reset to true -- but then I'd need to handle cases where the user exits the option dialogue or disconnects, times out, etc. I feel like there must be a better way.</p>
  
  <p>Another suggestion I've heard is to automatically increase the counter when the ads are summoned to the 3-option menu but that's even more overhead than the other and suffers the same issues.</p>
  
  <p>Locking the table is absolutely infeasible unless we wanted to only serve one ad view at a time - so I'm not even considering it.</p>
  
  <p>I'm sure something like this has been discussed before but don't know what keywords/etc to search to find more on this.</p>