Attendance_Add_tr.sql

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

-- MSSQL equivalent of disabling chained transaction
set implicit_transactions off;

set nocount on;

------------------------------------------------------------
-- Validation block
------------------------------------------------------------
set transaction isolation level read committed;

if @@TRANCOUNT > 0
begin
  exec ThrowError_OpenTransaction @@PROCID;
end

if not exists (
  select 1
  from dbo.Organisation
  where OrganisationId = @OrganisationId
)
begin
  return 9; -- Organisation does not exist
end

if not exists (
  select 1
  from dbo.Person
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
)
begin
  return 8; -- Person does not exist
end

if exists (
  select 1
  from dbo.Attendance
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
    and AttendanceDate = @AttendanceDate
)
begin
  return 7; -- Attendance on the given date has already been recorded.
end

------------------------------------------------------------
-- Execute block
------------------------------------------------------------

-- This 'set transaction isolation level' statement is an
-- alternative to specifying the 'holdlock' int as done below.
set transaction isolation level serializable;

begin transaction;

if not exists (
  select 1
  from dbo.Organisation
  with (holdlock)
  where OrganisationId = @OrganisationId
)
begin
  rollback;
  return 9; -- Organisation does not exist
end

if not exists (
  select 1
  from dbo.Person
  with (holdlock)
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
)
begin
  rollback;
  return 8; -- Person does not exist
end

if exists (
  select 1
  from dbo.Attendance
  with (holdlock)
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
    and AttendanceDate = @AttendanceDate
)
begin
  rollback;
  return 7; -- Attendance on the given date has already been recorded.
end

insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)
select @OrganisationId, @PersonId, @AttendanceDate;

commit;

Attendance_Get.sql

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

set nocount on;
set transaction isolation level read committed;

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

if @@ROWCOUNT != 1
  return 1;
else
  return 0;

GetObjectName_fn.sql

create function dbo.GetObjectName_fn (
  @ObjectId int
)
returns nvarchar(257)
as
begin

return object_schema_name(@ObjectId) + '.' + object_name(@ObjectId);

end

ThrowError_OpenTransaction.sql

create procedure dbo.ThrowError_OpenTransaction (
  @ProcId int
)
as
begin

declare @ErrorMessage nvarchar(512) = concat(
  'The procedure ''', dbo.GetObjectName_fn(@ProcId), ''' is a transaction, which is atomic. ',
  'It has been called within an open transaction, which would render it a non-transaction. ',
  'This is not allowed.'
);

throw 100000, @ErrorMessage, 0;

end

ThrowError_UtilityTransactionRequiresOpenTransaction.sql

create procedure dbo.ThrowError_UtilityTransactionRequiresOpenTransaction (
  @ProcId int
)
as
begin

declare @ErrorMessage nvarchar(512) = concat(
  'The procedure ''', dbo.GetObjectName_fn(@ProcId), ''' is a utility transaction. ',
  'It must be called from within an open transaction.'
);

throw 100001, @ErrorMessage, 0;

end

Hi Derek,

I'll probably update the above with RAISERROR. Given that the code is written to explicitly perform the rollback, I don't see the need to rely on XACT_ABORT.

Dan

Could you please start another thread or fork or whatever, for the discussion re your DM. If possible, the comments above that pertain to it.

Cheers
Derek

Hi Derek,

I've created a repository which has current code and diagrams here: https://github.com/DanielLoth/ShootingClubDatabase

Pull request with the above diagram here: DanielLoth/ShootingClubDatabase#2

I created another pull request with current SQL code here: DanielLoth/ShootingClubDatabase#3
Note that most stuff is not to specification - only the Attendance-related methods that have already been discussed.
For the most part, I'd ignore this pull request. I just put it there to make it available.

Cheers,
Dan

Dan

Thank you.

Can you possibly remove the comments in thread that are NOT related to the c.d.t thread.
That guy was confused to begin with, and getting more confused as we progress, hindering progress.
Let's keep this thread for your initial stated purpose only, which is associated with the c.d.t thread.

In this thread (sproc template for ACID Transaction using a Lock Manager), there are (b) two issues or conditions that need to be identified and named, before I can give the solution (c).

In the sense of keeping one's eye on the goal, we are heading to is (c) Optimistic Locking (don't tell Nicola). But first we need to understand why it is necessary, which is [b].

Cheers
Derek

Hi Derek,

Busy week for me at work. I'll look to clean this up on the weekend.

Cheers,
Dan

Hi Derek,

I've now cleaned this thread up. I've quoted most of the messages in the modelling-centric discussion here: DanielLoth/ShootingClubDatabase#2

Cheers,
Dan