(Step 3) Back to connection 1 and insert (why is it blocking?) (uniq identity: 341292662) INSERT INTO `tg_users` (uniq identity: 239302521) START TRANSACTION įROM `tg_users` WHERE `tg_user_id` = '239302521' FOR UPDATE (Step 2) Open Connection 2 and do select query. Keep connection 1 open, create connection 2. (uniq identity: 341292662) START TRANSACTION Reproduction Steps: - (Step 1) Open Connection 1 and do select query. (so I think the row lock is not relevant since they are working with different row). Connection 1 and connection 2 have different tg_user_id on its queries.Just to emphasize things that may be important to check: If the such data exists, then compare and update.If the user has not been stored in database, then insert it.Select user information from database based on tg_user_id.) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci KEY `private_msg_count` (`private_msg_count`), KEY `group_msg_count` (`group_msg_count`), `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `is_bot` enum('0','1') CHARACTER SET utf8 NOT NULL DEFAULT '0', `private_msg_count` bigint(20) unsigned NOT NULL DEFAULT '0', `group_msg_count` bigint(20) unsigned NOT NULL DEFAULT '0', `photo` bigint(20) unsigned DEFAULT NULL, `last_name` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL, `first_name` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL, `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `tg_user_id` bigint(20) unsigned NOT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, Below is the detailed information: DDL CREATE TABLE `tg_users` ( I was able to reproduce the deadlock in a simple scenario, but do not understand why does the deadlock happen. I tried to read the stack trace from my app and report from SHOW ENGINE INNODB STATUS. The error says SQLSTATE: Serialization failure: 1213 Deadlock found when trying to get lock try restarting transaction. I noticed that there are so many errors occur related to deadlock. So for now it appears manually locking the table is the only thing that works.I have a running app with concurrency transactions in it. WITH CONSISTENT SNAPSHOT and READ WRITE still gave me deadlock error, while READ ONLY naturally didn't even allow me to execute the INSERT. All 3 options required PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true) to be set. Is that the only way to get this to work, or can I with 100% certainty prevent deadlocks without having to manually lock the table?ġ) Putting the query in a transaction - still deadlock errorĢ) Starting the transaction with WITH CONSISTENT SNAPSHOT, READ WRITE, READ ONLY. With that method I ran the code 4 times simultaneously, each instance inserting 2500 users, without problems. Insert into `tt_users` (`state`, `state_position`)Īnd then: PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, false) Then locking the table manually, and running my query: SET autocommit=0 The only way I managed to get this to work, and work consistently, is this (language is PHP): PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true) I read this post - How to avoid mysql 'Deadlock found when trying to get lock try restarting transaction' but I didn't understand what I should do with my query to prevent deadlocks, if that is at all possible, since the answer to this question - Working around MySQL error "Deadlock found when trying to get lock try restarting transaction" - says deadlocks can happen no matter what. (SELECT MAX(state_position) AS maxStatePositionįor testing, I inserted a large amount of users simultaneously, and each time I got the deadlock error. This is the query I'm running that causes deadlocks: insert into `tt_users` (`state`, `state_position`) if I have 5 users with state 'x', their state_positions must be 1,2,3,4,5 State_positions must always be consecutive, and there may never be a duplicate position for a certain state, i.e. I have table tt_users which has id as primary key, and columns state (CHAR(1)), which can be "x" or "y", and state_position (INT).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |