Transaction Design Issues

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 事务设计问题

| username: TiDBer_fsUaXQRe

Overview: If Lua threads are used as business threads, and n threads are used as SQL client threads, Lua uses coroutines to achieve business concurrency.
Problem Description: How to ensure that the SQL logic between begin and commit or rollback is not interfered with by other business SQL during transaction design?

| username: xfworld | Original post link

Using pessimistic transactions is fine. When the data is locked, others can only read it and cannot make changes. Only after the transaction is committed (or released) can other SQL statements modify the data.

| username: 有猫万事足 | Original post link

You wouldn’t want these Lua threads to share a single database session, would you?

As long as each Lua thread uses a different session, they won’t interfere with each other. Generally, if you just get sessions from the connection pool, there shouldn’t be any interference issues. This should be a problem solved by the connection pool.

| username: Fly-bird | Original post link

Can thread locks solve the problem?

| username: ajin0514 | Original post link

Sure, I’ll learn it.

| username: TiDBer_fsUaXQRe | Original post link

In fact, it is a Lua thread and multiple SQL thread pools. If the transaction is executed in SQL thread A, then how can we ensure that other SQL operations executed in thread A do not affect the transaction?

| username: TiDBer_fsUaXQRe | Original post link

My understanding is that during the execution of a transaction, other SQL operations executed in the same SQL thread are directly blocked. Only after the transaction commits or rolls back will the thread execute other SQL operations. Is that correct?

| username: TiDBer_fsUaXQRe | Original post link

Each SQL thread maintains an SQL client, and all logic of the same transaction will only be executed in one SQL thread.

| username: xfworld | Original post link

This explanation should be understandable: When data is locked, other transactions cannot change the data… they can only read it.

| username: TiDBer_fsUaXQRe | Original post link

I understand what you mean, but Lua threads use coroutines for concurrency. During the period from begin to commit or rollback of the same SQL thread, there may be other business SQLs executing on that SQL thread.

| username: xfworld | Original post link

This doesn’t matter much. Even if it’s a thread pool, it’s still a shared resource. Reducing the cost of creation and destruction is quite normal.

| username: ajin0514 | Original post link

Restart it.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.