반응형
Sqlite DB가 Azure Dotnet 핵심 엔터티 프레임워크에서 잠김
Linux에서 B1(B2) Azure App Service에 배포한 간단한 asp.net 코어 웹 앱(v2.1)이 있습니다.내가 전화할 때dbContext.SaveChanges()매우 간단한 엔터티 하나를 추가한 후 요청이 다음 오류를 발생시키기 전에 약 30초가 걸립니다.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
여기 코드가 있습니다._dbContext는 범위 수명으로 주입됩니다.
public async Task<IActionResult> SignIn([Bind("Email,Password,RedirectUrl")] SignInModel model) {
if (ModelState.IsValid) {
var user = _dbContext.Users.Include(u => u.Claims).FirstOrDefault(u => u.UserName == model.Email);
...
user.LastLogin = DateTimeOffset.Now;
await _dbContext.SaveChangesAsync();
...
return Redirect(String.IsNullOrWhiteSpace(model.RedirectUrl) ? "/" : model.RedirectUrl);
}
else {
return View(model);
}
}
30초 동안 SSH를 통해 SQLite DB 파일 옆에 저널 파일이 있다는 것을 알게 되었습니다.결국 삭제됩니다.
업데이트: 로그입니다.한 번의 업데이트 호출 후 잠금 예외가 정확히 30초 후에 느려지는 것을 볼 수 있습니다.30초는 SQL 명령 시간 초과입니다.원격 SSH 쉘을 사용하여 파일 시스템을 보고 있는데, 저널 파일이 최대 30초 동안 있습니다.이는 앱 서비스에서 사용하는 네트워크 공유와 SQLite 파일 잠금 로직의 조합이 깨지거나 매우 느린 것과 같습니다.
2018-12-20T15:06:27.660624755Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
2018-12-20T15:06:27.660656156Z Request starting HTTP/1.1 POST http://insecuresite.azurewebsites.net/Account/SignIn application/x-www-form-urlencoded 56
2018-12-20T15:06:27.660797960Z [15:06:27 DBG] InsecureSite.Middleware.MyCookieAuthHandler:Constructor called.
2018-12-20T15:06:27.660875561Z [15:06:27 DBG] InsecureSite.Middleware.MyCookieAuthHandler:Constructor called.
2018-12-20T15:06:27.660885462Z [15:06:27 DBG] InsecureSite.Middleware.MyCookieAuthHandler:HandleAuthenticateAsync called.
2018-12-20T15:06:27.660890662Z [15:06:27 DBG] InsecureSite.Middleware.MyCookieAuthHandler:HandleAuthenticateAsync called.
2018-12-20T15:06:27.661837484Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
2018-12-20T15:06:27.661856585Z Route matched with {action = "SignIn", controller = "Account"}. Executing action InsecureSite.Controllers.AccountController.SignIn (InsecureSite)
2018-12-20T15:06:27.662465200Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
2018-12-20T15:06:27.662478400Z Executing action method InsecureSite.Controllers.AccountController.SignIn (InsecureSite) with arguments (InsecureSite.Models.SignInModel) - Validation state: Valid
2018-12-20T15:06:27.667736726Z [40m[32minfo[39m[22m[49m: Microsoft.EntityFrameworkCore.Infrastructure[10403]
2018-12-20T15:06:27.667751427Z Entity Framework Core 2.1.4-rtm-31024 initialized 'AppDbContext' using provider 'Microsoft.EntityFrameworkCore.Sqlite' with options: None
2018-12-20T15:06:27.716864407Z [40m[32minfo[39m[22m[49m: Microsoft.EntityFrameworkCore.Database.Command[20101]
2018-12-20T15:06:27.716886507Z Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
2018-12-20T15:06:27.716892507Z PRAGMA foreign_keys=ON;
2018-12-20T15:06:27.776374136Z [40m[32minfo[39m[22m[49m: Microsoft.EntityFrameworkCore.Database.Command[20101]
2018-12-20T15:06:27.776410837Z Executed DbCommand (59ms) [Parameters=[@__model_Email_0='?' (Size = 18)], CommandType='Text', CommandTimeout='30']
2018-12-20T15:06:27.776514640Z SELECT "u"."UserId", "u"."FirstName", "u"."LastLogin", "u"."LastName", "u"."PasswordHash", "u"."UserName"
2018-12-20T15:06:27.776526140Z FROM "Users" AS "u"
2018-12-20T15:06:27.776531140Z WHERE "u"."UserName" = @__model_Email_0
2018-12-20T15:06:27.776536040Z ORDER BY "u"."UserId"
2018-12-20T15:06:27.776540740Z LIMIT 1
2018-12-20T15:06:27.778553489Z [40m[32minfo[39m[22m[49m: Microsoft.EntityFrameworkCore.Database.Command[20101]
2018-12-20T15:06:27.778567689Z Executed DbCommand (1ms) [Parameters=[@__model_Email_0='?' (Size = 18)], CommandType='Text', CommandTimeout='30']
2018-12-20T15:06:27.778840096Z SELECT "u.Claims"."UserClaimId", "u.Claims"."Claim", "u.Claims"."UserId", "u.Claims"."Value"
2018-12-20T15:06:27.778852696Z FROM "UserClaims" AS "u.Claims"
2018-12-20T15:06:27.778857796Z INNER JOIN (
2018-12-20T15:06:27.778862596Z SELECT "u0"."UserId"
2018-12-20T15:06:27.778869696Z FROM "Users" AS "u0"
2018-12-20T15:06:27.778874396Z WHERE "u0"."UserName" = @__model_Email_0
2018-12-20T15:06:27.778879897Z ORDER BY "u0"."UserId"
2018-12-20T15:06:27.780228429Z LIMIT 1
2018-12-20T15:06:27.780242129Z ) AS "t" ON "u.Claims"."UserId" = "t"."UserId"
2018-12-20T15:06:27.780247829Z ORDER BY "t"."UserId"
2018-12-20T15:06:27.789636555Z [40m[32minfo[39m[22m[49m: Microsoft.EntityFrameworkCore.Database.Command[20101]
2018-12-20T15:06:27.789651955Z Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
2018-12-20T15:06:27.789657656Z PRAGMA foreign_keys=ON;
2018-12-20T15:06:27.794111763Z [40m[32minfo[39m[22m[49m: Microsoft.EntityFrameworkCore.Database.Command[20101]
2018-12-20T15:06:27.794126763Z Executed DbCommand (4ms) [Parameters=[@p1='?', @p0='?'], CommandType='Text', CommandTimeout='30']
2018-12-20T15:06:27.794132363Z UPDATE "Users" SET "LastLogin" = @p0
2018-12-20T15:06:27.794280267Z WHERE "UserId" = @p1;
2018-12-20T15:06:27.794298667Z SELECT changes();
2018-12-20T15:06:57.833069471Z [41m[30mfail[39m[22m[49m: Microsoft.EntityFrameworkCore.Database.Transaction[20205]
2018-12-20T15:06:57.833107571Z An error occurred using a transaction.
2018-12-20T15:06:57.833113572Z Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
2018-12-20T15:06:57.833118772Z at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
2018-12-20T15:06:57.833123772Z at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
2018-12-20T15:06:57.833128672Z at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
2018-12-20T15:06:57.833133672Z at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText)
2018-12-20T15:06:57.833138672Z at Microsoft.Data.Sqlite.SqliteTransaction.Commit()
2018-12-20T15:06:57.833143372Z at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Commit()
2018-12-20T15:06:57.853805669Z [41m[30mfail[39m[22m[49m: Microsoft.EntityFrameworkCore.Update[10000]
2018-12-20T15:06:57.853833569Z An exception occurred in the database while saving changes for context type 'InsecureSite.Data.AppDbContext'.
2018-12-20T15:06:57.853928072Z Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
2018-12-20T15:06:57.853938272Z at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
2018-12-20T15:06:57.853943572Z at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
2018-12-20T15:06:57.854041474Z at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
2018-12-20T15:06:57.854051475Z at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText)
2018-12-20T15:06:57.854056675Z at Microsoft.Data.Sqlite.SqliteTransaction.Commit()
2018-12-20T15:06:57.854137377Z at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Commit()
2018-12-20T15:06:57.854146577Z at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
2018-12-20T15:06:57.854208178Z at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
2018-12-20T15:06:57.854283180Z at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
2018-12-20T15:06:57.854292080Z at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
2018-12-20T15:06:57.854299081Z Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
2018-12-20T15:06:57.854366282Z at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
2018-12-20T15:06:57.854384483Z at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
2018-12-20T15:06:57.854389683Z at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
2018-12-20T15:06:57.854455384Z at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText)
2018-12-20T15:06:57.854463685Z at Microsoft.Data.Sqlite.SqliteTransaction.Commit()
2018-12-20T15:06:57.854468185Z at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Commit()
2018-12-20T15:06:57.854529686Z at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
2018-12-20T15:06:57.854652489Z at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
2018-12-20T15:06:57.854673890Z at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
2018-12-20T15:06:57.854748391Z at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
2018-12-20T15:06:57.858109772Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
2018-12-20T15:06:57.858123973Z Executed action InsecureSite.Controllers.AccountController.SignIn (InsecureSite) in 30193.6715ms
2018-12-20T15:06:57.860885139Z [41m[30mfail[39m[22m[49m: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[1]
2018-12-20T15:06:57.860899939Z An unhandled exception has occurred while executing the request.
2018-12-20T15:06:57.860905239Z Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
2018-12-20T15:06:57.861009242Z at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
2018-12-20T15:06:57.861018942Z at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
2018-12-20T15:06:57.861023842Z at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
2018-12-20T15:06:57.861120545Z at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText)
2018-12-20T15:06:57.861130145Z at Microsoft.Data.Sqlite.SqliteTransaction.Commit()
2018-12-20T15:06:57.861134745Z at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Commit()
2018-12-20T15:06:57.861237547Z at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
2018-12-20T15:06:57.861311249Z at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
2018-12-20T15:06:57.861320149Z at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
2018-12-20T15:06:57.861392851Z at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
2018-12-20T15:06:57.861401851Z at InsecureSite.Controllers.AccountController.SignIn(SignInModel model) in /home/site/repository/InsecureSite/Controllers/AccountController.cs:line 57
2018-12-20T15:06:57.861463253Z at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
2018-12-20T15:06:57.861472053Z at System.Threading.Tasks.ValueTask`1.get_Result()
2018-12-20T15:06:57.861541855Z at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
2018-12-20T15:06:57.861550055Z at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()
2018-12-20T15:06:57.861554655Z at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
2018-12-20T15:06:57.861629257Z at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
2018-12-20T15:06:57.861639057Z at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()
2018-12-20T15:06:57.861718659Z at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
2018-12-20T15:06:57.861727059Z at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
2018-12-20T15:06:57.861791861Z at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
2018-12-20T15:06:57.861800861Z at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
2018-12-20T15:06:57.861805561Z at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
2018-12-20T15:06:57.861810161Z at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
2018-12-20T15:06:57.861880363Z at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
2018-12-20T15:06:57.861888363Z at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
2018-12-20T15:06:57.861948164Z at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)
2018-12-20T15:06:57.862056667Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
2018-12-20T15:06:57.862066767Z Route matched with {action = "Error", controller = "Home"}. Executing action InsecureSite.Controllers.HomeController.Error (InsecureSite)
2018-12-20T15:06:57.867899207Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
2018-12-20T15:06:57.867914108Z Executing action method InsecureSite.Controllers.HomeController.Error (InsecureSite) - Validation state: Valid
2018-12-20T15:06:57.868025910Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
2018-12-20T15:06:57.868045011Z Executed action method InsecureSite.Controllers.HomeController.Error (InsecureSite), returned result Microsoft.AspNetCore.Mvc.ViewResult in 0.0771ms.
2018-12-20T15:06:57.868147613Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Mvc.ViewFeatures.ViewResultExecutor[1]
2018-12-20T15:06:57.868157914Z Executing ViewResult, running view Error.
2018-12-20T15:06:57.869182938Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Mvc.ViewFeatures.ViewResultExecutor[4]
2018-12-20T15:06:57.869196139Z Executed ViewResult - view Error executed in 7.5623ms.
2018-12-20T15:06:57.869201439Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
2018-12-20T15:06:57.869206339Z Executed action InsecureSite.Controllers.HomeController.Error (InsecureSite) in 7.9125ms
2018-12-20T15:06:57.869222639Z [40m[32minfo[39m[22m[49m: Microsoft.AspNetCore.Hosting.Internal.WebHost[2]
2018-12-20T15:06:57.869228039Z Request finished in 30208.5835ms 500 text/html; charset=utf-8
2018-12-20T15:08:44 No new trace in the past 1 min(s).
이 웹 앱에 대한 다른 요청이 없습니다.나만 요청합니다.
컨트롤러는 오류 없이 DB 파일에서 데이터를 읽을 수 있습니다.쓰기 실패 직전에 몇 가지 쿼리가 3ms와 6ms가 소요되는 것을 알 수 있습니다.
잠금과 여러 스레드에 문제가 있는 것을 보았으나 수정되었으며 수정 버전(2.1.4)에서 최신 버전으로 변경되었습니다.
임시 데이터베이스를 위한 솔루션
데이터베이스 파일이 다음 위치에 있는 경우/home(예: /home/Data/mydb.db 또는 /home/site/wwwroot/mydb.db) '데이터베이스가 잠겨 있습니다' 오류가 발생합니다.이는 /home 아래의 모든 파일이 지속되어 Azure Storage에 상주하기 때문이라고 생각합니다.여기에 설명됨
솔루션으로 다른 경로(예: /tmp/mydb.db)를 사용합니다.또는Path.Combine(Path.GetTempPath(), dbName);
주의: 응용 프로그램을 다시 시작하면 DB가 손실됩니다.따라서 임시 폴더에 db를 만든 다음 db 파일을 영구 폴더에 복사할 수 있습니다.그런 다음 읽기 전용 액세스 권한으로 지속된 폴더의 db에 액세스합니다.더하다Mode=ReadOnlySQLite 연결 문자열(최소한 사용 시)Microsoft.Data.Sqlite.SqliteConnection).
Linux를 OS로 요구할 때 더 나은 솔루션은 없을 것 같습니다.Microsoft는 이 동작을 변경해야 합니다.
업데이트 방법을 공유할 수 있습니까?C# 코드가 데이터베이스 연결/트랜잭션을 적절하게 사용하지 않기 때문에 데이터베이스가 잠기는 경우가 있습니다.
업데이트 방법을 살펴보고 데이터베이스 연결 및 트랜잭션이 올바르게 사용되는지 확인해 보는 것이 좋습니다. 마이크로소프트 문서가 도움이 될 수 있습니다.
using (var context = new BloggingContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });
context.SaveChanges();
context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/visualstudio" });
context.SaveChanges();
var blogs = context.Blogs
.OrderBy(b => b.Url)
.ToList();
// Commit transaction if all commands succeed, transaction will auto-rollback
// when disposed if either commands fails
transaction.Commit();
}
catch (Exception)
{
// TODO: Handle failure
}
}
}
여전히 이 문제에 직면한 사람에게:@Josh가 언급했듯이, 이것은 Linux Azure App Service와 관련이 있습니다.
제 잘App Azure App Service에 대해서도 이 오류가 발생합니다.INSERT한 가지 요청에서, 저는 또한 앱 서비스 모니터를 통해 그 시간에 동시 요청이 없다는 것을 확인했습니다.
그 이유에 대해서는 자세히 파악하지 못했지만, 앱 서비스를 윈도 기반으로 바꾸고 나니 모든 것이 잘 작동합니다.
이 문제는 대부분 일부 초기 프로세스로 인해 잠금이 해제되어 처리할 수 없는 열려 있거나 보류 중인 연결 요청으로 인해 발생합니다. 연결과 할 수 같은 해 볼 수 . 어떤 연결 및 명령을 올바르게 배치할 수 있는지 확인하십시오.in order.
private static void ExecuteNonQuery(string queryString)
{
using (var connection = new SQLiteConnection(
ConnectionString))
{
using (var command = new SQLiteCommand(queryString, connection))
{
command.Connection.Open();
command.ExecuteNonQuery();
}
}
}
언급URL : https://stackoverflow.com/questions/53730591/sqlite-db-locked-on-azure-dotnet-core-entity-framework
반응형
'programing' 카테고리의 다른 글
| 파이플롯을 사용하여 평활선 그리기 (0) | 2023.06.07 |
|---|---|
| 부동 소수점 번호를 특정 정밀도로 변환한 다음 문자열로 복사 (0) | 2023.06.07 |
| 매개 변수를 사용하는 Bash 별칭을 만드시겠습니까? (0) | 2023.06.07 |
| Handling applicationDidBeautive - "앱이 Active가 되었을 때 뷰 컨트롤러가 어떻게 응답할 수 있습니까?" (0) | 2023.06.07 |
| C(char, int)에서 포인터 교환 (0) | 2023.06.07 |