Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save DanielLoth/6a8777dd978b8d00dbe5d6fa880fed59 to your computer and use it in GitHub Desktop.
Save DanielLoth/6a8777dd978b8d00dbe5d6fa880fed59 to your computer and use it in GitHub Desktop.
comp.theory.database 2021-06-25
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment