CODEWITHSUNDEEP
mysqltransactions
Rehan Yousaf
Rehan Yousaf

Reputation: 765

Partially commit MySQL Transaction?

I want to know if there's a way to commit a transaction partially. I have a long running transaction in C# and when two users are running this transaction parallel to each other, the data is co-dependent and should show to them both even while in the transaction. For example say I have a table with these 3 columns

username | left_child | right_child

I am making a binary tree and whenever a new user is added into the database they end up somewhere in the tree. But I am running all of the insertions and updates in one transaction so if there's even one error the whole transaction can be rolled back and the structure of the tree is not disturbed. But the problem is the when two users are using my web app at the same time. Say that username 'jackie_' does not have any children at the minute. Two new users 'king_' and 'robbo' enter parallel to each other and the transaction is running for both of them. Since the results of the transaction running for one user are not visible to the other user in the actual database yet, they both think that the left_child of 'jackie_' hasn't been set yet and so they both update the left_child to their own username. During the transaction since the update was successful for both of them, they both commit the transaction. Now I have two users but only one of them is actually successfully entered into the tree and the structure of the tree is disturbed completely.

So what I need is to be able to commit one transaction even during, "partially". So if 'robbo' got set the left_child of 'jackie_' first, the transaction implements the change into the database so when 'king_' tries to update the same row, he can't. But if along the way, if some other problem occurs for 'robbo' I still want to be able to rollback the whole transaction. Any other solutions which would be more practical are appreciated as well.

For all the queries that I am running, this is the way I am doing it in the transaction

        string insertTreesQuery = "INSERT INTO tree (username) VALUES('king_')";
        MySqlCommand insertTreesQueryCmd = new MySqlCommand(insertTreesQuery , con);
        insertTreesQueryCmd.Transaction = sqlTrans;
        insertTreesQueryCmd.ExecuteNonQuery();

where sqlTrans is the transaction that I am using for all the MySqlCommand objects before executing them

Upvotes: 2

Views: 1739

Answers (1)

steliosbl
steliosbl

Reputation: 8921

What you are asking is not possible. You cannot "partially" commit a transaction. I'm assuming that your example is greatly simplified since you mention the transactions are very large. In that case, it would probably be best to split it up into smaller ones that can be committed independently, thus reducing the chance of there being a conflict.

Upvotes: 2

Related Questions