Are rows from dependent tables deleted in a single/general transaction?

Question

MySQL

On fingers. There are 2 tables connected by a relation:

CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ..
  PRIMARY KEY (`id`)
);

CREATE TABLE `post_comment` (
  `post_id` int(11) NOT NULL,
  ...
  FOREIGN KEY (`post_id`) REFERENCES `post`(`id`) ON DELETE CASCADE
);

When I do:
DELETE FROM `post` WHERE `id` = 1
Before actually deleting this entry, dependent lines from `post_comment` will be removed according to the foreign key rule.
The question is, will the rows from post and post_comment be internally locked in a SINGLE transaction, or will a separate transaction be used to delete from each dependent table (i.e. separately for post and separately for post_comment)?
PS Also of interest is the question of the differences between Postgres and InnoDB in this regard. Therefore, I will be grateful if you indicate in the answer the DBMS to which it refers.

Answer 1

postgres:
Actually, yes, FOR UPDATE row-level lock is always taken when delete is done on a row, reference does not affect this.
You can look at the loci like this.
From one psql console we do

BEGIN;
delete from post where id = 1;

- in one transaction we do delete (we understand that it has enough lock for everything that deletes)
From another console

BEGIN;
select * from post where id = 1 for update;

- this transaction is trying to grab a lock on the same line, it cannot and is waiting.
But what happens during a rollback / commit - it depends on the transaction isolation level.