CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008sql-server-2005
programming idiot
programming idiot

Reputation: 145

Does setting serializable make 2 select statements atomic? (no change in tables between the 2 selects)

I want to select from both tables using a 2 select statements. But it is possible another user may change the 2nd table while the 1st table is selected

If the isolation level is set to serializable, does it guarantee the 2nd table does not change while the first table is being selected?

Example: 

Select * from Burgers where ID = 299 -- returns 1 rows

Select * from BurgerIngredients where BurgerID = 299 -- returns many rows

If BurgerIngredients may change while Burger is being retrieved, what can I do to make sure the ingredients stay the same until they are retrieved also?

Thanks

Upvotes: 1

Views: 143

Answers (1)

rsanchez
rsanchez

Reputation: 14657

According to the documentation for MySQL, yes. You just need to make sure that both are InnoDB tables and that both SELECTs occur inside the same transaction:

START TRANSACTION;
Select * from Burgers where ID = 299;
Select * from BurgerIngredients where BurgerID = 299;
COMMIT;

You don't need to set isolation level to SERIALIZABLE, the default REPEATABLE READ is enough for this to happen.

All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

Note that this doesn't prevent other processes from writing to the tables, it's just that this transaction won't see the changes.

EDIT:

Now I see you also tagged the question with MS SQL server. In that case, the same principle holds if you specify transaction isolation level SNAPSHOT.

Upvotes: 1

Related Questions