Database Reference
In-Depth Information
Listing 33-2.
Atomic blocks and transactions: Object creation
create table dbo.MOData
(
ID int not null
primary key nonclustered
hash with (bucket_count=10),
Value int null
)
with (memory_optimized=on, durability=schema_only);
insert into dbo.MOData(ID, Value)
values(1,1), (2,2)
go
create proc dbo.AtomicBlockDemo
(
@ID1 int not null
,@Value1 bigint not null
,@ID2 int
,@Value2 bigint
)
with
native_compilation
, schemabinding, execute as owner
as
begin atomic
with (transaction isolation level = snapshot, language=N'us_english')
update dbo.MOData set Value = @Value1 where ID = @ID1
if @ID2 is not null
update dbo.MOData set Value = @Value2 where ID = @ID2
end
At this point, the
dbo.MOData
table has two rows with values
(1,1)
and
(2,2)
. As a first step, let's start the
transaction and call a stored procedure twice, as shown in Listing 33-3.
Listing 33-3.
Atomic blocks and transactions: Calling stored procedure
begin tran
exec dbo.AtomicBlockDemo 1, -1, 2, -2
exec dbo.AtomicBlockDemo 1, 0, 2, 999999999999999
The first call of the stored procedure succeeds, while the second call triggers an arithmetic overflow error as
shown below:
Msg 8115, Level 16, State 0, Procedure AtomicBlockDemo, Line 49
Arithmetic overflow error converting bigint to data type int.
