DBMS - COMMIT BEFORE SELECT QUERY

Question

I have 2 terminals in both of them i set autocommit= 0 in the first terminal i insert 5 rows and then i type commit it supposed now in the second terminal i can see the updates right? but this is not true i need to type commit in the second terminal before i type 'select * from table' why this is necessary can someone explain me?

`commit` command commits data for the session, two terminals - two different sessions, you have to commit in first terminal first to see updated data in second terminal — Iłya Bursov, Mar 07 '16 at 16:42
yes but in the first terminal i insert a couple of rows and then i pressed commit , it supposed in the second terminal to be able to see the previous changes..but to works i've made commit before i try to view the updated inforamtion — kkafkas, Mar 07 '16 at 16:45
I cannot understand how you can "press commit" in terminal, you have to "type" commit to actually apply changes — Iłya Bursov, Mar 07 '16 at 16:47
i have 2 terminals in both of them i set autocommit= 0 in the first terminal i insert 5 rows and then i type commit it supposed now in the second terminal i can see the updates right? but this is not true i need to type commit in the second terminal before i type 'select * from table' — kkafkas, Mar 07 '16 at 16:54
ah, I suggest you to fix wording in question then, as question `in the first terminal i insert a couple of rows and i've pressed commit in the second window` which means that you haven't executed commit in first terminal — Iłya Bursov, Mar 07 '16 at 16:57
you cannot send sql queries via ubuntu terminal, probably you're using mysql cli utils? — Iłya Bursov, Mar 07 '16 at 17:23
it is not of course, there millions of mysql clients, which can have their own bugs/features/caches — Iłya Bursov, Mar 07 '16 at 17:34
also it looks like your case https://bugs.mysql.com/bug.php?id=53211 — Iłya Bursov, Mar 07 '16 at 17:35
and this one looks like answer http://ilmarkerm.blogspot.com/2012/06/little-side-effect-of-having-autocommit.html — Iłya Bursov, Mar 07 '16 at 17:37

score 1 · Answer 1 · answered Mar 07 '16 at 17:39

1

it looks weird, but according to mysql bug and blog entry it is expected behavior with REPEATABLE READ isolation level, change level to READ COMMITTED to avoid this problem

set session transaction isolation level read committed;

answered Mar 07 '16 at 17:39

Iłya Bursov

23,342
4
33
57

didn't work.. same as before i need to make commit before i see the updated information – kkafkas Mar 07 '16 at 17:46
1

@Costas.K have you executed this statement in both terminals? – Iłya Bursov Mar 07 '16 at 17:46
dam it worked !! you goddamn righ, butt what exactly is the isolation level read? – kkafkas Mar 07 '16 at 17:54
http://stackoverflow.com/questions/4034976/difference-between-read-commit-and-repeatable-read --> ok thnk you very much – kkafkas Mar 07 '16 at 18:03

DBMS - COMMIT BEFORE SELECT QUERY

1 Answers1