CODEWITHSUNDEEP
mysqldatabaseconcurrencytransactionsisolation-level
Ed.
Ed.

Reputation: 43

Database concurrency

In my company we have a quiz. I can not pass the quiz which i will describe below. We have 3 transactions. Before these transactions select sum(b) from a returns 1000. in the table are not values with 10, 20 or 30. the isolation level is read committed. All 3 transactions are executed in parallel.

Transaction №1

select sum(b) from a;
commit;

Transaction №2

insert into a values (30);
insert into a values (20);
insert into a values (10);
commit;

Transaction №3

delete from a where b = 10;
delete from a where b = 20;
delete from a where b = 30;
commit;

what the first transaction will be returned? The first i noticed that there is no start transaction statement. Can you please explain me the result. I have answered 1010 but i was wrong.

the second quiz i also can not pass. i will describe it below. the same conditions but the isolation level is read uncommitted.

Transaction №1

start transaction;
select sum(b) from a;
commit;

Transaction №2

start transaction;
insert into a values (30);
insert into a values (20);
insert into a values (10);
commit;

Transaction №3

start transaction;
delete from a where b = 10;
delete from a where b = 20;
delete from a where b = 30;
commit;

I guess that the database is mysql (innodb) but it's not specified in the quiz.

Upvotes: 1

Views: 149

Answers (1)

aschoerk
aschoerk

Reputation: 3593

I assume each transaction is started only once and in parallel.

In the first quiz the connections will work in auto-commit mode. Therefore the results can be 1000, 1030, 1050, 1060, 1010. Because of the sequence of inserts and deletes 1020 and 1040 are not possible.

in the second quiz the database will isolate the complete transactions therefore the result can be 1000 or 1060.

Upvotes: 1

Related Questions