Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Oct 2008
    Posts
    255
    Thanks
    113
    Thanked 0 Times in 0 Posts

    Committing a MySQL Connection

    What exactly does that do? For example:

    PHP Code:
    $mysqli->commit(); 
    What purpose does it serve?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    If you're not using autocommit and you're using a transactional type table (INNODB for example, not MYISAM), you can actually rollback you're changes in a database. Once commit is called, the temporary records that where the *old* records are removed.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Regular Coder
    Join Date
    Oct 2008
    Posts
    255
    Thanks
    113
    Thanked 0 Times in 0 Posts
    You mean if I edit a table and don't commit, the edit might not happen?

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Sort of. If it works like oracle, the transaction is valid for the current connection, and no other connection can view the changes until its been committed. This gives you the opportunity to rollback if you desire. Should you're script terminate abnormally, I'd suspect that mysql works just like oracle and rolls the transactions back.

    For the above reason, transactions are very very handy (and also very very dangerous). Consider removing money from one bank account and adding it to another. If you commit you're transaction after adding subtracting from the first account and before adding to the other account and the engine dies, you will have one account short money and one account without being added to. Rerunning the query results in a double subtraction from the originating account with only a single addition to another account.

    BTW, in MySQL the autocommit is on by default if I'm not mistaken. You have to explicitly disable it to start transactional processing. You can check with a SELECT @@autocommit. If its 0, its disabled.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by Fou-Lu View Post
    For the above reason, transactions are very very handy (and also very very dangerous).
    They can also be a bit of an annoyance on occasion too. The PostgreSQL documentation has some good info on transactions. Might be worth having a look on their site.

  • #6
    Regular Coder
    Join Date
    Oct 2008
    Posts
    255
    Thanks
    113
    Thanked 0 Times in 0 Posts
    If you set MySQLi Autocommit as soon as you open the connection, would that take care of all queries? Do you need to commit SELECT queries?
    Last edited by Joseph Witchard; 01-21-2010 at 05:53 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •