An EXPLAIN on the SELECT might be a good place to start. Is rit_mdb_track_login_detail a table or a view?

Rhys

Von: Scott Canaan via discuss <discuss@lists.mariadb.org>
Gesendet: Freitag, 10. Mai 2024 16:34
An: discuss@lists.mariadb.org
Betreff: [MariaDB discuss] Deadlock Errors

Be aware: This is an external email.

We have a 3-node Galera cluster running MariaDB 10.5.23.  A couple months ago the cluster had an issue and one of my coworkers handled it.  We have several cron jobs that run against the databases to gather statistics and other information.  One of those jobs runs every 15 minutes.  Since the last issue, one SQL in that script has been throwing a deadlock error on two of the three nodes in the cluster:

ERROR 1213 (40001) at line 14: Deadlock found when trying to get lock; try restarting transaction

I’ve been monitoring and I don’t see the job running more than once at the same time on the same server.  The SQL that it is failing on is:

insert into rit_mdb_track_logins

select report_date, user, count(*) logins

from rit_mdb_track_login_detail

group by report_date, user;

I’ve run this SQL manually and it works fine.

I’m not sure where to go from here.

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

srcdco@rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.