Context: The framework used is Spring and all queries are run with JdbcTemplate. Mysql Server version is 5.6.19. The
table is an
InnoDB table and defaults like
auto commit and isolation level repeatable-read is set.
Insert happens inside a transaction and a
select which reads the same data inserted does not see the data. The
select runs after the
insert and after the
insert transaction has
I've enabled bin log as well as general log in mysql. Relevant logs below
SET TIMESTAMP=1438265764/*!*/; BEGIN /*!*/; # at 249935389 #150730 14:16:04 server id 1 end_log_pos 249935606 CRC32 0xa6aca292 Query thread_id=40 exec_time=0 error_code=0 SET TIMESTAMP=1438265764/*!*/; insert into user_geo_loc_latest(user_id, lat, lng) values(x,y,z) on duplicate key update lat=y, lng=z /*!*/; # at 249935606 #150730 14:16:06 server id 1 end_log_pos 249936255 CRC32 0x2a52c734 Query thread_id=40 exec_time=0 error_code=0 SET TIMESTAMP=1438265766/*!*/; INSERT INTO table(txnid) VALUES ('885851438265675046') /*!*/; # at 249936255 #150730 14:16:06 server id 1 end_log_pos 249936514 CRC32 0x6cd85eb5 Query thread_id=40 exec_time=0 error_code=0 SET TIMESTAMP=1438265766/*!*/; INSERT INTO table2(x) VALUES (y) /*!*/; # at 249936514 #150730 14:16:06 server id 1 end_log_pos 249936545 CRC32 0xceb9ec56 Xid = 9406873 COMMIT/*!*/;
150730 14:16:04 40 Query ... .... 40 Query select count(*) from table where txnid = '885851438265675046' 40 Query select @@session.tx_read_only 40 Query INSERT INTO table(txnid) VALUES ('885851438265675046') 40 Query select @@session.tx_read_only 40 Query INSERT INTO table2(x) values(y) 40 Query commit .... 150730 14:16:07 36 Query select pp.*, b.create_date from table pp left join bill b on pp.bill_id = b.bill_id where pp.txnid = '885851438265675046'
insert (249935389) should not be part of the transaction at all. It is a separate API call and completely unrelated. It could be spring mixing it with the transaction or I am reading the log wrong? AFAIK since it's on the same thread it implies the insert is in the transaction.
The next two
inserts are part of the transaction and it looks like it commits. (249936514). Now the select query (last one in general log) runs after the commit and it does not see the data. It returns 0 rows. How can this happen considering the data is
committed? Or is the
commit not on the thread 40? Since it does not have the thread id.
To summarize I have two questions.
BEGINin the binlog being before the
INSERT INTO user_geo_loc(which is not part of the transaction), is this a bug with spring/Jdbc or MySql simply does this as it knows this transaction has already committed (as transactions are written to binlog when they've succeeded) and therefore would never be rolled back.
Given the commit happens before the select (commit is at 14:16:06 and select is at 14:16:07) how is it that the select does not return the row inserted by the transaction?
This is extremely perplexing. Any help would be appreciated
Note: The queries in both the bin and query log have been edited to remove sensitive information. But the essence of the queries remain the same
Edit: Updated with general log and query log with a detailed example.