What is the difference between non-repeatable read and phantom read?
I have read the Isolation (database systems) article from Wikipedia, but I have a few doubts. In the below example, what will happen: the non-repeatable read and phantom read?
Transaction A
SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1
OUTPUT:
1----MIKE------29019892---------5000
Transaction B
UPDATE USERS SET amount=amount+5000 where ID=1 AND accountno=29019892;
COMMIT;
Transaction A
SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1
Another doubt is, in the above example, which isolation level should be used? And why?