![]() ![]() Tried computing Order on insert but I get the deadlock on insert now. I have a program that sends 20 items concurrently and not all of them are added to the database successfully because it deadlocks at the update statement.Ĭan anyone suggest me some ways to prevent the deadlock, or maybe a better design. Now the problem is, it works quiet well until I try adding lot of records concurrently. That is the reason why the insert sql doesn't calculate Order column and leaves it as null. For example for combination of key1=1 and key2=2, we might need to calculate the Order but not for combination key=1 and key2=3 and leave it null. ![]() Seems first transaction is waiting a lock and hold nothing. Here is the output of innodb status monitoring and code. The acquire method sometimes will throw DeadLock Exception. Between the insert and update, the C# code check whether or not the Order column needs to be calculated. Why mysql deadlock happened for select for update and insert Asked 641 times 0 I use peewee and mysql wrote a simple lock class. The above transaction is run using C# code. SELECT * FROM (SELECT COALESCE((SELECT MAX(`Order) INSERT INTO table_name VALUES (1, 2, 14, null) So, my SQL code for adding new item looks like this: START TRANSACTION I have to make sure that the 'Order' value is not duplicated for the items in the same group (items that have equal values for key1 and key2). It is all well and fine until we add items to the table concurrently. User can also reorder the items, which means I can modify the 'Order' column of (1, 2, 12) to have value of -10 and bring it on top of the list. Now if I have to add a new item (1, 2, 14) then the order must be greater than 30 so that the item is add the end of the list. So for example: key1 | key2 | ke圓 | order Everytime an item is added to the database, I have to make sure that the 'Order' column has the highest value (for the same group defined by key1, and key2). I have another column called 'Order' which keeps track of order of the items in database. I have a table with composite primary key made up of 3 columns: key1, key2 and ke圓 ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |