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.

I have a linked table id_group | id_user. Every time I add a one or more users to a group I DELETE all the links and INSERT the users again including the new one(s). This is how the framework of the application works.

From time to time some groups lose all the users they had, except the new ones, which are inserted.

The primary index of the table is the pair id_group - id_user.

I cannot reproduce this through a query, but the client has complained multiple times and there's an issue no doubt.

I could explain this if the table was innoDB with the DELETE locking the index and the INSERT not being able to add that pair again, however this is a myISAM table and myISAM locks the entire table during DELETE statements.

share|improve this question
    
Can you provide the exact DELETE query you use? –  Derek Downey Oct 5 '12 at 13:07
    
DELETE from users-user_groups where id_group = x –  Adrian Oct 15 '12 at 13:28
add comment

1 Answer 1

Is it possible you've got two processes attempting to do this at almost the same time? If this is a web application, could you have a site user who is double-click happy?

Process #1 "examine existing users"
Process #1 "remove all users"
Process #2 "examine existing users" (no users would found)
Process #1 "insert new users and put existing users back"
Process #2 "remove all users"
Process #2 "insert new users and put existing users back (but no existing users were found)"
share|improve this answer
    
This happens automatically, not as a direct user action. A user (or more) is assigned to a group, an object with all the users (old and new) is created, the links from the table are deleted for that user group, the object is parsed and the inserts are made. –  Adrian Oct 15 '12 at 13:31
1  
but what is the process that a user assigned to a group? –  Derek Downey Oct 15 '12 at 13:40
add comment

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.