1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SagittariusA
  • 5,289
  • 15
  • 73
  • 127

1 Answers1

2

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

oooo ooo
  • 314
  • 1
  • 2
  • 11
  • Thank you oooo ooo. But, what do you mean for "I have an architecture problem"? I mean...suppose that the allowed agendas are 4 and there are already 3 ceated agendas. If two or more users perform a select count() at the same time without mutual exclusion, they will get both 3 and so they both will create a new agenda...instead only one should, while the second, after getting the lock, should get 4 from a select count() and so he should not create a new agenda... – SagittariusA Dec 22 '16 at 11:25
  • It's not clear which version of sql server you're using, but if there is a limit for given day I would make users read the latest value from a sequence, and control the behaviour on whether the limit is reached or not – oooo ooo Dec 22 '16 at 11:34
  • Thank you for your hint...I don't know what sequences are. I'm going to try with serializable isolation level... – SagittariusA Dec 22 '16 at 11:41