The impact of Mysql automatic commit on queries is in database application programming. Generally, to improve the statement execution efficiency, the automatic commit mode of the database is disabled, and then the changes are committed using transactions. Then the problem arises. The following is a streamlined program using MysqlCAPI: MYSQL * connmysql_init (NULL); mysql_real_conn
The impact of Mysql automatic commit on queries is in database application programming. Generally, to improve the statement execution efficiency, the automatic commit mode of the database is disabled, and then the changes are committed using transactions. Then the problem arises. The following is a streamlined program using Mysql c api: MYSQL * conn = mysql_init (NULL); mysql_real_conn
Impact of Mysql automatic submission on queries
In database application programming, the automatic commit mode of the database is usually closed to modify the execution efficiency of statements, and then the changes are committed in transaction mode. Then the problem arises. The following is a streamlined program using Mysql c api:
MYSQL * conn = mysql_init (NULL); mysql_real_connect (conn, "198.120.0.199", "shr", "shr", "sp5000", 3306, NULL, 0 ); int num = 2; char SQL [512] = "select name from aaa"; MYSQL_STMT * stmt; MYSQL_BIND column [1]; mysql_autocommit (conn, 0 ); // set automatic submission to disable stmt = mysql_stmt_init (conn); for (int jj = 0; jj <100; jj ++) {mysql_stmt_prepare (stmt, SQL, strlen (SQL ); mysql_stmt_execute (stmt); memset (column, 0, sizeof (column); unsigned Long length [1]; my_bool is_null [1]; char name [100]; column [0]. buffer_type = MYSQL_TYPE_VAR_STRING; column [0]. buffer = (char *) name; column [0]. buffer_length = 100; column [0]. is_null = & is_null [0]; column [0]. length = & length [0]; mysql_stmt_bind_result (stmt, column); mysql_stmt_store_result (stmt); while (true) {rel = mysql_stmt_fetch (stmt); if (rel! = 0) break; printf ("data: % s \ n", name);} getchar ();} mysql_stmt_free_result (stmt); mysql_stmt_close (stmt );
Mysql is automatically submitted by default, and will be automatically closed through mysql_autocommit (conn, 0. The results of each query are the same in the loop query! Although this table has been modified elsewhere and submitted successfully, the data can still be correctly read for the first time during every restart of the program. I initially thought it was the impact of mysql cache, So I disabled mysql cache, but I found the same problem. Okay, then I simply put
Stmt = mysql_stmt_init (conn );
Mysql_stmt_free_result (stmt );
Mysql_stmt_close (stmt );These three statements of code move into the loop. Every time I generate a new query object, the results still disappoint me. I commented out all the statements that can be annotated, and finally I commented out mysql_autocommit (conn, 0). Then everything went fine. Every query was the latest Table content.
Automatic submission also affects select! But I can't help disabling automatic submission. How can I solve this problem. Because the program was rewritten from oracle to mysql, I would like to mention that oracle uses the OCI library, here, OCIStmtExecute uses the eighth parameter when executing an SQL statement (the oci function parameters are many ...) mode to determine whether to submit immediately or wait for display to call commit for submission. The default mode is non-automatic submission. For select statement parameters, the default mode is also set. Do select statements need to be submitted? In the Query Process of various databases, we do not need to commit after hitting a select statement. Most databases do.
But this is not the case for mysql. Do you really want to submit it?
Mysql_stmt_prepare (stmt, SQL, strlen (SQL );Then the code for submission is added:
Mysql_commit (conn );The problem was solved immediately, and database changes in the recycling query can also be queried immediately!
Then I opened the mysql terminal to test the basic query and found that the same phenomenon exists in mysql cmd. You can perform A simple test by following the steps below: Open Terminal A and connect to mysql. The default submission mode for each connection is automatic submission. You can enter the command show variables like 'autocommit '; check that the default value is
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Autocommit | ON |
+ --------------- + ------- +
Enter the command set session autocommit = 0. The command will be automatically submitted and closed. Then, check that the parameter is OFF and query a table. Then, open terminal B to modify and submit the table data; return to Terminal A for repeated queries and you will find that the results remain unchanged! Then, Click commit. The latest table data is displayed when the result is queried again! That is to say, to query a table, we need to select...; commit; select...; to query the results in real time! The Mysql version is 5.5.
Let's take a look at oracle and sqlplus connecting to the database. Input show autocommit to show that the default value is OFF (if it is ON, it is disabled through et autocommit off). Repeat the test above, although oracle is automatically committed to OFF, there is no mysql for select.
For select read operations, oracle and mysql have different processing methods when the automatic submission settings are disabled. Whether the read operation needs to be committed is a transaction, but like select ....; commit; select ....; in this way, it is easy to find the correct data format! Remember, I will try MYSQL C ++ class library or JDBC later to see if there is any change.
