Repository: n8n-io/n8n
File: packages/@n8n/db/src/repositories/workflow-dependency.repository.ts (TypeScript)

Code before:
			return await this.updateWithImmediateTransaction(workflowId, dependencies);
		}
		return await this.manager.transaction(async (tx) => {
			return await this.executeUpdate(workflowId, dependencies, tx);
		});
	}

	private async updateWithImmediateTransaction(
		workflowId: string,
		dependencies: WorkflowDependencies,
	): Promise<boolean> {
		// We use a query runner to have more control over the transaction.
		const queryRunner = this.manager.connection.createQueryRunner();
		await queryRunner.connect();

		try {
			// Set a busy_timeout - otherwise the query will fail immediately if the database is locked.
			// This enables retrying for a short period instead.
			await queryRunner.query('PRAGMA busy_timeout = 5000');

			// Start an immediate transaction FIRST to acquire a RESERVED lock.
			// NOTE: in the typical case where we're updating an existing workflow, this would happen
			// anyway when we delete the existing dependencies. We lock explicitly to make it clearer,
			// and ensure nothing weird happens when there are no existing dependencies.
			// NOTE: we use raw SQL here because TypeORM does not support specifying the transaction type.
			await queryRunner.query('BEGIN IMMEDIATE TRANSACTION');

			try {
				// Perform the update using queryRunner.manager
				const result = await this.executeUpdate(workflowId, dependencies, queryRunner.manager);

				await queryRunner.query('COMMIT');
				return result;
			} catch (error) {
				await queryRunner.query('ROLLBACK');
				throw error;
			}
		} finally {
			await queryRunner.release();
		}
	}

	private async executeUpdate(
		workflowId: string,
		dependencies: WorkflowDependencies,
		tx: EntityManager,
	): Promise<boolean> {
		const deleteResult = await tx.delete(WorkflowDependency, {
			workflowId,
			workflowVersionId: LessThan(dependencies.workflowVersionId),
		});

Review comment (refactor):
Our SQLite driver (the pooled one) actually does this already when you start a trx, and uses a semaphore to allow only single writer in at once. So we can use a normal typeorm trx and don't need the busy_timeout. In other words it doesn't need to be any different from the other DBs. The legacy driver is a different case so I guess we can leave this like this for now and clean up after v2

Code after:
		}
		return await this.manager.transaction(async (tx) => {
			return await this.executeUpdate(workflowId, dependencies, tx);
		});
	}

	private async executeUpdate(
		workflowId: string,
		dependencies: WorkflowDependencies,
		tx: EntityManager,
	): Promise<boolean> {
		const deleteResult = await tx.delete(WorkflowDependency, {
			workflowId,
			workflowVersionId: LessThan(dependencies.workflowVersionId),
		});

		// If we deleted something, the incoming version is newer - proceed with insert
		if (deleteResult.affected && deleteResult.affected > 0) {
			// NOTE: we cast to any[] because TypeORM doesn't like the JSON column.
			// eslint-disable-next-line @typescript-eslint/no-explicit-any
			await tx.insert(WorkflowDependency, dependencies.dependencies as any[]);
			return true;
		}

		// Nothing was deleted - either no existing data, or existing data is newer/same version
		// Check if any dependencies exist for this workflow. We lock for update to avoid a race
		// when two processes try to insert dependencies for the same workflow at the same time.
		const hasData = await this.acquireLockAndCheckForExistingData(workflowId, tx);

		if (!hasData) {
			// There's no existing data, so we can safely insert the new dependencies.
			const entities = dependencies.dependencies.map((dep) => this.create(dep));
			// NOTE: we cast to any[] because TypeORM doesn't like the JSON column.
			// eslint-disable-next-line @typescript-eslint/no-explicit-any
			await tx.insert(WorkflowDependency, entities as any[]);
			return true;
		}

		// Existing data has same or newer version - skip update
		return false;
	}

	/**
	 * Remove all dependencies for a given workflow.
	 *
	 * NOTE: there's a possible race in case of an update and delete happening concurrently.
	 * The delete could be reflected in the database, but the update could be reflected in the index.
	 * To prevent this we would need to implement some tombstone mechanism. However, since we don't
	 * do this for the workflow itself, it would be inconsistent to do it only for the dependencies.
	 * The chance of this happening in practice is also very low.
	 *