logo
down
shadow

Rollback without START TRANSACTION statement is not working in MySQL Stored Procedure


Rollback without START TRANSACTION statement is not working in MySQL Stored Procedure

By : Alexander Scott
Date : November 14 2020, 04:48 PM
it helps some times I don't think you have actually started a "transaction". I am pretty sure that you need to implement one of the mechanisms listed here: https://dev.mysql.com/doc/refman/5.7/en/commit.html
code :


Share : facebook icon twitter icon
mysql transaction in stored procedure (locking/rollback)

mysql transaction in stored procedure (locking/rollback)


By : Ani
Date : March 29 2020, 07:55 AM
this will help
Does a transaction in a stored procedure do any locking to prevent others from updating tables?
code :
SELECT  *
FROM    table
WHERE   condition
FOR UPDATE
how to rollback the transaction of a stored Procedure calling in another procedure in db2

how to rollback the transaction of a stored Procedure calling in another procedure in db2


By : Murat Kartal
Date : March 29 2020, 07:55 AM
I hope this helps you . From your description it sounds like your application executes CALL B(). And the stored procedure B executes the CALL A() statement.
Unless either of the stored procedures include a COMMIT statement (which goes against best practices), then it is up to the calling application to determine if the CALL B() statement was successful or not, and decide whether to issue the COMMIT or ROLLBACK statement. Executing ROLLBACK should roll back any/all changes made by B and A.
MySQL Stored Procedure Rollback on first If-else statement, if second if-else statement fails

MySQL Stored Procedure Rollback on first If-else statement, if second if-else statement fails


By : Diombare Niang
Date : March 29 2020, 07:55 AM
Any of those help You should use the START TRANSACTION; and then on you else statement, use a ROLLBACK and then, at the end of your procedure, execute a commit. I did a simple sample of how it should look like:
code :
DELIMITER $$
CREATE PROCEDURE sp_example()
BEGIN   
    -- Starting the transaction
    START TRANSACTION;
    INSERT INTO `test`.`owner` (`name`) VALUES ('Insert Cubias');
IF 1 > 2 THEN
    INSERT INTO `test`.`owner` (`name`) VALUES ('McCubo');
ELSE
    SELECT 'Rollback transaction' as ErrorMessage;
    -- No records will be inserted
    ROLLBACK;
End if;

-- commit changes to database
COMMIT;
END;
$$
Will adding transaction and rollback to a stored procedure with calls to other stored procedures, work as intended?

Will adding transaction and rollback to a stored procedure with calls to other stored procedures, work as intended?


By : WhyNot
Date : March 29 2020, 07:55 AM
will help you Thank you @JeroenMostert for enlightening me... Keep up the good work. I have updated answer as per our discussion.
After a short read this is what i ended up.. I was a bit short on time.. Please let me know how to improve on it. I was not that keen on raise error part. I just wanted the data to be rolled back correctly.
code :
ALTER PROCEDURE [dbo].[TemplateBatchDynamicFromRawDataSetTemplateRelnInsert] 
    @FileData VARBINARY(max) = NULL, 
    @ActualFileName NVARCHAR(max) = NULL, 
    @UserID INT = NULL, 
    @ClientID INT = NULL, 
    @SelectedSheet NVARCHAR(max) = NULL, 
    @ACAFileNotes NVARCHAR(max) = NULL,
    @ClientTemplateNotes NVARCHAR(max) = NULL,
    @SourceReportFile NVARCHAR(500) = NULL,
    @IncludedInDataSet bit = 0,
    @TemplateID INT =0 
AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
    BEGIN TRANSACTION tr 
    BEGIN 
    DECLARE @retTemplateID INT
    EXEC [TemplateFileInsert] @FileData,@ActualFileName,@UserID,@ClientID,@SelectedSheet,@TemplateIDs = @TemplateID OUTPUT

    EXEC [TemplateDetailsInsert] @TemplateID,'',@SourceReportFile,@ClientTemplateNotes,@ACAFileNotes ,3,@IncludedInDataSet

    EXEC [dbo].[TemplateBatchDetailsByTemplateIDSelect] @TemplateID
    --SELECT @TemplateID TemplateID
    END
    COMMIT TRANSACTION tr 

