Table Locking Issues After Upgrading to Aurora MySQL 5.7

0

As MySQL 5.6 is nearing EOL support, we have upgraded one of the 5.6 Aurora serverless MySQL clusters to 5.7. From the moment we upgraded we faced a lot of issues. esp with Table locking related which we never faced in 5.6 all these years. Have tried changing Isolation levels, Disabling cache and lot more options last few days but none provided any useful outcomes. It still hangs with tables with very minimal data on UPDATE queries with PK in WHERE clause. Gap Locks, Deadlocks or Locks not handled when the Processes were cleaning up., something we couldnt' exactly find so far. Does anybody face similar issues., Kindly advice.

---TRANSACTION 960499483, ACTIVE 12 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 43, OS thread handle 47444799534848, query id 36684 10.1.28.175 admin updating UPDATE bulk_import_hist_tracking set records_count =IFNULL(nullif(1, ''), records_count), practiceid = 123, submission_status = 'test',s3_url = NULLIF('test', ''), updatedat=now() where target_resource_id = 1 ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 15857 page no 4 n bits 0 index PRIMARY of table embry_care_db.bulk_import_hist_tracking trx id 960499483 lock_mode X locks rec but not gap waiting Record lock, heap no 111 PHYSICAL RECORD: n_fields 15; compact format; info bits 0 0: len=4; bufptr=0x2b27454f67bb; hex= 80000001; asc ;; 1: len=6; bufptr=0x2b27454f67bf; hex= 000039400ccf; asc 9@ ;; 2: len=7; bufptr=0x2b27454f67c5; hex= 300000642917c5; asc 0 d) ;; 3: len=4; bufptr=0x2b27454f67cc; hex= 8000007b; asc {;; 4: len=7; bufptr=0x2b27454f67d0; hex= 44425f74657374; asc DB_test;; 5: SQL NULL; 6: len=3; bufptr=0x2b27454f67d7; hex= 8fcd8e; asc ;; 7: len=4; bufptr=0x2b27454f67da; hex= 80000001; asc ;; 8: len=2; bufptr=0x2b27454f67de; hex= 4442; asc DB;; 9: len=4; bufptr=0x2b27454f67e0; hex= 74657374; asc test;; 10: len=1; bufptr=0x2b27454f67e4; hex= 80; asc ;; 11: len=4; bufptr=0x2b27454f67e5; hex= 6399cc81; asc c ;; 12: len=4; bufptr=0x2b27454f67e9; hex= 63dce65b; asc c [;; 13: len=4; bufptr=0x2b27454f67ed; hex= 74657374; asc test;; 14: SQL NULL;

  • This happens usually due to open transaction, wherein the transaction are being not closed from the application properly, two ways to solve is either check the application to close all transactions . Check the timeout in application and set the wait time out properly to clear the stale open connection from db. The first most preferred since closing connection from db could cause lot of rollbacks

1 Answer
0
Accepted Answer

This seem to be caused by a procedure that had a bug in it, which was not closing the connections properly. Fixing that seem to solve this issue. Thank you all..

Vasanth
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions