how to use row_count() with transAction?

Question

i have an stored procedure in mysql like below:

BEGIN
START TRANSACTION;  
    INSERT INTO tbl1 (v1,v2) VALUES (p1,p2);
    UPDATE tbl2 SET s1 = 1 WHERE s2 = s3;
    SELECT ROW_COUNT();
COMMIT
END

in this query even the transaction doesn't commit, the row_count will be 1. in fact my question is that how can i use row_count that it returns 0 if rollback occurred?

Answer 1

I ran into a similar problem recently, but I managed to solve it by selecting the row count inside of the transaction block and then returning it after the commit:

BEGIN
START TRANSACTION;  
    INSERT INTO tbl1 (v1,v2) VALUES (p1,p2);
    UPDATE tbl2 SET s1 = 1 WHERE s2 = s3;
    SET @rowCount = (SELECT ROW_COUNT());
COMMIT
SELECT @rowCount;
END

I don't know if this is the best way to solve it, but it seems to work for me :-)