comp.theory.database 2021-06-25

gistfile1.txt

create procedure dbo.AddAttendance_tr
  @OrganisationId int,
  @PersonId int,
  @AttendanceDate date
as

set nocount on;

------------------------------------------------------------
-- Validation block
--
-- Determine whether or not a row already exists
-- for the key {OrganisationId, PersonId, AttendanceDate}.
------------------------------------------------------------
set transaction isolation level read committed;

declare @RowExists bit = 0;

select @RowExists = 1
from dbo.Attendance
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate;


if (@RowExists = 1) return 0;


------------------------------------------------------------
-- Transaction block
--
-- At this point we know there's a chance that we will
-- succeed in inserting a row.
-- However we might conceivably be racing another session
-- that is calling this procedure for the same key
-- {OrganisationId, PersonId, AttendanceDate}.
--
-- Within a transaction:
-- 1. Re-execute the validation query, but this time acquire
--    an update lock while doing so (using hint 'with (updlock)').
-- 2. If the row is still absent, we can now safely insert
--    our new row.
--    This is due to the selection of an appropriate transaction
--    isolation level - in this case serializable - that
--    guarantees no other transaction can insert this row
--    while we hold our update lock.
--    With the isolation level set to serializable, the session
--    that we are running our query in (using hint 'with (updlock)'
--    has acquired a KEY lock from the lock manager.
--    This KEY lock ensures that no other transactions running
--    within other sessions can insert a row with this primary
--    key (the Isolation in ACID).
------------------------------------------------------------
set transaction isolation level serializable;

begin transaction;

select @RowExists = 1
from dbo.Attendance
with (updlock)
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate;


if (@RowExists = 0)
begin
  insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)
  values (@OrganisationId, @PersonId, @AttendanceDate);

  commit;
end
else
begin
  rollback;
end

return 0;