Making sure your changes won't be overwritten by someone else


Goal

Sometimes different users change the same record at the same time. The last one who saves the records, overwrites the earlier changes by the other user. And it is likely both users won't even notice!

If this scenario is a daily practice, you probably need an optimistic locking mechanism in your application. Optimistic locking is when you check if the record was updated by someone else before you commit the transaction. (Pessimistic locking is when you take an exclusive lock so that no one else can start modifying the record)

In this topic a solution for optimistic locking is provided.

Solution

First of all, by dynamic model code, every table gets an extra column, update_counter. This column is update after the commit of an update, or, when in the meantime the counter is incremented by another process, it will throw a message.

Dynamic model create_column_update_counter

This dynamic model code adds column to the tables. With the tag NO_UPDATE_COUNTER you can exclude a table from this mechanism.

Control procedure increment_update_counter_or_error

This control procedure increases column update_counter, or gives an error when the counter was increased by another process or user.

Template increment_update_counter_or_error

This template increases column update_counter, or gives an error when the counter was increased by another process or user.

In the attachment you will find the code of this solution.


2 replies

Userlevel 4
Badge +3

You could expand this a little more and warn the user earlier by testing this value during the execution of the default procedure and raise an error there. This way you would, in most cases, not even do the update and fire the update trigger rolling back the transaction.

Userlevel 3
Badge +10

I have set up a similar solution with less steps and control procedures.

This solution requires the user to have implemented the trace columns  

It uses the column update_date_time to indicate whether the record has been changed. 

This solution uses a table tag “USE_UPDATE_DATE_TIME_CHECK”. If you assign this tag to a table. The check to determine whether a record has been altered in the meantime will be automatically created for that table.

In order to set this up you need to create a dynamic model procedure with the following code for generating an index on the update_date_time column:

------------------------------------------------
-- add index
------------------------------------------------
insert into indx
(
project_id
,project_vrs_id
,indx_id
,tab_id
,primary_key
,foreign_key
,type_of_indx
,unique_indx
,ignore_null
,indx_description
,no_of_col
,generated
)
select
t.project_id -- project_id
,t.project_vrs_id -- project_vrs_id
,'index_' + t.tab_id + '_update_date_time_check' -- indx_id
,t.tab_id -- tab_id
,0 -- primary_key
,0 -- foreign_key
,1 -- type_of_indx
,0 -- unique_indx
,0 -- ignore_null
,'Index for checking updated' -- indx_description
,0 -- no_of_col
,1 -- generated
from tab t
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and exists(
select -- has been tagged
1
from tab_tag tt
where tt.project_id = t.project_id
and tt.project_vrs_id = t.project_vrs_id
and tt.tab_id = t.tab_id
and tt.tag_id = 'USE_UPDATE_DATE_TIME_CHECK'
)
;

------------------------------------------------
-- add index columns
------------------------------------------------
insert into indx_col
(
project_id
,project_vrs_id
,indx_id
,tab_id
,col_id
,sort_order
,included
,order_no
,abs_order_no
)
select
t.project_id -- project_id
,t.project_vrs_id -- project_vrs_id
,'index_' + t.tab_id + '_update_date_time_check' -- indx_id
,c.tab_id -- tab_id
,c.col_id -- col_id
,0 -- sort_order
,case c.col_id -- include
when 'update_date_time'
then 1
else 0
end -- include
,ROW_NUMBER() over( -- order_no
partition by t.tab_id
order by c.abs_order_no
) * 10 -- order_no
,ROW_NUMBER() over( -- abs_order_no
partition by t.tab_id
order by c.abs_order_no
) -- abs_order_no
from tab t
join col c
on c.project_id = t.project_id
and c.project_vrs_id = t.project_vrs_id
and c.tab_id = t.tab_id
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and ( c.primary_key = 1 -- is the primary key
or c.col_id = 'update_date_time' -- is the trace column
)
and exists(
select -- has been tagged
1
from tab_tag tt
where tt.project_id = t.project_id
and tt.project_vrs_id = t.project_vrs_id
and tt.tab_id = t.tab_id
and tt.tag_id = 'USE_UPDATE_DATE_TIME_CHECK'
)
;

Next set up a dynamic control procedure of the code group trigger. Provide this control procedure with the following SQL code:

