Our application experiences occasional deadlocks when updating a record in a temporal table and then invoking a stored procedure that accesses that table in a temporal way.
The application is using EF Core 5 against SQL Server database and still runs on .NET Core 3.1.
The logic first updates the record, then invokes Update on its DbContext and then awaits a call to SaveChangesAsync on the same DbContext. Then the stored procedure accessing that table is invoked using ExecuteSqlInterpolatedAsync.
Based on database logging, it appears that both the save and the stored procedure are invoked in the same process, though it was expected that await on the save would ensure completion before the stored procedure is invoked.
This issue could not be reproduced in test environment.
Question: How can such deadlocks happen if all calls are awaited?