Announcement

Announcement Module
Collapse
No announcement yet.

Committed but line not exist in the database.

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

  • Committed but line not exist in the database.

    Dear All,
    I have a very funny scenario. I have system where it reads the
    value from another db and updates my local db. I put the whole thing
    into a transaction it have been working very fine. Suddenly I find in
    my log file as below for two different cases. I notice the insert into
    product have been commited but when I check my databased this lines are
    not there. What whould have gone wrong possibly as there is no rollback
    and commited well rite.

    First Case

    090917 23:07:11 580 Connect root@localhost on mpcms4
    580 Query SHOW VARIABLES
    580 Query SHOW COLLATION
    580 Query SET character_set_results=NULL
    580 Init DB mpcms4
    580 Query SET SESSION TRANSACTION ISOLATION LEVEL
    REPEATABLE READ
    580 Query BEGIN
    090917 23:07:19 580 Query Insert into tblProduct Set
    productID
    =3220,productCategoryID='14',productSubCategoryID= '41',produ ctBrandID='1010',productGroupID='21',productStatus ID='1',pro ductCode='GIFT-ITEC-PS-STD-O',productName='SE
    PHONE
    STRING',productType='Accessory',rrpMarkUpType='Fix ed',rrpMar kUpAmount=0,rrpMarkUpFixed=10,rrpMarkUpPercentage= 0
    090917 23:07:20 580 Query Update tblProduct Set
    productCategoryID='14',productSubCategoryID='41',p roductBran dID='1010',productGroupID='21',productStatusID='1' ,productCo de='GIFT-ITEC-PS-STD-O',productName='ITECH
    PHONE
    STRING',productType='Accessory',rrpMarkUpType='Fix ed',rrpMar kUpAmount=0,rrpMarkUpFixed=10,rrpMarkUpPercentage= 0
    Where productID = 3220
    090917 23:07:21 580 Query Insert into tblProduct Set
    productID
    =3221,productCategoryID='14',productSubCategoryID= '25',produ ctBrandID='1010',productGroupID='21',productStatus ID='1',pro ductCode='GIFT-SE-PS-STD-O',productName='SE
    PHONE
    STRING',productType='Accessory',rrpMarkUpType='Fix ed',rrpMar kUpAmount=0,rrpMarkUpFixed=10,rrpMarkUpPercentage= 0
    580 Query COMMIT
    090917 23:07:22 580 Quit

    Second Case
    090917 23:37:33 631 Connect root@localhost on mpcms4
    631 Query SHOW VARIABLES
    631 Query SHOW COLLATION
    631 Query SET character_set_results=NULL
    631 Init DB mpcms4
    631 Query SET SESSION TRANSACTION ISOLATION LEVEL
    REPEATABLE READ
    631 Query BEGIN
    090917 23:38:04 631 Query Insert into tblProductGroup Set
    productGroupID
    =819,productGroupCode='BLB2',productGroupDescripti on='BLB2'
    090917 23:38:05 631 Query Insert into tblProduct Set
    productID
    =3222,productCategoryID='6',productSubCategoryID=' 1',product BrandID='277',productGroupID='819',productStatusID ='34',prod uctCode='ACC-BAT-NK-BLB2-MAX',productName='MOMAX
    BATTERY
    BLB2',productType='Accessory',rrpMarkUpType='Fixed ',rrpMarkU pAmount=0,rrpMarkUpFixed=29,rrpMarkUpPercentage=0
    090917 23:38:06 631 Query COMMIT
    090917 23:38:07 631 Quit

  • #2
    You are probably in repeatable-read mode and will not notice the new rows until you restart your transaction.

    Comment


    • #3
      Dear Xaprb,
      I dont get you why your said that I am in repetable-read mode. But why only this case n not other cases ? Quite confuse? What is the best manner to distributed transaction? Thank you.

      Comment


      • #4
        You're not using distributed transactions.

        What exactly is your error? It looks to me like you're inserting a row, committing it, and you don't see it in some other session, which is not an error. This is expected if the other session already has a transaction open. If you are confused by how this works, you need to read http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-mo del.html

        Comment


        • #5
          Dear Xaprb,
          Yes you are right I am not using distributed transaction the problem is that .net connector can not support distributed transaction. The problem there is no error only the row is not show on the other database.I have also attached my .err file for the pc which did not get updated. I dont really get what you trying to say here "It looks to me like you're inserting a row, committing it, and you don't see it in some other session, which is not an error. This is expected if the other session already has a transaction open." Yes what I am doing actually is committing one transaction one by one. First I commit my local transaction and then other transaction can there be any missing updates here ?

          Comment

          Working...
          X