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.
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.