END TRY
  BEGIN CATCH
   IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
   ; THROW
    END CATCH
ALTER PROCEDURE [dbo].[TemplateDetailsInsert] 
      @TemplateID INT =0 ,
      @TemplateName NVARCHAR(500) = NULL, 
      @SourceReportFile NVARCHAR(500) = NULL, 
      @ClientTemplateNotes NVARCHAR(max) = NULL, 
      @ACAFileNotes NVARCHAR(max) = NULL,
      @DataSourceID INT = NULL,
      @IncludedInDataSet bit = 0
    AS 

    SET XACT_ABORT, NOCOUNT ON
    DECLARE @trancount BIT = 0
        BEGIN TRY
        BEGIN TRANSACTION

        IF(@DataSourceID = 3)
        BEGIN
            EXEC TemplateNameCreator @TemplateID,@TemplateName out

            UPDATE [TemplateBatch] 
            SET 
            TemplateName = @TemplateName, 
            [SourceReportFile] = @SourceReportFile , 
            ClientTemplateNotes=@ClientTemplateNotes,
            ACAFileNotes = @ACAFileNotes, 
            DataSourceID = @DataSourceID,
            IncludedInDataSet = ISNULL(NULLIF(@IncludedInDataSet,0),IncludedInDataSet)
            WHERE TemplateID = @TemplateID
        END
        ELSE
        BEGIN
            UPDATE [TemplateBatch] SET TemplateName = @TemplateName, [SourceReportFile] = @SourceReportFile , ClientTemplateNotes=@ClientTemplateNotes,
            ACAFileNotes = @ACAFileNotes, DataSourceID = @DataSourceID
            WHERE TemplateID = @TemplateID
        END

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
    IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION
     ; THROW
    END CATCH
How to rollback transaction in a stored procedure?

How to rollback transaction in a stored procedure?


By : user3442049
Date : March 29 2020, 07:55 AM
I hope this helps you . try not to handle parent transactions inside a child procedure (exception when XACT_STATE() = -1). Handle the transaction at the "execution" level that started it.
if a procedure is executed in a parent transaction, then create a savepoint and rollback to it when needed. capture the execution result of the child procedure and handle the transaction at the parent level (if the parent is the one that begun the transaction).
code :
CREATE OR ALTER PROCEDURE [dbo].[ptest] @parrollback bit = 0
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT OFF

    DECLARE @trancount INT = @@TRANCOUNT;

    IF @trancount = 0
    BEGIN
        BEGIN TRANSACTION;
    END
    ELSE
    BEGIN
        SAVE TRANSACTION MySavepoint;
    END

    --do stuff.........


    --when it is time to commit or check for errors
    --assume @parrollback is the main control criterium

    IF @parrollback = 1
    BEGIN
        IF @trancount = 0
        BEGIN
            ROLLBACK TRANSACTION;
            RETURN(0);
        END
        ELSE
        BEGIN
            ROLLBACK TRANSACTION MySavePoint
            RETURN (1); 
        END
    END

    --just handle @parrollback <> 1, for completeness of the test
    IF @trancount = 0
    BEGIN
        COMMIT TRANSACTION;
    END

    RETURN (0);
END
GO

