I get many select @@session.tx_read_only, would one do the same?

I am using Python to insert value in a database through an API written by someone else in Java. The insertion goes quite slow, and when I look at the mysql log of the transcactions for one insert I see that it often does select @@session.tx_read_only:

| 2015-09-04 10:53:51 | INSERT INTO `InterestTable` (`added_by`, `datetime_added`, `datetime_last_updated`, `updated_by`, `accuracy`, `average_reported_quality`, `empirical_quality`) VALUES ('admin', '2015-09-04 10:53:50.502649', null, null, 42.28, 43.0)
| 2015-09-04 10:53:51 | select @@session.tx_read_only
| 2015-09-04 10:53:51 | select @@session.tx_read_only
| 2015-09-04 10:53:51 | UPDATE `muloTransactionLog` SET `transactionId` = 'aaaact4xr7lus7v3ina5bgyaae', `userName` = 'admin', `startTime` = '2015-09-04', `transactionStatus` = 'COMMITED', `endTime` = '2015-09-04' WHERE `transactionId`= 'aaaact4xr7lus7v3ina5bgyaae'
| 2015-09-04 10:53:51 | select @@session.tx_read_only
| 2015-09-04 10:53:51 | select @@session.tx_read_only
| 2015-09-04 10:53:51 | commit
| 2015-09-04 10:53:51 | SET autocommit=1
| 2015-09-04 10:53:51 | SHOW FULL TABLES FROM `db` LIKE 'PROBABLYNOT'
| 2015-09-04 10:53:52 | SET autocommit=0
| 2015-09-04 10:53:52 | select @@session.tx_read_only
| 2015-09-04 10:53:52 | select @@session.tx_read_only
| 2015-09-04 10:53:52 | INSERT INTO `muloTransactionLog` (`transactionId`, `userName`, `startTime`, `transactionStatus`, `endTime`) VALUES ('aaaact4xr7omm7v3ina5bgyaae', 'g3943', '2015-09-04', 'STARTED', null)
| 2015-09-04 10:53:53 | select @@session.tx_read_only
| 2015-09-04 10:53:53 | select @@session.tx_read_only
| 2015-09-04 10:53:53 | INSERT INTO `muloTransactionLogEntry` (`id`, `muloTransactionLog`, `entity`, `type`) VALUES ('AAAACT4XR7PWQ7V3INA5BGYAAE', 'aaaact4xr7omm7v3ina5bgyaae', 'InterestTable', 'ADD')
| 2015-09-04 10:53:53 | select @@session.tx_read_only
| 2015-09-04 10:53:53 | select @@session.tx_read_only

From http://docs.oracle.com/cd/E17952_01/refman-5.6-en/server-system-variables.html#sysvar_tx_read_only I guess it’s being used so that there is not writing and reading at the same time, but I don’t see it being turned off again. So can it be used only once and have the same effect, like this:

| 2015-09-04 10:53:51 | INSERT INTO `InterestTable` (`added_by`, `datetime_added`, `datetime_last_updated`, `updated_by`, `accuracy`, `average_reported_quality`, `empirical_quality`) VALUES ('admin', '2015-09-04 10:53:50.502649', null, null, 42.28, 43.0)
| 2015-09-04 10:53:51 | select @@session.tx_read_only
| 2015-09-04 10:53:51 | UPDATE `muloTransactionLog` SET `transactionId` = 'aaaact4xr7lus7v3ina5bgyaae', `userName` = 'admin', `startTime` = '2015-09-04', `transactionStatus` = 'COMMITED', `endTime` = '2015-09-04' WHERE `transactionId`= 'aaaact4xr7lus7v3ina5bgyaae'
| 2015-09-04 10:53:51 | commit
| 2015-09-04 10:53:51 | SET autocommit=1
| 2015-09-04 10:53:51 | SHOW FULL TABLES FROM `db` LIKE 'PROBABLYNOT'
| 2015-09-04 10:53:52 | SET autocommit=0
| 2015-09-04 10:53:52 | INSERT INTO `muloTransactionLog` (`transactionId`, `userName`, `startTime`, `transactionStatus`, `endTime`) VALUES ('aaaact4xr7omm7v3ina5bgyaae', 'g3943', '2015-09-04', 'STARTED', null)
| 2015-09-04 10:53:53 | INSERT INTO `muloTransactionLogEntry` (`id`, `muloTransactionLog`, `entity`, `type`) VALUES ('AAAACT4XR7PWQ7V3INA5BGYAAE', 'aaaact4xr7omm7v3ina5bgyaae', 'InterestTable', 'ADD')

And if that could work, where should I look for changing this

Answer

This is because the MySQL JDBC driver has a default useSessionStatus value of false. Each time the driver needs to check the isReadOnly status of the target database, it will send a select @@session.tx_read_only to the server. Setting useSessionStatus as true will use the connection object’s local state.

The simplest way to remove this query is to append &useLocalSessionState=true to the JDBC connection string.

Advertisement

Answer