I am working on MariaDB.I have a scenario of 2 sessions working in parallel and understand their actions and things i get when i use SELECT COUNT(). However, in the last SELECT COUNT() i get 6 as a count, but i expected a 5. I believe my problem lies in the isolation levels set by the code.I know default isolation level is REPEATABLE READ and SET SESSION changes it for the whole session, while SET TRANsaction changes it just for the next transaction of the session it is declared. The last SET TRANSACTION is quite weird to me, because i believe before that isolation level has returned to repeatable read.
This is the table i create:
CREATE TABLE Accounts (acctID INTEGER NOT NULL PRIMARY KEY, balance
INTEGER NOT NULL, CONSTRAINT unloanable_account CHECK (balance >= 0));
INSERT INTO Accounts (acctID,balance) VALUES (101,1000);
INSERT INTO Accounts (acctID,balance) VALUES (202,2000);
SELECT * FROM Accounts;
COMMIT;
and the 2 sessions:
