What kind of locking/transaction isolation level is appropriate for this situation?

Let's say I have a Student and a School table. One operation that I am performing is this:

• Delete all Students that belong to a School
• Modify the School itself (maybe change the name or some other field)
• Add back a bunch of students

I am not concerned about this situation: Two people edit the School/Students at the same time. One submits their changes. Shortly afte开发者_如何转开发r, someone else submits their changes. This won't be a problem because, in the second user's case, the application will notice that they are attempting to overwrite a new revision.

I am concerned about this: Someone opens the editor for the Schools/Students (which involves reading from the tables) while at the same time a transaction that is modifying them is running.

So basically, a read should not be able to run while a transaction is modifying the tables. Additionally, a write shouldn't be able to occur at the same time either.

Only in serializable isolation level MySQL won't allow you to read the rows that are being modified by another transaction. In any lower isolation level, you will see the rows in the state they were before the transaction, that modifies them, have been started. Of course, in READ_UNCOMITTED, the rows will be seen as deleted / modified, although transaction hasn't been completed.

If you use select for update,

You can use locking of tables to prevent this. Check this for more info on lock tables

EDIT

Have a look at this how to lock some row as they don't be selected in other transaction . Think a similar method can be applied for tables also