---------------------------------------------
-- program object item
---------------------------------------------
insert into prog_object_item
(
project_id
,project_vrs_id
,prog_object_id
,prog_object_item_id
,order_no
,control_proc_id
,template_id
)
select
po.project_id -- project_id
,po.project_vrs_id -- project_vrs_id
,po.prog_object_id -- prog_object_id
,cpt.template_id -- prog_object_item_id
,cpt.order_no -- order_no
,cpt.control_proc_id -- control_proc_id
,cpt.template_id -- template_id
from tab t
join prog_object po
on po.project_id = t.project_id
and po.project_vrs_id = t.project_vrs_id
and po.tab_id = t.tab_id
join control_proc_template cpt
on cpt.project_id = po.project_id
and cpt.project_vrs_id = po.project_vrs_id
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and po.prog_object_id = t.tab_id + '_tu'
and cpt.control_proc_id = @control_proc_id
and cpt.template_id = cpt.control_proc_id
and exists(
select
1
from tab_tag tt
where tt.project_id = t.project_id
and tt.project_vrs_id = t.project_vrs_id
and tt.tab_id = t.tab_id
and tt.tag_id = 'USE_UPDATE_DATE_TIME_CHECK'
)
;

---------------------------------------------
-- program object item parameter
---------------------------------------------
insert into prog_object_item_parmtr
(
project_id
,project_vrs_id
,prog_object_id
,prog_object_item_id
,parmtr_id
,parmtr_value
,order_no
,no_line_when_empty
)
----------------------
-- table
----------------------
select
poi.project_id -- project_id
,poi.project_vrs_id -- project_vrs_id
,poi.prog_object_id -- prog_object_id
,poi.prog_object_item_id -- prog_object_item_id
,'table' -- parmtr_id
,t.tab_id -- parmtr_value
,10 -- order_no
,0 -- no_line_when_empty
from tab t
join control_proc_template cpt
on cpt.project_id = t.project_id
and cpt.project_vrs_id = t.project_vrs_id
join prog_object_item poi
on poi.project_id = t.project_id
and poi.project_vrs_id = t.project_vrs_id
and poi.control_proc_id = cpt.control_proc_id
and poi.template_id = cpt.template_id
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and poi.prog_object_id = t.tab_id + '_tu'
and cpt.control_proc_id = @control_proc_id
and cpt.template_id = cpt.control_proc_id
and exists(
select
1
from tab_tag tt
where tt.project_id = t.project_id
and tt.project_vrs_id = t.project_vrs_id
and tt.tab_id = t.tab_id
and tt.tag_id = 'USE_UPDATE_DATE_TIME_CHECK'
)

union all
----------------------
-- pk
----------------------
select
poi.project_id -- project_id
,poi.project_vrs_id -- project_vrs_id
,poi.prog_object_id -- prog_object_id
,poi.prog_object_item_id -- prog_object_item_id
,'pk' -- parmtr_id
,c.col_id -- parmtr_value
,40 + c.abs_order_no -- order_no
,1 -- no_line_when_empty
from tab t
join col c
on c.project_id = t.project_id
and c.project_vrs_id = t.project_vrs_id
and c.tab_id = t.tab_id
join control_proc_template cpt
on cpt.project_id = t.project_id
and cpt.project_vrs_id = t.project_vrs_id
join prog_object_item poi
on poi.project_id = t.project_id
and poi.project_vrs_id = t.project_vrs_id
and poi.control_proc_id = cpt.control_proc_id
and poi.template_id = cpt.template_id
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and poi.prog_object_id = t.tab_id + '_tu'
and cpt.control_proc_id = @control_proc_id
and cpt.template_id = cpt.control_proc_id
and c.primary_key = 1 -- is ook een PK kolom
and exists(
select
1
from tab_tag tt
where tt.project_id = t.project_id
and tt.project_vrs_id = t.project_vrs_id
and tt.tab_id = t.tab_id
and tt.tag_id = 'USE_UPDATE_DATE_TIME_CHECK'
)
;

And as the final step include the following template in this control procedure:

----------------------------------------------
-- record has been altered
----------------------------------------------
if exists(
select
1
from inserted i
where exists(
select
1
from deleted d
where ( d.update_date_time <> i.update_date_time -- has been updated
or ( d.update_date_time is not null
and i.update_date_time is null
)
)
and d.[pk] = i.[pk] -- the same PK
)
)
begin
----------------------------------------------
-- display error
----------------------------------------------
exec dbo.tsf_send_message 'record_has_been_altered', 0, 1;
rollback tran;
return;
end;

 

Reply