Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is a homework question.

For the following transactions state the isolation level that will maximize throughput without lowering the integrity of the database. Explain the answer.

Change the course identified by coursed_id = ’CPSC1350’ from one department to a different one.

The Courses table contains information about courses: their id, their name, the department that offers it, the id of its instructor, and the maximum number of students who can take it (max_size). Courses(coursed_id: string, cname: string, dept: string, instructor_id: string, max_size:integer) – Primary Key: coursed_id – Foreign Key: instructor_id references Instructors

Assume that PostgreSQL is used.

I believe the transaction can be done using read committed because

  • dirty reads are not OK because there is an update going on, thus a read is involved
  • non-repeatable reads are OK because it is unlikely someone else is changing the value
  • phantoms are OK because there are no SELECT statements

Am I on the right track?

share|improve this question
1  
What is the schema of the relevant tables? –  Martin Smith Dec 9 '12 at 17:13
2  
"because there is nothing being read in this transaction" - an update has a read component - it needs to find the row(s) to update. Same for delete. –  Mat Dec 9 '12 at 17:38
    
I'm a little bit confused because non-repeatable reads seem to be a good thing but would slow down the database, but dirty reads and phantoms are generally considered a bad thing? I say non-repeatable reads are good because they get the most current data. –  Celeritas Dec 9 '12 at 20:05
    
Hint: Consider not just isolation, but locking. Think about what phenomena you're trying to protect against. What concurrent queries could be a problem? How? Remember, an UPDATE takes a row lock that prevents other updates affecting the row until the transaction commits or rolls back. Personally I suspect READ COMMITTED is entirely appropriate, but your reasoning as to why is faulty. –  Craig Ringer Dec 10 '12 at 1:13

1 Answer 1

up vote 0 down vote accepted

The question seems like a puzzle which appears very simple, but maybe not so simple ,or it just pretends to be complex... I'll try my best answering it as I understand it. I apologize if I misunderstood some obvious hints.

I haven't worked a lot with PostgreSQL, but from what I remember it handles transactions very similar to Oracle, so there is no real Read uncommitted - you get Read committed. The question doesn't sound very clear to me because isolation levels matter when you have simultaneous queries, and no other queries mentioned, so don't blame me too much if I miss something.

If we put remaining isolation levels in the order "Read committed"->"Repeatable read"->"Serializable", the overhead grows accordingly. So we need to check them in the same order, and once we are satisfied, there is no need to check remaining levels (they will be fine, but with more overhead). As far as I understand, UPDATE Courses set dept = 'New department' coursed_id = ’CPSC1350’ does the required work. From Postgre documentation that describes Read committed isolation level,

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

I'd say I'm satisfied with such behaviour in this case, so I'll go with READ COMMITTED unless there are other restrictions not mentioned in the question.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.