SQL Server stored procedure Isolate transaction has been cancelled-CodePudding

I have a stored procedure to update tables. I get this error when the stored procedure is executed from a different PC at the same time.

System.Exception: System.Data.SqlClient.SqlException (0x80131904): La transaction d'isolement d'instantané a été abandonnée en raison d'un conflit de mise à jour. Vous ne pouvez pas utiliser l'isolement d'instantané pour accéder à la table 'dbo.Journee' directement ou indirectement dans la base de données 'PlanningGestion' afin de mettre à jour, de supprimer ou d'insérer la ligne modifiée ou supprimée par une autre transaction. Réexécutez la transaction ou changez le niveau d'isolement pour l'instruction de mise à jour/suppression

Is it possible to specify that the system needs to wait until the end of the first stored procedure execution then start the new one? As FIFO.

Or do I have to use a different way to do that?

PS: my stored procedure updates from subquery

update Journee 
set UVGlobalJournee = (select 
                           case
                               when max(E.UVGlobale) is null
                                   then 0 
                                   else max(E.UVGlobale) 
                           end
                       from 
                           Examen E 
                       inner join 
                           IT_Journee_Examen IT on E.IdExamen = IT.IDEXAMEN 
                       where 
                           Journee.IdJournee = IT.IDJOURNEE) 
where Test = '1'  
  and Journee.IdJournee = @IdJournee

CodePudding user response：

If you are using Read Committed Snapshot (READ_COMMITTED_SNAPSHOT database option), setting TRANSACTION ISOLATION LEVEL has no effect.

Instead add the READCOMMITTEDLOCK table hint to the table being updated.

Note that you can still get read skew, unless you use READCOMMITTEDLOCK in all the other tables as well

create or alter procedure name_of_your_procedure AS
begin

update Journee WITH (READCOMMITTEDLOCK)
set UVGlobalJournee = (select 
                           case
                               when max(E.UVGlobale) is null
                                   then 0 
                                   else max(E.UVGlobale) 
                           end
                       from 
                           Examen E 
                       inner join 
                           IT_Journee_Examen IT on E.IdExamen = IT.IDEXAMEN 
                       where 
                           Journee.IdJournee = IT.IDJOURNEE) 
where Test = '1'  
  and Journee.IdJournee = @IdJournee

end

CodePudding user response：

You can use an explicit transaction, with read committed isolation level on you procedure. Assuming the procedure code is only what is on the question, it would be something like:

create or alter procedure name_of_your_procedure AS
begin
set transaction isolation level read committed
begin transaction
update Journee 
set UVGlobalJournee = (select 
                           case
                               when max(E.UVGlobale) is null
                                   then 0 
                                   else max(E.UVGlobale) 
                           end
                       from 
                           Examen E 
                       inner join 
                           IT_Journee_Examen IT on E.IdExamen = IT.IDEXAMEN 
                       where 
                           Journee.IdJournee = IT.IDJOURNEE) 
where Test = '1'  
  and Journee.IdJournee = @IdJournee
commit transaction
end

If, running on a database with RCSI enabled, you change beginning of this procedure to:

set transaction isolation level snapshot

and execute it concurrently, you will get the same error on the question. With read committed, I couldn't get that error.