Talking about database--transaction (MySQL)

Transaction

Transaction is actually a set of database additions and deletions to the combination of operations, it can be understood that when you hit a person 1000 yuan, in the database will be two changes, one is that your money is reduced, the other is that person's money increased, both operations must be satisfied, or the problem is big, How to ensure that all two operations are performed, this requires MySQL transaction support.

MySQL is a support transaction, but first confirm that you are the InnoDB storage engine

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/82/FC/wKiom1dnyT_j-W02AACX2WhXRAY777.png "title=" Untitled. png "Width=" "height=" 144 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:800px;height:144px; "alt=" Wkiom1dnyt_ J-w02aacx2whxray777.png "/>

MySQL transaction is to maintain the integrity of the database, the batch of statements are either all executed, or all do not execute. Typically used to manage insert Delete and UPDATE statements.

Features of the transaction:

1. atomicity of a transaction: a set of transactions, either successful or withdrawn.

2, stability : There is illegal data (foreign key constraints and the like), transaction recall.

3. Isolation: The transaction runs independently. The result of one transaction affects other transactions, and then other transactions are recalled. The 100% isolation of a transaction requires a sacrifice of speed.

4, reliability: After the hardware and software crashes, the INNODB data table driver will use the log file to reconstruct the changes. Reliability and high speed cannot be combined, the INNODB_FLUSH_LOG_AT_TRX_COMMIT option determines when the transaction is saved to the log.

Here are some commands to operate in MySQL;

Set autoaction=0;//This command to cancel the automatic submission of MySQL. begin;//the transaction begins. SavePoint pointname;//set up a storage point, set up multiple can use rollback to return to one. Rollback pointname;//returns to a point. rollback;//returns to begin without using point. commit;//commit with commit if it can be submitted.

Use show variables like ' autocommit '; You can view the status of the current autoaction

Here are the tests I performed under MySQL:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/82/FD/wKioL1dn_dejDdw2AAC9cXdAiHU470.png "title=" Untitled. png "Width=" "height=" 361 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:600px;height:361px; "alt=" Wkiol1dn_ Dejddw2aac9cxdaihu470.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/82/FE/wKiom1dn_iOS3gguAACTSXRQPIM690.png "title=" Untitled. png "Width=" "height=" 337 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:600PX;HEIGHT:337PX; "alt=" Wkiom1dn_ Ios3gguaactsxrqpim690.png "/>

You can see that using rollback backwards back to the data before adding.

Using SavePoint, the reader can go down to test.

The following is a test of a transaction under C: (using the Connect C package)

The first time you test the data in the previous table, this

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/82/FD/wKioL1dn_wyDe4hQAAA3BOerXwA166.png "title=" Untitled. png "alt=" Wkiol1dn_wyde4hqaaa3boerxwa166.png "/>

My test code is as follows:

The my_sql.h here only gives the code for this part of the transaction (the code for all operations on the database is given in the post-build blog of the HTTP server manually).

My_sql.h    13     } 14     bool  httpsql::mysql_start ()  15     { 16          if (mysql_query (MySQL, "set autocommit=0") ==0)  17          { 18              cout<< "Start success" <<endl; 19              return true; 20          } 21         else { 22              return false; 23          } 24  25     } 26      bool httpsql::mysql_begin ()  27     { 28          if (mysql_query (MySQL, "BEGIN") ==0)  29         { 30              cout<< "Begin success" < <endl; 31             return  true; 32         } 33          else{ 34              return false; 35         } 36      } 37     bool httpsql::mysql_commit ()  38      { 39         if (mysql_query (MySQL, "COMMIT" ) ==0)  40         { 41              cout<< "Commit success" <<endl; 42              return true; 43          } 44         else{ 40          { 41              cout<< "Commit success" <<endl; 42              return true; 43          } 44         else{ 45              return false; 46         &nbSP;}  47     } 48     bool httpsql::mysql_rollback ()  49     { 50         if ( mysql_query (MySQL, "ROLLBACK") ==0)  51         { 52              cout<< "Rollback success" <<endl; 53             return  true; 54         } 55          else 56         { 57              return false; 58          } 59     }121      bool httpsqL::mysql_modify (STRING&NBSP;STR) 122     {123          bool ret=false;124         string _ Str= "update test_info set ";125     //  cout<<str< <endl;126         _str+=str.c_str ();127      //  cout<<_str<<endl;128          ret=mysql_op (_STR); 129         if (ret) 130          {131              return 1;132         }else{133              return -1;134          }  135      }                                                                                                                                     46,3-9        28%                                                                                                                                                   32,2-8         8%

Modify.cpp#include "My_sql.h" 104 int main () 105 {106     httpsql  Sql;107     if (!sql.mysql_start ()) 108     {109          return -1;110     }111      char buf1[1024];112     char buf2[1024];113      memset (Buf1, ' n ', sizeof (BUF1)) 114     memset (Buf2, ' n '), sizeof (BUF2 ); 115     strcpy (BUF1, "update test_info set name=\" wangmazi\ "  Where name=\ "zhangsan\"); 116     strcpy (Buf2, "Update test_info set  name=\ "wangmazi\"  where name\ "Lisi\" ");             //above this line of code I deliberately in the back of where name a equals sign 117     string str1 (BUF1); 118      string str2 (BUF2); 119     if (!sql.mysql_begin ()) 120      {121         return -1;122      }123     int ret1=sql.mysql_op (STR1);124      int ret2=sql.mysql_op (STR2);//Because STR2 is bound to perform a failed index return false.125     //ret1=false; 126     if (Ret1&&ret2) 127     {128          if (Sql.mysql_commit ()) 129          {130             cout<< "Modify  success "<<endl;131         }132          else{133              cout<< "ModifY failure "<<endl;134         }135    The   }else    //executes the else statement so that it returns to the shape before it is modified. 136     {137         if (Sql.mysql_ Rollback ()) 138         {139              cout<< "Modify failure roolback success" << endl;140         }141          else{142             cout<< "Boom!!! 1 "<<endl;143         }144     }145  146 147 }                                                                                                                                                                                                                                                                                                     131,2-8        Bottom                                                                                                                                                   116,2-5        89%

Test results

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/82/FD/wKioL1dn_wyDe4hQAAA3BOerXwA166.png "title=" Untitled. png "alt=" Wkiol1dn_wyde4hqaaa3boerxwa166.png "/>

It is clear that no change has taken place in the table.

The change code adds the above ' = ' result as follows

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/82/FD/wKioL1doBEzDtUaaAAAvmL2tgZk514.png "title=" Untitled. png "alt=" Wkiol1dobezdtuaaaaavml2tgzk514.png "/>

When working with transactions in various languages, close the connection mysql_close () manually at the end.

You think it's right up there? It is true, because all of the above are already configured.

before it was done, it took a long time for the test to find a Tai Hang !! listen to my Slow way :

Check the relevant information, basically are using show engines; see if the InnoDB storage engine is supported, but after testing, but found that the rollback is not at all, the code shows success, but the database is 1 worning; Although show engines shows that InnoDB is yes, you still need to add this code:

ALTER TABLE Test_info TYPE=INNODB; or you may never be able to test successfully . .

Summary: Transactions have deep implications for database integrity. is an integral part of it. There are many other aspects of the use of transactions. Need to learn slowly

This article is from the "Traces" blog, be sure to keep this source http://wpfbcr.blog.51cto.com/10696766/1791197

Talking about database--transaction (MySQL)