none
Transaccion en Procedimiento Almacenado RRS feed

  • Pregunta

  • Que tal a todos, tengo la siguiente inquietud.

    Tengo varios bloques If para crear tablas en otra base de datos a partir de un servidor vinculado, los bloques If evalua si la cantidad de plantas son igual a 20, entonces procede a borrar la tabla actual y crear la tabla con los registros nuevos si, no entonces alertar para notificar al DBA. Pero lo que quiero es tener un poco mas de control ya que quiero llamar desde una aplicacion estas instrucciones me imagino que lo ideal seria un SP, y una transacción por cada bloque If si hay un error entonces que siga con la siguiente instruccion al finalizar la transacción hacer el commit.

    El proceso por cada tabla puede tardar de 5 a 20 min, entonces las dudas son:
    Como puedo agregar la transacción dentro de un Sp, por cada tabla?
    En su opinion es correcto el planteamiento que pretendo realizar?

    IF (SELECT COUNT(DISTINCT PLANTAS) as 'QtyPlants' FROM [GLOBAL_CON]..[RPTDBA].[T5W])=20
    	BEGIN
    	INSERT INTO [GSD_Sys].[dbo].[T5W]
    	SELECT * FROM [GLOBAL_CON]..[RPTDBA].[T5W]
    	END
    	ELSE BEGIN
    	print 'No es igual a 20, notificar y ejecutar siguiente If'
    END
    
    IF (SELECT COUNT(DISTINCT PLANTAS) as 'QtyPlants' FROM [GLOBAL_CON]..[RPTDBA].[T6X])=20
    	BEGIN
    	INSERT INTO [GSD_Sys].[dbo].[T6X]
    	SELECT * FROM [GLOBAL_CON]..[RPTDBA].[T6X]
    	END
    	ELSE BEGIN
    	print 'No es igual a 20, notificar y ejecutar siguiente If'
    END
    
    IF (SELECT COUNT(DISTINCT PLANTAS) as 'QtyPlants' FROM [GLOBAL_CON]..[RPTDBA].[T6Y])=20
    	BEGIN
    	INSERT INTO [GSD_Sys].[dbo].[T6Y]
    	SELECT * FROM [GLOBAL_CON]..[RPTDBA].[T6Y]
    	END
    	ELSE BEGIN
    	print 'No es igual a 20, notificar y ejecutar siguiente If'
    END
    
    IF (SELECT COUNT(DISTINCT PLANTAS) as 'QtyPlants' FROM [GLOBAL_CON]..[RPTDBA].[T6Z])=20
    	BEGIN
    	INSERT INTO [GSD_Sys].[dbo].[T6Z]
    	SELECT * FROM [GLOBAL_CON]..[RPTDBA].[T6Z]
    	END
    	ELSE BEGIN
    	print 'No es igual a 20, notificar y ejecutar siguiente If'
    END
    
    IF (SELECT COUNT(DISTINCT PLANTAS) as 'QtyPlants' FROM [GLOBAL_CON]..[RPTDBA].[T7A])=20
    	BEGIN
    	INSERT INTO [GSD_Sys].[dbo].[T7A]
    	SELECT * FROM [GLOBAL_CON]..[RPTDBA].[T7A]
    	END
    	ELSE BEGIN
    	print 'No es igual a 20, notificar y ejecutar siguiente If'
    END
    
    IF (SELECT COUNT(DISTINCT PLANTAS) as 'QtyPlants' FROM [GLOBAL_CON]..[RPTDBA].[T7B])=20
    	BEGIN
    	INSERT INTO [GSD_Sys].[dbo].[T7B]
    	SELECT * FROM [GLOBAL_CON]..[RPTDBA].[T7B]
    	END
    	ELSE BEGIN
    	print 'Notificar con el administrador Dba'
    END

    De antemano gracias.

    DS


    Aprendiz de todos maestro de nadie. Saludos desde Cd. Juarez Chihuahua Mexico. DS.

    miércoles, 24 de marzo de 2021 16:08