CREATE OR ALTER  PROCEDURE dbo.pcaller
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @ptestexec INT;
    BEGIN TRANSACTION

    select @@TRANCOUNT as '@@TRANCOUNT: before [ptest]'
    EXEC @ptestexec = dbo.ptest @parrollback = 1;

    IF @ptestexec = 1
    BEGIN
        ROLLBACK TRANSACTION
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION
    END


    --execute ptest, outside of a transaction
    EXEC @ptestexec = dbo.ptest @parrollback = 0;
    SELECT @@TRANCOUNT AS trancount1;

    EXEC @ptestexec = dbo.ptest @parrollback = 1;
    SELECT @@TRANCOUNT AS trancount2;

    --execute ptest, outside of a transaction
    BEGIN TRANSACTION;

    --ptest executed in a parent transaction
    EXEC @ptestexec = dbo.ptest @parrollback = 0;
    SELECT @@TRANCOUNT AS trancount3; --ptest does not affect the parent transactions

    COMMIT TRANSACTION --or rollback
END
GO

EXEC dbo.pcaller
GO
Related Posts Related Posts :
  • select the records with the highest amount
  • Changing Mysql golbal variable
  • How to create Effective Indexing on MySQL
  • `require': Incorrect MySQL client library version
  • How to save a FirstName of a Person efficiently without duplicate entries
  • Can I store my own sql query by mysql special function and execute stored query later?
  • MySQL comma separated field query
  • Why does the same exact query produce 2 different MySQL explain results?
  • Suppress warning output in bash
  • Doctrine "on update CURRENT_TIMESTAMP" annotation (Symfony2)
  • Designing "Friend" relationships across two mysql tables
  • Importing sql file using phpmyadmin in EasyPHP
  • Easy PHP and mysql ..php cannot see database on local pc (all works on server)
  • MySQL join optimization - bypassing filesort
  • How to Select record with the maximum value in a GROUP BY?
  • MySQL Innodb Full text Proximity Search Gives Horrible Performance
  • MySQL best approach for db normalising, relationships and foreign keys
  • How to update one table based on the count value of another table while matching ids?
  • MAMP Pro 5.1.1-terminates MySQL when quitting
  • SQL - Get date of most votes cast
  • "Fuzzier" search in mysql
  • Tracing mysqld Using SystemTap
  • SELECT + INSERT + Query Cache = MySQL lock up
  • SQL vs. NoSQL for medium complexity search systems
  • SQL Error when creating table
  • Is it possible to write a query to compare rows to other rows in same table?
  • Display all duplicate records based multiple columns
  • summing time column in mysql
  • InnoDB: Cannot open table
  • Why is IN() considered O(logN) operation?
  • Can a deadlock occur on commit?
  • Is there a fast way to do a '%phrase%' search in any DB?
  • Is using an IN over a huge data set a good idea?
  • How can I know if a table is already arranged in ascending order in MySQL?
  • MySQL - Designing database for a simple game
  • Why am I getting "Host '192.168.1.220' is not allowed to connect to this MySQL server"?
  • what type of password hash? possible mysql hash?
  • MySQL - Using a date range vs functions
  • Using IS NULL and COALESCE in OrderBy Doctrine Querybuilder
  • How to merge MySQL columns into one column if there are empty values in the table?
  • Cardinality Confusion
  • Renaming column multiple ids in SELECT
  • Group all from and to messages per user
  • How to handled the null values in WHERE IN condition in mysql
  • Is it okay to update primary key field to shift a record to the top?
  • Stop query through pdo
  • How to check whether particular value in field in MySQL exists
  • Execute a statement for every table in a database
  • insertion 74G data into mysql table cost more then 2 days, how to improve insert performance
  • Where can I see the differents "Hibernate Console Configurations" in Eclipse
  • MySql | relational database vs non relational database in terms of Performance
  • Exporting mysql database using mysqldump including procedures
  • Trigger MySQL not working
  • How do I update values for individual rows quickly?
  • MySQL join query with multiple where condition
  • MySQL stored procedure is not working
  • MySQL - Order By exact match at the end
  • Select max date or null date from same table MySQL
  • Best solution workaround for needing a mysql event on insert?
  • MySQL select several columns of several tables at the same time without using JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org