How to fix mysql select command giving wrong result after the update in another db connection?

Question

I have taken two mysql db connections using python MySQLdb module namely db and db1. The first connection is used to read a table and the second connection is for updating the table. The following are the sequence of code that I have used.

1 : Read id from user table using db conncetion; current value 'Y'

2 : Update id in user table to 'N' using db1 connection.

3 : Reading id from user table using db connection. But at this time it gives value 'Y'.

import MySQLdb

db = MySQLdb.connect("localhost","root","test007","db",charset='')

apikey="123"


cursor=db.cursor()                                             ## fetching no. of data received in valid time range
cursor.execute("select id from USER where apikey=%s",(apikey,))
data=cursor.fetchone()
cursor.close()
print data #current value 'Y'

db1 = MySQLdb.connect("localhost","root","test007","db",charset='')

cursor=db1.cursor()                                             ## fetching no. of data received in valid time range
cursor.execute("update USER set id='N' where apikey=%s",(apikey,))
db1.commit()
cursor.close()
db1.close()

cursor=db.cursor()                                             ## fetching no. of data received in valid time range
cursor.execute("select id from USER where apikey=%s",(apikey,))
data=cursor.fetchone()
cursor.close()
print data
db.close()

In step 3 it doesn't show the updated value. Why does this happen? How can i solve this issue without closing the connection db and take another connection to read the table after update?

This is not the actual code implementation. db1 is actually running from some other file. For simplicity, I just showed this.

What is the setting of `autocommit`? Do you use transactions? — Rick James, Jul 03 '19 at 16:55
I haven't used autocommit. If you run the above code, it will have the above said problem. — mcv, Jul 04 '19 at 03:57
Any time. It is a setting that won't change unless you deliberately change it. (And it sounds like you are not changing it.) — Rick James, Jul 04 '19 at 16:24

score 0 · Accepted Answer · answered Jul 05 '19 at 16:56

It's doing what it is supposed to. REPEATABLE-READ means that you see the same data, regardless of what is going on in other transactions. This, of course, lasts only until your transaction terminates.

Think of it as taking a snapshot of the entire dataset when you start the transaction. Then, everything you do or see (SELECT) is frozen in time.

Either change to READ-UNCOMMITTED or break up the SELECTs into separate transactions.

Oluwafemi Rotimi Nehemiah · Answer 2 · 2022-10-19T21:07:55.460

0

As explained above, this is apparently as it ought to behave because of this illustion below (see https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html)

So simply commiting transaction before reading from the session resolves this error.

edited Oct 19 '22 at 21:07

answered Oct 19 '22 at 20:47

Oluwafemi Rotimi Nehemiah

11
2

How to fix mysql select command giving wrong result after the update in another db connection?

2 Answers2