How to get a mutual exclusion on select queries in SQL Server

Question

I know maybe I'm asking something stupid in my application users can create a sort of agendas but only a specific number of agendas is allowed per day. So, users perform this pseudo-code:

select count(*) as created
from Agendas
where agendaDay = 'dd/mm/yyyy'

if  created < allowedAgendas {
  insert into Agendas ...
}

All this obviously MUST be executed in mutual exclusion. Only one user at time can read the number of created agendas and, possibly, insert a new one if allowed.

How can I do this?

I tried to open a transaction with default Read Committed isolation level but this doesn't help because during the transaction the other users can still get the number of the created agendas at the same time with a select query and so try to insert a new one even if it wouldn't be allowed.

I don't think changing the isolation level could help.

How can I do this?

For testing I'm using SQL Server 2008 while in our production server SQL Server 2012 is run.

Answer 1

it sounds like you have an architecture problem there, but you may be able to achieve this requirement with:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

If you're reading an inserting within the same transaction, I don't see where the problem will be, but if you're expecting interactive input on the basis of the count then you should probably ensure you do this within a single session of implement some kind of queuing functionality