Respuestas

  • Hola Dany Solis:

    Puedes hacer transacciones independientes dentro de cada if, en un procedure.

    Create or alter procedure dbo.Trancounts 
    As
    Begin
    	Begin tran;
    	Declare @num int = 5;
    	Declare @errorNumberDiv int = 0;
    	If ( @num>4)
    	begin
    		begin try 
    			begin tran tran1
    				select @@TRANCOUNT as transactionNumber, 1 as [if], @num as valorVariable
    
    				Set @num -=1;
    			commit tran tran1
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    	If ( @num>3)
    	begin
    		begin try 
    			begin tran tran2
    				select @@TRANCOUNT as transactionNumber , 2 as [if], @num as valorVariable
    				Set @num -=1;
    			commit tran tran2
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    	If ( @num>3)
    	begin
    		begin try 
    			begin tran tran3
    			-- forzamos un error.
    				select @num/@errorNumberDiv as transactionNumber , 3 as [if], @num as valorVariable
    				-- este no se ejecuta.
    				Set @num -=1;
    				
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    	If ( @num>2)
    	begin
    		begin try 
    			begin tran tran4
    				select @@TRANCOUNT as transactionNumber, 4 as [if], @num as valorVariable
    				Set @num -=1;
    			Commit tran tran4;	
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    		If ( @num>1)
    	begin
    		begin try 
    			begin tran tran5
    				select @@TRANCOUNT as transactionNumber, 5 as [if], @num as valorVariable
    				Set @num -=1;
    			Commit tran tran5;		
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    	commit tran;
    End
    go
    exec dbo.Trancounts

    Así que puedes confirmar unos si y otros no dentro del mismo procedure.

    • Marcado como respuesta Dany Solis jueves, 25 de marzo de 2021 14:29
    miércoles, 24 de marzo de 2021 16:41

