RECORD LOCKS space id 166 page no 4 n bits 120 index PRIMARY of table `my_database`.`property` trx id 38289 lock mode S locks rec but not gap SELECT id, namespace_id, name, type, parent_property_id, custom_scope_type, metadata, state, created_at, updated_at FROM property WHERE (namespace_id = '3' AND id = '3180') OR (namespace_id = '3' AND id = '1100') OR (namespace_id = '3' AND id = '3181') OR (namespace_id = '3' AND id = '3140') OR (namespace_id = '3' AND id = '3121') OR (namespace_id = '3' AND id = '3150') FOR UPDATE LOCK WAIT 9 lock struct(s), heap size 1128, 9 row lock(s), undo log entries 7 TRANSACTION 38289, ACTIVE 1 sec starting index read Is there a way to force FOR UPDATE to take directly an EXCLUSIVE lock which would have prevented this issue? *** (1) TRANSACTION: When I look at the logs, it seems like even though we used FOR UPDATE, the row was first acquired with a "SHARED" lock, and then for each request it tried to promote from "SHARED" to "EXCLUSIVE" lock, but then a deadlock happen because 2 requests already have a SHARED lock on it. SELECT id, namespace_id, name, type, parent_property_id, custom_scope_type, metadata, state, created_at, updated_at I thought using FOR UPDATE should serialize the request, forcing the second one to wait for the first one to finish and to start after, creating small contention, but avoiding at least deadlock. Our application performs multiple identical requests using SELECT … FOR UPDATE and ends-up deadlocking each other.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |