1

I have a stored procedure that I am using that creates a temp table. Recently I've noticed the logs are filling up with locks. I think the temp table is trying to be replicated but I am unsure.

*** Victim TRANSACTION:                                                                                                                                                                                                                                       
TRANSACTION 0, ACTIVE 1 sec fetching rows
mysql tables in use 3, locked 3
MySQL thread id 1875027, OS thread handle 140436968662784, query id      41228278 dbserver 192.168.5.5 web_east Sending data
INSERT IGNORE into temp_table select ... from db.table as f inner join   data_temp temp on temp.id =f.id where f.date1 >= "2018-07-01 00:00:00" and f.date2 < "2019-08-01 00:00:00"

*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2272 page no 5986916 n bits 288 index   db_name_date_idx of table `db`.`table` trx id 421941145506800 lock mode S  locks gap before rec
2019-12-02T10:25:38.389502Z 10 [Note] WSREP: --------- CONFLICT DETECTED --------
2019-12-02T10:25:38.389506Z 10 [Note] WSREP: cluster conflict due to high  priority abort for threads: 
[Note] WSREP: Winning thread:                                                                                                                                                                                                        
THD: 10, mode: applier, state: executing, conflict: no conflict, seqno: 7653754
SQL: (null)  
0

Consider trying row based replication ( https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html ) in your case to prevent issues like yours. If your inserts are mostly complex queries, inserting only the resulting rows, rather than reexecuting the full queries may decrese locking and performance. Note this is highly application specific and may have other disadvantages in other queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.