2 mysql connections doesnt seem to update each other when doing data-manipulation

Question

So I have a python bot running that has 2 threads, each thread is using it's own database object to have a database connection towards a MySQL database. I tried it with only one connection but received errors.

So far so good.

The code nearly works fine and everything works as expected BUT my connection 1 SELECT statement doesn't seem to know that connection 2 updated a table. This is obviously a huge problem when handling 'world generation' like in my case in a different thread because the main application doesn't know that a new world had been generated. Now I'm wondering why's that?

Here's an example Query that my connection 2 is executing

    def add_block_to_world(self, idWorld, idBlock, x, y):
        sql = f"SELECT idRel FROM worlds_has_blocks WHERE idWorld = {idWorld} AND x = {x} AND y = {y};"
        self.cursor.execute(sql)
        res = self.cursor.fetchone()
        if res:
            # update the block
            sql = f"UPDATE worlds_has_blocks SET idBlock = {idBlock} WHERE idWorld = {idWorld} AND x = {x} AND y = {y};"
            self.cursor.execute(sql)
            self.mydb.commit()
        else:
            # There doesn't exist a block yet
            sql = f"INSERT INTO worlds_has_blocks VALUE(NULL, {idWorld}, {idBlock}, {x}, {y});"
            self.cursor.execute(sql)
            self.mydb.commit()

I've read a lot that I may be missing a commit or not, but I'm not. And If I read it correctly once I commit all other connections should receive the new edited columns?

here's my database constructor in case that's important.

class Database():
    
    def __init__(self):
        self.mydb = mysql.connector.connect(
            host=config.botConfig["host"],
            user=config.botConfig["user"],
            password=config.botConfig["password"],
            port=config.botConfig["port"],
            database=config.botConfig["database"],
            charset='utf8mb4'
        )
        self.cursor = self.mydb.cursor()

        # Check if the connection is alive
        if self.mydb.is_connected():
            print("Database connection successful")
        else:
            print("Database connection failed")

below that are of course the methodes..

Any help? Can I somehow sync both connections? Is it something entirely I'm doing wrong with how I handle threads and mysql connections? Thanks.

There is probably an [open transaction](https://stackoverflow.com/questions/7598373/how-to-display-open-transactions-in-mysql), which needs to be committed. (see link to know how to display those open transactions) — Luuk, Apr 25 '23 at 11:51
1) I would use an insert ... on duplicate key update ... instead of a select / update / insrt structure. 2) Your isolation level is probably on "repeatable read", which means the first select established the version of the data that a connection reads in. Try setting your isolation level to "read committed" to see updates done by concurrent transactions in a select. — Shadow, Apr 25 '23 at 12:00
That's true. My Isolation level was "repeatable read", I'll change that and see if it made a difference. And also thanks for the other suggestion with duplicate key — JanikCodes, Apr 25 '23 at 12:02

score 0 · Accepted Answer · answered Apr 25 '23 at 12:07

0

Fixed my issue. The problem was that the session and global transaction isolation level was set to 'Repeatable Read'

Changing that to 'Read Commited' fixed the issue. Thanks!

answered Apr 25 '23 at 12:07

JanikCodes

9
3

2 mysql connections doesnt seem to update each other when doing data-manipulation

1 Answers1