encode
Transaction rollback seems to be unsuccessful
Below is my code
async with conn.transaction(force_rollback=True):
transaction = await conn.transaction()
try:
query = user.insert().values(**row["data"])
await conn.execute(query=query)
query_sms = (
user_sms.update()
.where(user_sms.c.ccode == "code")
.values(status=1)
)
await conn.execute(query=query_sms)
except Exception as e:
print(e)
print("This is the error")
await transaction.rollback()
return False
else:
await transaction.commit()
return True
I estimate that user_sms.c.code is written as user_sms.c.ccode
As a result, user.insert has been executed, but user_sms.update has not been executed
The print function outputs, but the single database does not roll back
print output:
ccode
This is the error
depend： databases==0.5.3 SQLAlchemy==1.4.25
So the two executed transactions are not the same connection? So, how should we solve it?
I'm not really sure how to fix it.
The only way I could fix it was by reverting parallel transactions here.
Still waiting for a reply there.
@jakehu Why to do you need the first line?
async with conn.transaction(force_rollback=True):
# (...)
Would your code do what you want if you removed the with statement and decreased the indentation of the rest of the code?
Another idea to consider:
try:
async with conn.transaction():
query = user.insert().values(**row["data"])
await conn.execute(query=query)
query_sms = (
user_sms.update()
.where(user_sms.c.ccode == "code")
.values(status=1)
)
await conn.execute(query=query_sms)
except Exception as e:
print(e)
print("This is the error")
return False
else:
return True
(I haven't tested this idea. I am sorry in advance if it doesn't work. In theory, it seems OK [famous last words])
Plus with is to refer to https://github.com/encode/databases/blob/master/tests/test_databases.py line 589
It is also unsuccessful to change it to what you wrote. I found a problem. The problem should come from conn.
When I changed to:
async with Database(db_url) as conn:
async with conn.transaction(force_rollback=True):
transaction = await conn.transaction()
try:
query = user.insert().values(**row["data"])
await conn.execute(query=query)
query_sms = (
user_sms.update()
.where(user_sms.c.ccode == "code")
.values(status=1)
)
await conn.execute(query=query_sms)
except Exception as e:
print(e)
print("This is the error")
await transaction.rollback()
return False
else:
await transaction.commit()
return True
Can run
And my previous conn is defined like this
database = Database(app.config.custom["mysql"])
@app.listener("before_server_start")
async def startup_db(app, loop):
await database.connect()
@app.listener("after_server_stop")
async def shutdown_db(app, loop):
await database.disconnect()
app.ctx.conn = database
Write the connection link to the application context
@ffunenga @aminalaee
@jakehu
I guess that test uses nested transactions, specifically database.transaction(force_rollback=True (the outer one) to rollback when test finishes.
I tested it, my writing above is still wrong, it still can’t run
I don't know what you said
@aminalaee
Finally I referenced #424
Modified with transaction._connection
transaction = await conn.transaction()
try:
query = user.insert().values(**row["data"])
await transaction._connection.execute(query=query)
query_sms = (
user_sms.update()
.where(user_sms.c.ccode == "code")
.values(status=1)
)
await transaction._connection.execute(query=query_sms)
except Exception as e:
print(e)
print("This is the error")
await transaction.rollback()
return False
else:
await transaction.commit()
return True
It works, but I don't know if it is correct?
I think that's not related, this should work too:
transaction = await conn.transaction()
try:
query = user.insert().values(**row["data"])
await conn.execute(query=query)
query_sms = (
user_sms.update()
.where(user_sms.c.ccode == "code")
.values(status=1)
)
await conn.execute(query=query_sms)
except Exception as e:
print(e)
print("This is the error")
await transaction.rollback()
return False
else:
await transaction.commit()
return True
