I currently have an event listener listening to a SQS queue. Once a message comes in, it gets processed and inserted into my Account table (MySql). This table is simple with only 4 columns. However sometimes when multiple events come in at the same time, I will have 2 inserts that occur at the exact same time (to the millisecond) which causes a deadlock issue.
Now I'm not sure how this deadlock issue occurs because the two inserts have completely different primary keys, maybe through index pages or something along those lines?
However when changing the transaction isolation level to Read_Committed from Repeatable_Read, it seems to resolve the issue. I am not sure how it does this, I know that locks are held for a shorter period with this isolation level so that may be it if someone can help enlighten me?
I tried changing the isolation levels which worked