database design – multiple inconsistent states and multiple deadlocks

Do these plural ‘states’ and ‘deadlocks’ mean that multiple inconsistent states and multiple deadlocks may occur? If they were singular (an inconsistent state, a deadlock) then would there be only one of them at a time, please?

If we do not use locking, or if we unlock data items too soon after
reading or writing them, we may get inconsistent states. On the
other hand, if we do not unlock a data item before requesting a lock
on another data item, deadlocks may occur.

Database System Concepts

operating systems – Different ways to avoid deadlocks

Cheers, so I was thinking of ways that we can implement, in order to avoid deadlocks in processes. While I am aware of some tactics, e.g. killing a process when we reach a deadlock, or using spooling, would there be any other viable solutions? For example, can we adjust a process to be executed if it urgent, and has higher priority for execution, or maybe execute a process which asks for the smallest amount of resources? Or even depending on the resource, we remove it from a process which has reached a deadlock and then re-use it by giving it again on the processes? Thanks

MySQL trigger deadlocks itself? – Database Administrators Stack Exchange

I have a MySQL 5.7.22 database using the InnoDB engine.

There’s a table respondents that has 6 associated triggers – three of them make CRUD operations on a completed_respondents table, and the other three do so on respondent_stats (shown below for their relevance).

I’ve been noticing some deadlock errors on the application logs (specifically, Deadlock found when trying to get lock; try restarting transaction in different parts of the codebase), and enabling deadlock printing on the DB yields things like the one below.

For what I understand, there are two different transactions mentioned (each of them executing a different trigger).

Transaction (1) is waiting on a lock:

(1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600376 lock_mode X locks rec but not gap waiting

But, as far as I understand, it doesn’t hold any lock (given the lack of a (1) HOLDS THE LOCK(S) section).

Transaction (2) does hold a lock:

(2) HOLDS THE LOCK(S):

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600374 lock_mode X locks rec but not gap

And it’s blocked waiting on a lock that, to me, seems to be exactly that very same one:

(2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600374 lock_mode X waiting

I’ve read answers stating that triggers run in the same transaction as the query that generated them (and that queries run in an implicit transaction by default), but I don’t understand why is the transaction trying to get the same lock again, and, as an extra, why can’t it get it (I’d expect the same transaction to be able to get the same lock of a second time) or why is this same lock been asked twice at all.


Full deadlock description

2020-06-18T21:35:34.027779Z 566 (Note) InnoDB: Transactions deadlock detected, dumping detailed information.
2020-06-18T21:35:34.027808Z 566 (Note) InnoDB:
*** (1) TRANSACTION:

TRANSACTION 22600376, ACTIVE 1 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 577, OS thread handle 140402000619264, query id 108941776 10.42.205.242 surveda updating
UPDATE respondent_stats
     SET `count` = `count` - 1
   WHERE survey_id = OLD.survey_id
     AND questionnaire_id = IFNULL(OLD.questionnaire_id, 0)
     AND state = OLD.state
     AND disposition = OLD.disposition
     AND quota_bucket_id = IFNULL(OLD.quota_bucket_id, 0)
     AND mode = IFNULL(OLD.mode, '')
2020-06-18T21:35:34.027839Z 566 (Note) InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600376 lock_mode X locks rec but not gap waiting
Record lock, heap no 49 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 0000000000000047; asc        G;;
 1: len 4; hex 8000005d; asc    );;
 2: len 6; hex 616374697665; asc active;;
 3: len 9; hex 636f6e746163746564; asc contacted;;
 4: len 4; hex 80000000; asc     ;;
 5: len 7; hex 5b22697672225d; asc ("ivr");;
 6: len 6; hex 00000158dab6; asc    X  ;;
 7: len 7; hex 32000001e72445; asc 2    $E;;
 8: len 4; hex 8000030c; asc     ;;

2020-06-18T21:35:34.028070Z 566 (Note) InnoDB: *** (2) TRANSACTION:

TRANSACTION 22600374, ACTIVE 1 sec inserting
mysql tables in use 3, locked 3
7 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 566, OS thread handle 140402422155008, query id 108941778 10.42.205.242 surveda update
INSERT INTO respondent_stats(survey_id, questionnaire_id, state, disposition, quota_bucket_id, mode, `count`)
  VALUES (NEW.survey_id, IFNULL(NEW.questionnaire_id, 0), NEW.state, NEW.disposition, IFNULL(NEW.quota_bucket_id, 0), IFNULL(NEW.mode, ''), 1)
  ON DUPLICATE KEY UPDATE `count` = `count` + 1
2020-06-18T21:35:34.028092Z 566 (Note) InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600374 lock_mode X locks rec but not gap
Record lock, heap no 49 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 0000000000000047; asc        G;;
 1: len 4; hex 8000005d; asc    );;
 2: len 6; hex 616374697665; asc active;;
 3: len 9; hex 636f6e746163746564; asc contacted;;
 4: len 4; hex 80000000; asc     ;;
 5: len 7; hex 5b22697672225d; asc ("ivr");;
 6: len 6; hex 00000158dab6; asc    X  ;;
 7: len 7; hex 32000001e72445; asc 2    $E;;
 8: len 4; hex 8000030c; asc     ;;

