Database Timeout

keira_huy發表於2024-04-07
Database

1. 遇到的錯誤

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.
 The timeout period elapsed prior to completion of the operation or the server is not responding.

2. 查詢原因

1) 命令超時&&連線超時

  • Connection timeout (15 seconds by default)
  • Command timeout (30 seconds by default)

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/timeout-expired-error

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-timeouts

2) CommandTimeout

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout

The time in seconds to wait for the command to execute. The default is 30 seconds.
等待命令執行的時間（以秒為單位）。預設值為 30 秒。

3. 解決方案

1) 當未設定任何value的時候

_dbContext.Database.GetCommandTimeout() == null

2) 設定 CommandTimeout（秒）

// during your context setup
_dbContext.Database.SetCommandTimeout(180);

或者

// 在 Startup.cs 的 ConfigureServices 裡面
// 新增 sqlServerOptions.CommandTimeout
services.AddDbContext<AppDbContext>(
	options => options.UseLazyLoadingProxies()
	.UseSqlServer(Configuration.GetConnectionString("AppConnectionString"),
sqlServerOptions => sqlServerOptions.CommandTimeout(180)));

3) 當設定Value之後

_dbContext.Database.GetCommandTimeout() == 180

相關文章