MySQL Transactions — The Right Way

DROP PROCEDURE IF EXISTS mytestproc;
 
DELIMITER $$
 
CREATE PROCEDURE mytestproc()
BEGIN
    START TRANSACTION;
    SAVEPOINT x;
    
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION    
        BEGIN
            ROLLBACK TO SAVEPOINT x; -- Rollback on exception    
            RESIGNAL; -- Rethrow the error (if you want to raise it upstream, i.e., in .NET/java code)
        END;
        
        #SAVEPOINT x; #named savepoint      
        
        SELECT 1;
    #Other statements here
    END;
    
    COMMIT;
    # Clean Up
    RELEASE SAVEPOINT x;
END$$    
 
DELIMITER ;
 
CALL mytestproc();

MySQL How-To: Which SQL statements are running right now?

Here’s the basics:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

Now these results can be a bit daunting, so you might want to refine your lookup — especially if you have at least a vague idea of what your expecting to find:

SELECT * 
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE INFO REGEXP 'FROM too_many_rows_why_did_i_do_this';

As you may know, the output is the same as executing the following:

SHOW FULL PROCESSLIST;

However, unlike the above, you can run full SELECT queries against it.

Note: applies only to MySQL versions >= 5.1.17