Base solution for your next web application
Open Closed

ORA-08177: can't serialize access for this transaction #5709

User avatar
0
tteoh created

Hi, I'm using non-core ASP.NET Zero Version 5.6.0 (ASP.NET MVC 5 & AngularJs 1.x). I'm connecting to Oracle database. I'm having this exception when operate Update-Database for the migration codes below:

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-08177: can't serialize access for this transaction ORA-06512: at line 27

public override void Up()
{
    AddColumn("USFA.Visitation", "InitialRecordID", c => c.String(maxLength: 150));

    //AddColumn("USFA.Visitation", "DeviceUserID", c => c.String(nullable: false, maxLength: 256));
    Sql(@"ALTER TABLE ""Visitation"" ADD ""DeviceUserID"" NVARCHAR2(256) DEFAULT 'UserName' NOT NULL");
    Sql(@"UPDATE ""Visitation"" ""v"" SET ""DeviceUserID"" = (SELECT ""UserName"" FROM ""AbpUsers"" ""u"" WHERE ""u"".""Id"" = ""v"".""CreatorUserId"") WHERE ""DeviceUserID"" = 'UserName'");
    Sql(@"ALTER TABLE ""Visitation"" MODIFY(""DeviceUserID"" DEFAULT (null))");
}

public override void Down()
{
    DropColumn("USFA.Visitation", "DeviceUserID");
    DropColumn("USFA.Visitation", "InitialRecordID");
}

But I'm not getting any exception when running the migration codes below:

public override void Up()
{
    //AddColumn("USFA.EntityStatus", "EntityName", c => c.String(nullable: false, maxLength: 50));
    //DropColumn("USFA.EntityStatus", "StatusType");
    Sql(@"ALTER TABLE ""USFA"".""EntityStatus"" RENAME COLUMN ""StatusType"" TO ""EntityName""");
}

public override void Down()
{
    //AddColumn("USFA.EntityStatus", "StatusType", c => c.String(nullable: false, maxLength: 50));
    //DropColumn("USFA.EntityStatus", "EntityName");
    Sql(@"ALTER TABLE ""USFA"".""EntityStatus"" RENAME COLUMN ""EntityName"" TO ""StatusType""");
}

I have already added the codes below to EntityFramework module:

Configuration.UnitOfWork.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

Should I add this to Web module as well? And I'm using Migrator.exe to run on server, therefore should I add it to Migrator module as well?

Thanks. /Tommy

Go to accepted answer
4 Answer(s)
  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    I'm not sure if this is related to transaction directly. You can tyr the solutions on the links below;

    1. https://forums.asp.net/t/2135564.aspx?ORA+00604+ORA+08177+with+entity+framework
    2. https://github.com/laurenz/oracle_fdw/issues/200#issuecomment-329741493
  • User Avatar
    0
    tteoh created

    Hi ismcagdas,

    This problem is solved, sorry that forgot to update here.

    Due to the answer here: ORA-08177: can't serialize access for this transaction So I just added Sql("COMMIT") at the last line of the codes at both migrations. (Sorry that didn't mention about I was running 2 migrations)

    The answer of why the second migration class is working is because I only ran 1 migration at a time.

    For the first migration class that I showed above is getting exceptions is because I ran 2 migrations at a time. Therefore, the first migration class is not committing (I have no idead why is it not committing) then I added Sql("COMMIT") and it's working fine now.

    Question Why it is not committing even though I added:

    Configuration.UnitOfWork.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

    Thanks. /Tommy

  • User Avatar
    0
    ryancyq created
    Support Team

    Configuration.UnitOfWork.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

    This does not perform sql commit. it is just a configuration to how ef/core reads the data.

    ReadCommitted 4096 Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.

    See isolationlevel

  • User Avatar
    0
    tteoh created

    Hi ryancyq,

    Thanks for the explanation.

    Thanks. /Tommy

Assignee
No one
Labels
Non yet