7

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: Situation

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

Simon
  • 2,643
  • 3
  • 40
  • 61
  • Can you explain why you need the second transaction? Reading all records within index/table-heap isn't a good/scalable solution. – Bogdan Sahlean Jan 11 '14 at 16:54
  • @BogdanSahlean it's an academic project where I have to simulate isolation level, and thats the example I've come up with. – Simon Jan 11 '14 at 17:31
  • @Simon good to know because that explains why you are not interested in snapshot isolation. In other words, you are not interested in a production-grade solution. What do you want to "simulate" exactly? – usr Jan 11 '14 at 19:37

1 Answers1

2

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.

usr
  • 168,620
  • 35
  • 240
  • 369
  • First of all thanks for your time! Im sorry, but I dont get it. I can choose between: READ UNCOMMITED, READ COMMITED, REPEATABLE READ and SERIALIZABLE. Which transaction should be SERIALIZABLE and what about the second one? Why? – Simon Jan 11 '14 at 15:05
  • First, read about snapshot isolation to recognize that it will solve your problem. It is a very nice tool to have. https://www.google.com/webhp?complete=1&hl=en#complete=1&hl=en&q=sql+server+snapshot+isolation and http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx You can use it using ADO.NET and `SET TRANSACTION ISOLATION LEVEL SNAPSHOT`.; C2 would have to be serializable so that the read set of the transaction is locked (this will deadlock in the schedule you have given, but at least it will not silently return wrong results). – usr Jan 11 '14 at 15:11
  • Thank you, and what about the second transaction? – Simon Jan 11 '14 at 15:17
  • Doesn't matter. The writes will X-lock under any isolation level and with any kind of locking hint. I'd use `SERIALIZABLE` as well because it seems you are mostly concerned with safety. Be aware of potential blocking and deadlocking issues. Also, I repeat: your workload *will* deadlock with a certain probability. – usr Jan 11 '14 at 16:23
  • @usr: Can you tell me just one scenario when these two transactions can block each other (deadlock) ? – Bogdan Sahlean Jan 11 '14 at 16:59
  • @BogdanSahlean I now realize it is not possible. This will never deadlock. Serializable will work. I still recommend using SNAPSHOT isolation because it makes it so easy to conclude that everything works. I had to mentally go through all possible schedules to conclude that no deadlock is possible. That's bad code because it makes me think. And it's fragile with respect to future changes. – usr Jan 11 '14 at 17:04