Skip to content

Instantly share code, notes, and snippets.

@DanielLoth
Last active July 11, 2021 11:22
Show Gist options
  • Save DanielLoth/a272a601e38c9c6f2c002956b08d534e to your computer and use it in GitHub Desktop.
Save DanielLoth/a272a601e38c9c6f2c002956b08d534e to your computer and use it in GitHub Desktop.
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;
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;
create function dbo.GetObjectName_fn (
@ObjectId int
)
returns nvarchar(257)
as
begin
return object_schema_name(@ObjectId) + '.' + object_name(@ObjectId);
end
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
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
@Derek-Asirvadem
Copy link

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

@DanielLoth
Copy link
Author

DanielLoth commented Jul 4, 2021

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

@Derek-Asirvadem
Copy link

Derek-Asirvadem commented Jul 4, 2021

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

@DanielLoth
Copy link
Author

Hi Derek,

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

Cheers,
Dan

@DanielLoth
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment