Transaction isolation level - choosing the right one

Question

I'm a sql beginner and I need help concerning isolation levels of transactions. I need to know which isolation level is the best for the following situation and why:

There are 3 tables in the database:

• Animals (that are registered by inserting a chip into them) KEY - ID_CHIP REF CHIPS
• Chips (that can but dont have to be inserted into an animal) KEY - ID_CHIP. One of the attributes is "INSERTED_BY" which references to the third table PEOPLE (gives ID of a person who inserted the chip, and NULL if it wasnt inserted yet)
• People - KEY: ID

Now let's consider the following transactions: a new chip has been inserted into an animal. A person who updates the database has to change two things:

• add a new entity to ANIMALS
• update the chip record that was inserted (change the INSERTED_BY attribute from NULL to ID of a person who inserted the chip)

The second transaction is a controller transaction, who checks if the number of entities in ANIMALS is equal to the numer of CHIPS that have the attribute INSERTED_BY not equal to NULL. A situation is shown by the image below:

Can anyone tell me which of the fours isolation levels is best and why? I'm stuck here.. Any help would be appreciated.

Answer 1

Your situation is easy because one of the transactions a purely read transaction. Look into snapshot isolation. Running the reader under SNAPSHOT isolation level will give it a point-in-time consistent view of the entire database. No locks will be taken or waited on.

This means that at t2 the insert will not be visible to C2.

This is very easy to implement and solves the problem completely.

Without SNAPSHOT isolation you'd need SERIALIZABLE isolation and you'll deadlock a lot. Now you need to investigate locking hints. Much more complex, not necessary.