use master 
go
create database banktest;
go
use banktest;
go

create table dbo.account
 (reknr int primary key,
  beginsaldo dec(18) not null default (0),
  laatst_verwerkte_mutatie int not null default (0), -- in beginsaldo
  minimaal_saldo dec(18) not null default (0));
go

create table dbo.overboeking
 (id int identity not null primary key,
  debreknr int not null references dbo.account (reknr),
  credreknr int not null references dbo.account (reknr),
  bedrag dec(18) not null,
  vanafdatum date not null default (getUTCdate()),
  uitgevoerd bit not null default (0));
go
-- index om de nog uit te voeren overboekingen te vinden
  -- zeker als dit een kleine subset is
create index idx_overboeking_niet_uitgevoerd on dbo.overboeking (id) 
  include (debreknr, credreknr, bedrag, vanafdatum, uitgevoerd)
  where uitgevoerd = 0
go       

create table dbo.accountmutatie
 (id int identity not null primary key,
  overboeking int not null references overboeking (id),
  reknr int not null references account (reknr),
  bedrag dec(18) not null,
  datumtijd datetime2 not null default (getUTCdate()));
go
-- index om het saldo te bepalen
 -- deze index fragmenteert er er worden range-scans gedaan; zo nu en dan defragmenteren dus
create index idx_accountmutatie_reknr on dbo.accountmutatie (reknr, id) include (bedrag);
go

-- testdata
-- 10K rekeningen
with n0 as (select 1 as c union all select 1)              --      2 rows
    ,n1 as (select 1 as c from n0 as a cross join n0 as b) --      4 rows
    ,n2 as (select 1 as c from n1 as a cross join n1 as b) --     16 rows
    ,n3 as (select 1 as c from n2 as a cross join n2 as b) --    256 rows
    ,n4 as (select 1 as c from n3 as a cross join n3 as b) --  65536 rows
    ,nums as (select row_number() over(order by (select null)) as n from n4)
insert dbo.account (reknr, beginsaldo, minimaal_saldo) 
  select n, n%10*100, -n%10*100
    from nums
    where n <= 10000;
-- 1M uit te voeren overboekingen
with n0 as (select 1 as c union all select 1)              --      2 rows
    ,n1 as (select 1 as c from n0 as a cross join n0 as b) --      4 rows
    ,n2 as (select 1 as c from n1 as a cross join n1 as b) --     16 rows
    ,n3 as (select 1 as c from n2 as a cross join n2 as b) --    256 rows
    ,n4 as (select 1 as c from n3 as a cross join n3 as b) --  65536 rows
    ,n5 as (select 1 as c from n4 as a cross join n2 as b) --1048576 rows
    ,nums as (select row_number() over(order by (select null)) as n from n5)
insert dbo.overboeking (debreknr, credreknr, bedrag) 
  select (n%10000)+1, ((n+1)%10000)+1, n%100
    from nums
    where n <= 1000000;
go

-- inline table valued function voor actueel saldo en minimaal_saldo
create or alter function dbo.tvf_reksaldo (@reknr int) returns table
as
return (
  select isnull(sum(AM.bedrag), 0)+A.beginsaldo saldo, A.minimaal_saldo 
    from dbo.account A
      left join dbo.accountmutatie AM with (updlock) 
        on AM.reknr = A.reknr 
       and AM.id > A.laatst_verwerkte_mutatie
    where A.reknr = @reknr
    group by A.reknr, A.beginsaldo, A.minimaal_saldo, A.laatst_verwerkte_mutatie
);
go

-- procedure voor het verwerken van een enkele overboeking
create or alter proc dbo.usp_overboeking 
  @id int
 ,@debreknr int
 ,@credreknr int
 ,@bedrag dec(18)
as
begin try;
  begin tran;
    -- saldocheck
    if exists 
     (select * 
        from dbo.tvf_reksaldo(@debreknr) 
        where saldo - @bedrag >= minimaal_saldo)
    begin; -- voldoende saldo
      -- bijboeken
        insert dbo.accountmutatie (reknr, bedrag, overboeking) 
          values (@credreknr, @bedrag, @id);
      -- afboeken
        insert dbo.accountmutatie (reknr, bedrag, overboeking) 
          values (@debreknr, -@bedrag, @id);
      -- statusupdate
        update dbo.overboeking
          set uitgevoerd = 1
          where id = @id;
    end;
    else 
    begin; -- onvoldoende saldo, stel verwerking 1 dag uit
      update dbo.overboeking
        set vanafdatum = dateadd(dd, 1, getUTCdate())
        where id = @id;
    end;
  commit tran;
end try
begin catch
  rollback tran;
end catch;
go

-- verwerk batch van overboekingen 
create or alter proc verwerk_overboekingen_serieel @aantal int 
as
set nocount on;
set transaction isolation level serializable;
declare @id int
       ,@debreknr int
       ,@credreknr int
       ,@bedrag dec(18);
declare c_overboeking cursor forward_only for 
  select top (@aantal) id, debreknr, credreknr, bedrag
    from dbo.overboeking
    where uitgevoerd = 0
      and vanafdatum <= getUTCdate()
    order by id;
open c_overboeking;
fetch next from c_overboeking into @id, @debreknr, @credreknr, @bedrag;
while @@fetch_status = 0
begin;
  exec dbo.usp_overboeking @id, @debreknr, @credreknr, @bedrag;
  fetch next from c_overboeking into @id, @debreknr, @credreknr, @bedrag;
end;
close c_overboeking;
deallocate c_overboeking;
go

-- test batch van 100000
exec verwerk_overboekingen_serieel @aantal = 100000

-- resultaten
select count(*) from dbo.accountmutatie 
select top 100 * from dbo.accountmutatie 
select top 100 A.reknr, A.minimaal_saldo, A.beginsaldo, S.saldo 
  from account A 
    cross apply dbo.tvf_reksaldo(A.reknr) S
select top 100 * -- uitgestelde overboekingen
  from overboeking 
  where vanafdatum > getUTCdate()