Theory about the deadlocks: I don't have a lot of background in MySQL but here goes. This avoids the dependency on the cron job and allows you to reschedule it to run less often. Select userid from onlineusers where datetime <= now - interval 900 second ĭelete from onlineusers where userid in (select userid from deletetemp) īreaking it up like this is less efficient but it avoids the need to hold a key-range lock during the delete.Īlso, modify your select queries to add a where clause excluding rows older than 900 seconds. You might try having that delete job operate by first inserting the key of each row to be deleted into a temp table like this pseudocode create temporary table deletetemp (userid int) If table lock happen, the lock contention is higher and the likelihood of deadlock increases. Be sure then to always have index on the foreign keys (and of course primary keys), otherwise it could result in a table lock instead of a row lock. It might result in other lock being acquired than the row that is modified. To do so, the database needs to check the foreign keys in the related tables. When a row is inserted/update/delete, the database needs to check the relational constraints, that is, make sure the relations are consistent. always table A first, then table B).Īnother reason for deadlock in database can be missing indexes. Generally speaking, try to acquire lock always in the same order even in different transaction (e.g. To avoid deadlock, you must then make sure that concurrent transactions don't update row in an order that could result in a deadlock. Each time you insert/update/or delete a row, a lock is acquired. There are numerous questions and answers about deadlocks. (Say, 3 retries on this particular error before giving up).ĭeadlock happen when two transactions wait on each other to acquire a lock. you can add this logic to your client code. WHERE datetime <= now() - INTERVAL 900 SECONDĪnother thing to keep in mind is that mysql documentation suggest that in case of a deadlock the client should retry automatically. if you do (and I suspect you do), order their WHERE in (k1,k2.kn) in ascending order.įix your delete statement to work in ascending order: Make sure you have no other queries that lock access more than one key at a time except for the delete statement. connection 2: locks key( 1), locks key( 2).Now, if you changed your queries such that the connections would lock the keys at the same order, ie: If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock. connection 2: locks key(2), locks key(1).connection 1: locks key(1), locks key(2).You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie: Wen cache is cleared using Drush on website on the frontend also.One easy trick that can help with most deadlocks is sorting the operations in a specific order. While using drupal admin interface for making updates or inserts website admin often gets error below.Ģ. Node3 website communication to MariaDB on node3 (localhost)ġ. Node2 website communication to MariaDB on node2 (localhost)Ĭ. Node1 website communication to MariaDB on node1 (localhost)ī. HTTP Traffic -> HAPROXY Load Balancer -> (one of the 3 Nodes)Ī. So, when a visitor hits the website, traffic flow is : The website files on main location are replicated between the nodes using lsyncd and csync2. Likewise, node2 read/writes to node2’s DB and so on. So this means when node1 is serving a page, it is reading/writing to the DB on node1. The website is actually a 3-node cluster behind a load balancer.Įach node (node1,node2 and node3) are part of a MariaDB Galera Cluster, and each node is hosting the Drupal website.Įach drupal website communicates with it’s own DB. Here is more detail about site configuration on server :. Whenever updates are made to the site cache is cleared from all 3 instances / nodes for changes to work properly esp. The site setup is based on 3 instances of website setup or file system using one database. I am working on Drupal 8.2.0-dev for website on linux server with MariaDB as database server.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |