Race-Proof Cron Sweep: Suspending Services Safely

When a customer's payment fails, your automation has to suspend their service. But if your cron job runs while another process is updating the same records, you risk suspending a customer who just paid, or missing one who didn't. This race condition can cost you revenue and trust. Here's how to design a sweep that touches only the right rows, every time.

Why do cron sweeps for payment failures need race-condition protection?

Race conditions happen when two processes read and write the same data at the same time. In billing, a common scenario is a cron job that checks for overdue invoices and suspends services, while a webhook from a payment gateway marks an invoice as paid. If the cron reads the invoice as unpaid just before the webhook updates it, it may suspend a customer who has actually paid. This leads to angry customers and support tickets.

To avoid this, you need to design your query pattern so that the check and the action are atomic, or at least protected against concurrent modifications.

What is the core pattern for a race-condition-proof sweep?

The core idea is to use a conditional update that only affects rows in a certain state, and to claim the row before acting on it. In SQL, you can do this with an UPDATE ... WHERE status = 'overdue' that sets a status like 'suspending' and returns the affected rows. This way, only one process can claim a row at a time, because the update changes the status, and other processes will not see it as 'overdue' anymore.

For example, instead of:

SELECT * FROM services WHERE payment_status = 'overdue';
-- then suspend each service

You do:

UPDATE services SET status = 'suspending' WHERE payment_status = 'overdue' AND status = 'active' RETURNING id;

This update atomically claims the rows. Only the rows that were updated are returned, and they are now in a state that prevents other processes from claiming them again.

How do you handle the actual suspension after claiming rows?

After you claim the rows, you perform the suspension action, like calling your provisioning API to stop the service. But what if the suspension fails? You need a way to retry or roll back. One approach is to have a 'suspension_attempts' counter and a 'next_retry_at' timestamp. If the suspension fails, you increment the counter and set a retry time. If it succeeds, you set the status to 'suspended'.

If the process crashes after claiming but before suspending, you need a timeout. For instance, set a 'claimed_at' timestamp and in your sweep, also look for rows that are 'suspending' but have been claimed for more than a few minutes. Those can be retried or reset to 'overdue'.

What are the common pitfalls in this pattern?

One pitfall is using a separate SELECT then UPDATE, which breaks the atomicity. Another is not using transactions properly. If you use a transaction, you must ensure the isolation level is appropriate. For most databases, the default is fine if you use the conditional update.

Another pitfall is not including all relevant conditions in the WHERE clause. For example, if you only check payment_status, but a customer might have multiple services, you could suspend a service that is not the one with the overdue invoice. You need to join with the invoice or order table to ensure you are suspending the correct service.

How do you test for race conditions in your sweep?

Testing is crucial. You can write integration tests that simulate concurrent updates. For example, you can have a test that starts a transaction that claims a row, then tries to claim it again from another connection, and asserts that the second claim returns no rows. You can also use tools like `pgbench` for PostgreSQL or `sysbench` for MySQL to simulate load.

But a simpler approach is to write a test that runs the sweep function and at the same time updates a row to 'paid' from a different connection, and then checks that the service was not suspended. This can be done with a script that runs both operations in parallel.

What are the best practices for scheduling the cron sweep?

Schedule the sweep at a time when your system is least busy, but also consider the billing cycle. For example, if you send invoices on the 1st, you might run the sweep on the 5th to give customers a grace period. Use a cron expression that runs every few minutes, but ensure that the sweep is idempotent, meaning it can run multiple times without causing harm.

Also, consider using a distributed lock if you have multiple application servers. You don't want two servers running the same sweep at the same time. Tools like Redis or database-based locks can help.

How does Teculiar handle this?

Teculiar, a platform for hosting and domain resellers, incorporates these patterns in its billing automation. When you use Teculiar's automation features, you can trust that the suspension logic is race-condition-proof. But if you are building your own, follow the patterns above.

For more advanced scenarios, you might want to use a queue system where you enqueue suspension tasks and have workers process them, but the core principle remains: claim the row atomically before acting.

What to do next

• Review your current sweep queries and identify any that use separate SELECT and UPDATE.
• Rewrite them using conditional UPDATE ... RETURNING to claim rows atomically.
• Add a retry mechanism with a counter and timeout for failed suspensions.
• Write tests that simulate concurrent access to ensure your sweep is safe.

Once you have a solid pattern, you can apply it to other automation tasks like domain expiry checks or resource overage billing.