Todas las respuestas

  • Hola Dany Solis:

    Puedes hacer transacciones independientes dentro de cada if, en un procedure.

    Create or alter procedure dbo.Trancounts 
    As
    Begin
    	Begin tran;
    	Declare @num int = 5;
    	Declare @errorNumberDiv int = 0;
    	If ( @num>4)
    	begin
    		begin try 
    			begin tran tran1
    				select @@TRANCOUNT as transactionNumber, 1 as [if], @num as valorVariable
    
    				Set @num -=1;
    			commit tran tran1
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    	If ( @num>3)
    	begin
    		begin try 
    			begin tran tran2
    				select @@TRANCOUNT as transactionNumber , 2 as [if], @num as valorVariable
    				Set @num -=1;
    			commit tran tran2
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    	If ( @num>3)
    	begin
    		begin try 
    			begin tran tran3
    			-- forzamos un error.
    				select @num/@errorNumberDiv as transactionNumber , 3 as [if], @num as valorVariable
    				-- este no se ejecuta.
    				Set @num -=1;
    				
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    	If ( @num>2)
    	begin
    		begin try 
    			begin tran tran4
    				select @@TRANCOUNT as transactionNumber, 4 as [if], @num as valorVariable
    				Set @num -=1;
    			Commit tran tran4;	
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    		If ( @num>1)
    	begin
    		begin try 
    			begin tran tran5
    				select @@TRANCOUNT as transactionNumber, 5 as [if], @num as valorVariable
    				Set @num -=1;
    			Commit tran tran5;		
    		end try
    		begin catch
    			if (@@TRANCOUNT > 0)
    				rollback tran;
    		end catch
    	end
    	commit tran;
    End
    go
    exec dbo.Trancounts

    Así que puedes confirmar unos si y otros no dentro del mismo procedure.

    • Marcado como respuesta Dany Solis jueves, 25 de marzo de 2021 14:29
    miércoles, 24 de marzo de 2021 16:41
  • [...] una transacción por cada bloque If [...]

    No es necesario hacer una transacción si la vas a meter dentro del IF. Cada uno de tus bloques IF contiene una única sentencia. Una sentencia independiente de forma predeterminada se ejecuta dentro de una transacción. Por lo tanto, no necesitas iniciar expresamente una transacción; solo sería necesario si tuvieses que agrupar varias sentencias dentro de la transacción.

    En otras palabras, donde tienes

    Insert into tabla... select ... from ...

    si se produce un error durante la inserción se deshace automáticamente todo lo que se haya insertado hasta el momento, porque la totalidad de la sentencia Insert se hace en una transacción automáticamente

    Otra cosa es que quieras meter el COUNT que hay en el IF dentro de la transacción, para garantizar que no se producen cambios en la tabla entre medias del Count y el Insert. Pero esto no va a funcionar bien con un servidor vinculado, especialmente teniendo en cuenta que no es SQL Server, o al menos no sin una cantidad tremenda de trabajo a nivel de administración de sistemas. Tendría que ser una transacción distribuida, y eso requeriría que todos los servidores implicados implementen el protocolo necesario en una versión compatible, que ejecutes el DTC (coordinador de transacciones distribuidas) en todos los servidores implicados, y que estén abiertos en los cortafuegos los puertos usados por el DTC.

    miércoles, 24 de marzo de 2021 17:20
  • No me ha quedado del todo claro, meto todo en una sola transaccion o lo hago por cada sentencia if, algo como esto claro que serian seis sentencias una por cada tabla?

    ALTER PROCEDURE DBO.TRANCOUNTS
    AS
    BEGIN
    	BEGIN TRAN
    		BEGIN TRY
    			--La sentencia Ifm es porque si no estan las 20 plantas existentes, los datos estan incompletos, es la misma sentencia If en cada tabla.
    			IF (SELECT COUNT(DISTINCT PLANTAS) as 'QtyPlants' FROM [GLOBAL_CON]..[RPTDBA].[T5W])=20
    	--Borro los datos actuales e inserto los nuevos, en la primer entrada tenia la instruccion para que creara la tabla completa, pero que seria lo mas optimo en rendimiento y rapidez?
    	TRUNCATE TABLE PRUEBAII
    	--Inserto los registros a la tabla si la sentencia If es verdadera
    	INSERT INTO [GSD_Sys].[dbo].[PRUEBAII]
    	SELECT * FROM [GLOBAL_CONEX]..[RPTDBA].[T5W]
    	--Finalizo la transacción con exito
    	COMMIT TRAN;
    	END TRY
    	--Si ocurre un error, regreso todo tal como estaba
    	BEGIN CATCH
    		ROLLBACK TRAN;
    	END CATCH
    END
    EXEC DBO.TRANCOUNTS

    Esta transaccion la tendria que hacer por cada tabla?
    Los datos los he de actualizar una vez al mes, cual seria la recomendacion hacer un Drop Table de las tablas y crearlas de nuevo o tal como lo hago en el Sp un truncate e insercción de registros?

    Saludos

    DS


    Aprendiz de todos maestro de nadie. Saludos desde Cd. Juarez Chihuahua Mexico. DS.

    miércoles, 24 de marzo de 2021 20:19
  • Un par de problemas con este código:

    - La sentencia TRUNCATE TABLE no es transaccional. No puede participar en la transacción. Si quieres que el borrado sea transaccional, es decir que en caso de rollback se deshaga el borrado, puedes cambiar el "Truncate Table laTabla" por "Delete from laTabla". Esto sí que es transaccional, pero ojo, es considerablemente más lento que el Truncate.

    - La tabla enlazada mediante el nombre de cuatro partes no participará en la transacción. En principio no es grave puesto que solo la lees y no modificas nada en ella, pero si esperabas que quedase bloqueada para que no se modifique durante la duración de la transacción, no conseguirás que quede bloqueada solo con este código.

    miércoles, 24 de marzo de 2021 21:32
  • En tu experiencia, si creo la tabla cada ves que ejecuto el SP, es decir DROP TABLE e inmediatamente despues 

    INSERT INTO [GSD_Sys].[dbo].[T5W]
    	SELECT * FROM [GLOBAL_CON]..[RPTDBA].[T5W]

    Seria mas rapido que el DELETE?

    DS


    Aprendiz de todos maestro de nadie. Saludos desde Cd. Juarez Chihuahua Mexico. DS.

    miércoles, 24 de marzo de 2021 21:48
  • Hola Dany Solis:

    Si haces un drop table, no te funcionará Insert into Select *, ya que drop table, borra la estructura y no puedes insertar en ella.

    Y si te la has cargado, y falla algo, te la has cargado. No hay un vuelta atrás.

    Si quieres tener la posibilidad de rollback, tienes que hacer delete.

    jueves, 25 de marzo de 2021 6:16
  • DROP TABLE e inmediatamente despues INSERT INTO ...

    Bueno, inmediatamente después no puedes poner el Insert Into. Primero tendrás que volver a crear la tabla.

    Pero sí, borrar y crear la tabla es una operación rápida. Es más rápido que el Delete, si la tabla tiene muchos registros.

    Esto presume que no hay ninguna otra tabla que tenga Foreign Keys apuntando a la que borras, de ser así no funcionaría.

    EDITADO: Estoy seguro de que cuando comencé a escribir esta respuesta, todavía no existía en el foro la respuesta que ha escrito Javi más arriba.
    jueves, 25 de marzo de 2021 6:19
  • Gracias por las aclaraciones y la ayuda brindada.

    Esta claro ahora, con su ayuda pude lograr lo que pretendia.

    Saludos

    DS


    Aprendiz de todos maestro de nadie. Saludos desde Cd. Juarez Chihuahua Mexico. DS.

    jueves, 25 de marzo de 2021 14:34