GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Innodb transaction problem

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Innodb transaction problem

    Hello,
    I read in the book High Performance MySQL, Second Edition that innodb transactions are reliable but its not the same case on my side. On my website i am sending three sql commands to mysql server.
    2 Inserts, and one update. Innodb skip the update sql (MOST of the time, NOT all the time) and commits the transaction.

    I am using set autocommit = 1 and here are mysql statements

    > Begin

    Q1: INSERT INTO files (`file_title`, 'file_date') VALUES('new file', NOW());
    > $id = mysql_insert_id();
    Q2: UPDATE user_info SET `total_media`=`total_media`+1, `file_size`=$file_size WHERE id=13;
    Q3: INSERT INTO keywords (file_id, keyword) VALUES ($id, 'keyword')

    if(mysql_query->query($q1) && mysql_query($q2) && mysql_query($q3)) {
    > COMMIT;
    }
    else {
    > ROLLBACK;
    }

    Above code is just for understanding and all numeric fields are Integers.

    Please help me on this.


    Thanks,
    Moazam

  • #2
    Quote:

    mysql_query->query

    Some dataabse class?

    Anyways, the error is in your code. InnoDB would not be deployed anywhere if it would not return an error if UPDATE fails.

    Note that COMMIT and ROLLBACK do not have any use if autocommit=1. Read http://dev.mysql.com/doc/refman/5.1/en/commit.html

    Comment


    • #3
      sorry about that typo, its mysql_query, there is no code error. As far as I know commit should work with begin or start transaction.

      Comment


      • #4
        moazam wrote on Wed, 12 August 2009 12:48

        sorry about that typo, its mysql_query, there is no code error. As far as I know commit should work with begin or start transaction.

        Ah, I did not see BEGIN. This should work indeed. I guess that there is something with your update sql query (WHERE clause matching no/wrong rows?)

        Comment


        • #5
          not possible, because i am getting that from database.

          and if this is the case it didn't work at all, the same statement fails for one user who has just previously executed this query.

          The interesting thing is if one query fails then it should not commit the record, why its inserting records to database? (all tables type is innodb).

          Comment

          Working...
          X