Timeline for Mysql deadlock on non unique index
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 22, 2023 at 14:31 | comment | added | Rick James | Yes, add that 3-column Index. | |
| Oct 22, 2023 at 14:28 | comment | added | Rick James |
@AndreyKhataev - See INSERT ... ON DUPLICATE KEY UPDATE ... -- This does either an INSERT or UPDATE, as needed. (Also called "upsert".)
|
|
| Oct 22, 2023 at 10:13 | comment | added | Andrey Khataev |
Regarding UPDATE instead of DELETE+INSERT. Interesting idea, but since there could be the case when record does not exist, we need to use UPSERT. I need to figure out if this is possible in mysql. Still this looks like a workaround for database bug ))
|
|
| Oct 22, 2023 at 10:09 | comment | added | Andrey Khataev |
Do you mean to add index on (context_id, context_type, user_id) instead of user_id ? If yes - it won't be used if we query just by user_id ...
|
|
| Oct 17, 2023 at 23:08 | comment | added | Rick James |
Is the intent to replace the row(s) with 'id of inserted user', 'Trial', 'some constant id'? If so, use an UPDATE instead of DELETE + INSERT.
|
|
| Oct 17, 2023 at 23:06 | comment | added | Rick James |
@AndreyKhataev - If you are unwilling to change the PK, try adding ADD INDEX(context_id, context_type, user_id).
|
|
| Oct 12, 2023 at 22:04 | comment | added | Rick James |
I don't know whether the DELETE interferes; I was running out of ideas. Improving the index will at least help its speed.
|
|
| Oct 12, 2023 at 22:03 | comment | added | Rick James |
@AndreyKhataev - Drop those indexes and at the same time you build the new indexes; the FK definition should realize that the new keys are sufficient. Also, the Optimizer failed to use the longer index when the shorter one is there: INDEX(a), INDEX(a,b). If there is further trouble, DISABLE the FKs while changing the indexes, then re-enable.
|
|
| Oct 12, 2023 at 15:32 | comment | added | Andrey Khataev |
also I didn't still understand why DELETE from user_roles for user_id = X blocks INSERT into user_roles for user_id = Y. Your suggestions do not answer this question. (I'm still trying to understand how we read ID of inserted record, because it is done under the hood by ORM)
|
|
| Oct 12, 2023 at 15:26 | comment | added | Andrey Khataev |
unfortunately, we can't transform separate indexes index_user_roles_on_role_id/index_user_roles_on_user_id into composite, because they correspond to foreign keys and we want to have fast search by these keys. While query by user_id won't use index on (role_id, user_id).
|
|
| Oct 11, 2023 at 22:55 | history | answered | Rick James | CC BY-SA 4.0 |