Difference between isolation levels in locktable implementation

Question

I'm trying to implement lock tables in database with c++ and mysql. i'm using tpc-a benchmarks where i simulate a mini bank database and i run a bunch of threads on it and each thread do 10 transactions concurrently and goal is to maintain consistency after all transaction finished their work. here is a piece of code that is a part of for loop that each thread run 10 times :

g_lockTableAccount->lockRecord(hashInt(randomAccountNumSource, g_numAccount), randomAccountNumSource, GetCurrentThread());
    startTransaction(conn);
    //get first account balance and branch.
    {

        std::string query = "SELECT account_balance,branch_ID from `account` WHERE account_ID = " + std::to_string(randomAccountNumSource) + ";";
        executeQuery(conn, query);
        MYSQL_RES * res = mysql_use_result(conn);
        MYSQL_ROW row = mysql_fetch_row(res);

        sourceAccountBalance = toInt(row[0]);
        sourceAccountBranch = toInt(row[1]);
        mysql_free_result(res);

    }
    //--------------------------------------------------------------------
    sourceAccountBalance -= amount;
    {
        std::string query = "UPDATE `Account` SET account_balance = '" +
            std::to_string(sourceAccountBalance) + "' where account_ID = " + std::to_string(randomAccountNumSource) + ";";
        executeQuery(conn, query);

    }
    //--------------------------------------------------------------------
    commitTransaction(conn);
    g_lockTableAccount->unlockRecord(hashInt(randomAccountNumSource, g_numAccount), randomAccountNumSource);

i lock and unlock other records with the same pattern. but i feel there is something wrong with it because i think the pattern of running a sql command and committing it is not correct, but the problem is when i start transaction at the beginning of for loop and commit at the end this pattern ensue deadlocks because at any isolation level the database won't release the locks it acquired. how can i do this correctly and without getting deadlocks? is there anyway at all? how different isolation levels affect this problem?

Answer 1

In just MySQL, one would do this:

START TRANSACTION;
SELECT @bal := balance FROM acct WHERE id=123  FOR UPDATE;
SET @bal = @bal - @amount;
UPDATE acct SET balance = @bal WHERE id = 123;
COMMIT;

The FOR UPDATE declares the intention to change the row(s) found. This keeps other threads away -- not via deadlock, but via a delay.

I don't know what is added by lockRecord(). Maybe it is a replacement for FOR UPDATE. If so, it is over-kill since it grabs the row for longer than the MySQL code does.

I don't think transaction_isolation_level matter, but I am not sure.

A deadlock can occur when you grab multiple rows in a transaction while another transaction is grabbing the same rows in a different order. In some situations, the "gap" next to a row counts, and can participate in a deadlock.