Differences in MySQL autocommit

Python in the development of a small web site encountered a problem, through the MySQL client to modify the data in the library (commit), the foreground web page refresh or previous data. And another MySQL client query can query to the value. You can see the new value after restarting the Web service. The same way you can see new values when you connect using Java's JDBC approach.

So decided to turn on the MySQL log to see what the difference is between the actual SQL run.

First look at the Python connection method:

150517 13:34:00     3 connect   [email protected]  on dw                     3 Query     SET NAMES  ' UTF8 '  collate   ' Utf8_general_ci '                      3 query     set @ @session. autocommit  = OFF                     3 query     select id,name from job  where id=5150517 13:34:20     3 Query      select id,name from job where id=5                      3 quit 

Look at Java again:

150517 13:39:08     5 connect   [email protected]  on dw                     5 Query     /* mysql-connector-java-5.1.25  (  Revision: ${bzr.revision-id} )  */show variables where variable_name = ' Language '  OR Variable_name =  ' net_write_timeout '  OR Variable_name =  ' Interactive_timeout '  OR Variable_name =  ' wait_timeout '  or variable_name =   ' character_set_client '  OR Variable_name =  ' character_set_connection '  OR  variable_name =  ' character_set '  OR Variable_name =  ' character_set_server '   or variable_name =  ' tx_isolation '  OR Variable_name =  ' transaction_isolation ' Or variable_name =  ' character_set_results '  OR Variable_name =  ' timezone '  or variable_ name =  ' time_zone '  OR Variable_name =  ' System_time_zone '  or variable_ name =  ' lower_case_table_names '  OR Variable_name =  ' Max_allowed_packet '  or  Variable_name =  ' net_buffer_length '  OR Variable_name =  ' Sql_mode '  or  Variable_name =  ' Query_cache_type '  OR Variable_name =  ' query_cache_size '  OR Variable_name =  ' Init_connect '                      5 query     /*  mysql-connector-java-5.1.25  ( Revision: ${bzr.revision-id} )  */select @@ session.auto_increment_increment                     5 query     show collation                     5 query      SET NAMES latin1                     5 Query      set character_set_results = null                     5 query     set  autocommit=1150517 13:39:09     5 Query      Select id,name from job where id=5150517 13:39:29     5  Query     select id,name from job where id=5      &nbsP;              5 quit 

At first glance, it seems to be similar, no difference. is very depressed, so think of another way. Start with 2 Python scripts to try, one to write, and another to read.

The result is that the script read can read the new data immediately. Then the next reading of the script found that each time after reading a commit. Remove the commit and try again, and the problem recurs. And looked back at the differences between the Python script and the MySQL log generated by the Java script. Found

Python:3 query set @ @session. autocommit = offjava:5 Query Set autocommit=1

Querying the MySQL Documentation

The autocommit mode. If set to 1, any changes to a table take effect immediately. If set to 0, you must use to COMMIT accept a transaction or to ROLLBACK cancel it

Autocommit=1 is automatically submitted as Autocommit=on.

Next, you'll try the next PostgreSQL and you'll be able to see the new data when you autocommit=false.

When looking at PostgreSQL's python-driven documentation, I found a good hint, reprint as follows:

Warning

By default, any query execution, including a simple SELECT would start a transaction:for long-running programs, I F no further action is taken, the session would remain "idle in transaction", a condition non desiderable for several reaso NS (Locks is held by the session, tables bloat ...). For long lived scripts, either ensure to terminate a transaction as soon as possible or use an autocommit connection.

Differences in MySQL autocommit