2020-06-18T21:35:34.028363Z 566 (Note) InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 325 page no 8 n bits 192 index PRIMARY of table `surveda`.`respondent_stats` trx id 22600374 lock_mode X waiting
Record lock, heap no 49 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 0000000000000047; asc        G;;
 1: len 4; hex 8000005d; asc    );;
 2: len 6; hex 616374697665; asc active;;
 3: len 9; hex 636f6e746163746564; asc contacted;;
 4: len 4; hex 80000000; asc     ;;
 5: len 7; hex 5b22697672225d; asc ("ivr");;
 6: len 6; hex 00000158dab6; asc    X  ;;
 7: len 7; hex 32000001e72445; asc 2    $E;;
 8: len 4; hex 8000030c; asc     ;;

2020-06-18T21:35:34.028593Z 566 (Note) InnoDB: *** WE ROLL BACK TRANSACTION (1)

respondent_stats-related triggers

CREATE TRIGGER respondents_ins
AFTER INSERT ON respondents
FOR EACH ROW
BEGIN
  INSERT INTO respondent_stats(survey_id, questionnaire_id, state, disposition, quota_bucket_id, mode, `count`)
  VALUES (NEW.survey_id, IFNULL(NEW.questionnaire_id, 0), NEW.state, NEW.disposition, IFNULL(NEW.quota_bucket_id, 0), IFNULL(NEW.mode, ''), 1)
  ON DUPLICATE KEY UPDATE `count` = `count` + 1;
END;

CREATE TRIGGER respondents_del
AFTER DELETE ON respondents
FOR EACH ROW
BEGIN
  UPDATE respondent_stats
      SET `count` = `count` - 1
    WHERE survey_id = OLD.survey_id
      AND questionnaire_id = IFNULL(OLD.questionnaire_id, 0)
      AND state = OLD.state
      AND disposition = OLD.disposition
      AND quota_bucket_id = IFNULL(OLD.quota_bucket_id, 0)
      AND mode = IFNULL(OLD.mode, '');
END;

CREATE TRIGGER respondents_upd
AFTER UPDATE ON respondents
FOR EACH ROW
BEGIN
  UPDATE respondent_stats
      SET `count` = `count` - 1
    WHERE survey_id = OLD.survey_id
      AND questionnaire_id = IFNULL(OLD.questionnaire_id, 0)
      AND state = OLD.state
      AND disposition = OLD.disposition
      AND quota_bucket_id = IFNULL(OLD.quota_bucket_id, 0)
      AND mode = IFNULL(OLD.mode, '');

  INSERT INTO respondent_stats(survey_id, questionnaire_id, state, disposition, quota_bucket_id, mode, `count`)
  VALUES (NEW.survey_id, IFNULL(NEW.questionnaire_id, 0), NEW.state, NEW.disposition, IFNULL(NEW.quota_bucket_id, 0), IFNULL(NEW.mode, ''), 1)
  ON DUPLICATE KEY UPDATE `count` = `count` + 1;
END;

transaction – Avoid deadlocks in PostgreSQL with multiple clients

I am developing software that launches multiple simultaneous clients to connect to a PostgreSQL database (12). When each client starts up, the first thing it does when connecting to PostgreSQL is to run the schema creation script.

This script was written in an idempotent way – at least in principle – so that the multiple clients do not have to trip over themselves. Basically, it works well. However, PostgreSQL sometimes detects deadlocks and crashes of affected customers. Looking through the logging, I think these happen in such a sequence:

  1. Client A: start the schema creation transaction
  2. Client A: complete the schema creation transaction
  3. Client B: start the schema creation transaction
  4. Client A: new transaction using the schema (select in the view)
    • Customers A and B are now at an impasse

The logs are not 100% clear and I cannot reproduce that deterministically, but that seems to be what is happening: Client A is trying to SELECT from a view defined by the schema, but it is a blockage because client B tries to recreate this view (CREATE OR REPLACE VIEW) in the schema script.

Is there any way to make sure that the schema creation script runs exclusively? Or, is there another solution (for example, rather than CREATE OR REPLACE VIEW, I only CREATE VIEW once I have determined that it does not already exist)?