why php function mysql_insert_id still autoincrement after rollback?

Question

Possible Duplicate:
MySQL AUTO_INCREMENT does not ROLLBACK

<?php
header("Content-Type:text/html; charset=utf-8") ;

$conn = mysql_connect('127.0.0.1', 'root', '123456') ;

mysql_select_db('test') ;

mysql_query('set autocommit=0') ;
mysql_query('start transaction') ;

try
{
    $sql = "insert into `user`(`username`, `password`) values('demo', 'demopassword')" ;
    echo $sql, '<br/>' ;
    $rs = mysql_query($sql) ;
    echo mysql_affected_rows(), '<br/>' ;
    if (mysql_affected_rows() > 0)
    {
        $uid = mysql_insert_id() ;
        echo $uid, '<br/>' ;
        throw new Exception('conn exception : ' . mysql_error()) ;
    }
    else
    {
        throw new Exception('conn exception : ' . mysql_error()) ;
    }
}
catch(Exception $e)
{
    echo $e->getMessage(), '<br/>' ;
    echo "rollback", "<br/>" ;
    mysql_query('rollback') or die(mysql_error()) ;
}

if (!$conn) 
{
    mysql_close($conn) ;
}
?>

sorry .. my english is so poor...

see above codes... when I fresh that page ... uid variable will auto increment .. why?

i hope u understand me... please help..thanks

score 1 · Answer 1 · answered Feb 16 '12 at 12:53

Why is this a problem? MySQL will have to generate the auto increment value to attempt the insert, so even if the transaction fails (after key generation), that key will still effectively be 'used'. You can run a clean up on the table to reclaim these used keys, if I remember correctly.

why php function mysql_insert_id still autoincrement after rollback?

1 Answers1