| Bug #48492 | Dirty Read in Select Query | ||
|---|---|---|---|
| Submitted: | 3 Nov 2009 8:16 | Modified: | 25 Nov 2009 8:37 |
| Reporter: | Adityakar Jha | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.1.28, 5.1.40 | OS: | Linux (2.6.18-128) |
| Assigned to: | CPU Architecture: | Any | |
[3 Nov 2009 11:13]
Valeriy Kravchuk
Please, try to repeat with a newer version of MySQL server, 5.1.40, and inform about the results.
[3 Nov 2009 12:03]
Adityakar Jha
To be precise it seems to be a problem of Query_Cache. The Query Cache is not invalidated on updates. When we disable Query Cache it works fine. But that solution is unacceptable.
[3 Nov 2009 13:04]
Valeriy Kravchuk
I suspected query cache, and that's why asked you to check latest version, 5.1.40, where some query cache related problems were fixed. 5.1.28 is year old non-GA version, it contains many bugs fixed since that.
[14 Nov 2009 8:43]
Adityakar Jha
We are now using mysql-5.1.40-GA. We are still facing the same problem. If we do a select after an update (and commit) query we get stale data. We are using mysql native c api to connect to server. We are using latest client library of mysql that is available on mysql website.
[18 Nov 2009 19:24]
Valeriy Kravchuk
Is your application multi-threaded? Also, please, check if the same problem happens with 5.1.41.
[24 Nov 2009 6:58]
Adityakar Jha
Sorry for the late reply. I was on leave. Yes our application is multithreaded and we are using latest thread-safe client library of mysql for C APIs. We shall try repeating the problem on mysql-5.1.41. Meanwhile you please suggest what additionally I should do in the context of multithreaded mysql client application.
[24 Nov 2009 8:25]
Valeriy Kravchuk
Coming back to your query log, if transaction in connection 30 was started before commit in connection 29, it will see data as they were before commit. Please, check concurrency logic and isolation level of your connections. Also make sure that different threads do NOT share the same connection.
[24 Nov 2009 9:26]
Adityakar Jha
Our application has five threads and all the five threads are having dedicated connections. After update and commit through application we wait for sometime to run another test case which does a select through application. I would like to add one more point. If we use mysql clients e.g. sqlyog or mysql shell then we are able to see the updated result but application threads read stale result.
[24 Nov 2009 10:25]
Adityakar Jha
I do not understand the phrase "if transaction in connection 30 was started before commit in connection 29". MySQL C client APIs do not provide an api to begin transaction. There is only "mysql_commit()" api to commit a transaction. Do we need to commit even after a group of select statements (without any update query) so as to mark the begining of new transaction after commit. We create the one connection per application thread and we never close connection till the lifetime of application process since we are using mysql option MYSQL_OPT_RECONNECT that takes care of intermittent connection problems.
[24 Nov 2009 10:32]
Valeriy Kravchuk
I need to know how your threads (those that do SELECT) connect, do you set autocommit property to 1 explicitly or not. See http://dev.mysql.com/doc/refman/5.1/en/mysql-autocommit.html.
[24 Nov 2009 11:03]
Adityakar Jha
When a test case runs, it is taken up by an application thread. In the logic of test case there may be only select queries or update queries or a mix of both. Before business logic is executed by the application thread, we set the autocommit to 0 (OFF). If there is any update query involved in the application logic a flag is set to indicate the same. At the end of business logic the flag is checked and if it is found set then only we do a mysql_commit() else we skip it and close the result set and statement. The connection is not closed and hence in the case of execution of only select queries we are not calling mysql_commit() api. As per our understanding we do not need to commit if we execute only select statements. Please correct me if I am wrong.
[24 Nov 2009 11:22]
Valeriy Kravchuk
If you use autocommit to 0 then, taking into account the fact that by default REPEATABLE READ isolation level is used, all SELECTs until COMMIT or ROLLBACK will "see" the database at the same status as it was at the beginning of this (read) transaction (at the moment of the very first select). Please, try to set isolation level to READ COMMITTED for SELECTs and check if committed updates will be taken into account.
[25 Nov 2009 5:28]
Adityakar Jha
You are right. Its a case of consistent read and it has been beautifully explained by an example in 13.5.10.7 in mysql reference manual. We shall change transaction isolation level to READ_COMMITTED. That should solve this problem. Thanks a lot for your quick response and guidance.
[25 Nov 2009 8:37]
Valeriy Kravchuk
So, this problem was not a result of any bug in MySQL code.
Description: My Application is using MySQL C api. The problem I am getting is not consistent. After updation or deletion application is commiting the transaction. After commit if i again select the row then i am getting old value in case of of update and I am getting that particular row in case of delete. In commit api we are not receiving any error. Following is the relevant section of query.log at mysqld. 29 Close stmt 29 Prepare UPDATE IDENTITY_MAP set registration_status =? , auth_pending_flag = 0 where public_identity in (SELECT publ ic_identity from PUBLIC_IDENTITY where implicit_grp_id =?) and private_identity =? 29 Execute UPDATE IDENTITY_MAP set registration_status =0 , auth_pending_flag = 0 where public_identity in (SELECT publ ic_identity from PUBLIC_IDENTITY where implicit_grp_id =29) and private_identity ='a_26_pr@load.test' 29 Close stmt 29 Prepare SELECT COUNT(*) from IDENTITY_MAP as t1 right outer join PUBLIC_IDENTITY as t2 on t2.public_identity_id = t1 .public_identity_id where t2.subscription_grp_id = ? and t1.registration_status != 0 29 Execute SELECT COUNT(*) from IDENTITY_MAP as t1 right outer join PUBLIC_IDENTITY as t2 on t2.public_identity_id = t1 .public_identity_id where t2.subscription_grp_id = 26 and t1.registration_status != 0 29 Close stmt 29 Query set autocommit=0 29 Prepare SELECT scscf_diameter_address, scscf_sip_url from IMS_SUBSCRIPTION where subscription_id =? 29 Execute SELECT scscf_diameter_address, scscf_sip_url from IMS_SUBSCRIPTION where subscription_id =26 29 Reset stmt 29 Close stmt 29 Prepare UPDATE IMS_SUBSCRIPTION set scscf_diameter_address =?, scscf_sip_url =? , shared_ifc_supported =0 where subs cription_id =? 29 Execute UPDATE IMS_SUBSCRIPTION set scscf_diameter_address ='', scscf_sip_url ='' , shared_ifc_supported =0 where su bscription_id =26 29 Close stmt 29 Query commit /********* as can be seen Tx is commited. Now see the following select query returning old value **********/ 30 Prepare SELECT registration_status from IDENTITY_MAP where private_identity =? and public_identity =? 30 Execute SELECT registration_status from IDENTITY_MAP where private_identity ='a_26_pr@load.test' and public_identity ='sip:a_26_pu@load.test' I am using following my.cnf file. [mysqld] port=3306 server-id=1 #log=/tmp/query.log #log-bin=/usr/HSS2/data/prod-bin.log #relay-log=/usr/HSS2/data/prod-relay-bin.log #binlog-do-db=PROD binlog-cache-size=5120K #innodb-data-file-path=/usr/HSS3/data/ibdata1:1G;/usr/HSS3/data/ibdata2:1G;/usr/HSS3/data/ibdata3:1G;/usr/HSS3/data/ibdata4:1G:autoextend innodb-file-per-table datadir=/usr/HSS1/data/var thread-concurrency=30 innodb-thread-concurrency=16 innodb-buffer-pool-size=1G innodb-log-file-size=1024M innodb-log-buffer-size=256M innodb-flush-method=O_DIRECT innodb-support-xa=0 innodb-max-dirty-pages-pct=70 join-buffer-size=1M key-buffer-size=64M table-open-cache=256 query-cache-size=128M query-cache-limit=128M innodb-flush-log-at-trx-commit=0 sync-frm=0 thread-stack=1M thread-cache-size=20 max-heap-table-size=512M tmp-table-size=512M innodb-doublewrite=0 innodb-locks-unsafe-for-binlog=1 socket=/tmp/ss.sock master-host=192.168.129.202 master-user=root master-password= master-connect-retry=60 #replicate-do-db=PROD [client] port=3306 socket=/tmp/ss.sock How to repeat: The problem repetition is inconsistent.