Queries ran against AWS Oracle RDS DB timing out-CodePudding

I have some issues with some functions in my application. To be more precise, with my RegisterPuchase function. To be more precise on my Oracle database hosted on AWS.

My application allows an end-user insert(register) and alter(update) a puchase, given that puchase is not yet paid. When inserting the puchase, no problem arises, everyting works as intended.

However, when the user modify the puchase it will run till the subfunction where the itens are inserted to the database, where it will keep running endless, or until "killed". Until then, any attempt to update a puchase afterwards will stop at the UpdatePuchase function. This cause the end-user application to return a timeout error.

For examplification, here is the logic of the webservice implemented on the AWS.

private string RegisterPuchase(Puchase puc)
{
    int reterror = 0; // Works only as error identifier
    decimal ValidatePuchaseValue = 0;
    int PuchaseID = 0;
    try
    {
        string NewPuchaseID = "";
        BeginTransaction();
        if (puc.Ped_temp == 0) //New Puchase, This one presents no issue
        {
            reterror = -10;
            NewPuchaseID = InsertPuchase(puc);
            if (Int32.TryParse(NewPuchaseID, out PuchaseID) && Convert.ToInt32(PuchaseID) > 0)
            {
                reterror = -20;
                ValidatePuchaseValue = InsertItens(puc.LsItens, PuchaseID);
                Commit();
                if(ValidatePuchaseValue != puc.Totalprod)
                    //Send Error: Values on Costumer end and Server does not match
            }
            else
            {
                reterror--;
                RollBack(); // Call DB RollBack
                return reterror.ToString();
            }
        }
        else //Modify Puchase, presents problem at InsertItens
        {
            reterror = -50;
            NewPuchaseID = UpdatePuchase(puc);
            if (Int32.TryParse(NewPuchaseID.Split('*')[0], out PuchaseID) && Convert.ToInt32(PuchaseID) > 0)
            {
                reterror--;
                RemoveItemsPuchase(PuchaseID); // reterrorr = -51
                //Commit(); // If uncommented along with the BeginTransaction the function works
                reterror--;
                //BeginTransaction();// TEST TEST TEST
                ValidatePuchaseValue = InsertItens(puc.LsItens, PuchaseID);// SQL stuck on ExecuteNonQuery() of the first item
                Commit();
                if (ValidatePuchaseValue != puc.Totalprod)
                    //Send Error: Values on Costumer end and Server does not match
            }
            else
            {
                reterror = -60;
                RollBack(); // Call DB RollBack
                return reterror.ToString();
            }
        }
        // Send a Puchase Confirmation Email
        return NewPuchaseID;
    }
    catch(Exception ex)
    {
        RollBack();// Call DB RollBack
        //Register log
        return reterror.ToString();
    }
}


private string InsertPuchase(Puchase puc)
{
    string sql = @"INSERT INTO PUCHASE (ID, SellerID, CostumerID, Status, Altered) 
                    VALUES (:ID, :SellerID, :CostumerID, 0, 0) ";
                    
    Com.Parameters.Clear();
    Com.Parameters.Add("ID", puc.ID);
    Com.Parameters.Add("SellerID", puc.Seller);
    Com.Parameters.Add("CostumerID", puc.Costumer);
    return ExecuteSQL(sql);
}

private string UpdatePuchase(Puchase puc)
{
    string sql = @"UPDATE PUCHASE SET SellerID = :SellerID, CostumerID = :CostumerID, Altered = 1
                    WHERE ID = :ID";
                    
    Com.Parameters.Clear();
    Com.Parameters.Add("SellerID", puc.Seller);
    Com.Parameters.Add("CostumerID", puc.Costumer);
    Com.Parameters.Add("ID", puc.ID);
    return ExecuteSQL(sql);
}


public decimal InsertItems(List<CLSItems> items, int pucID)
{
    string sql = @"INSERT INTO items(ID, Amount, Puc_ID) 
            values (:ID, :Amount, :PucID)";
            
    decimal total = 0;
    int i = 1;
    foreach (CLSItems it in items)
    {
        decimal PriceItem = GetProductPrice(it.ID); //Get the price of the product

        Com.Parameters.Clear(); 
        Com.Parameters.Add("ID", items.ID);
        Com.Parameters.Add("Amount", items.Amount);
        Com.Parameters.Add("PucID", pucID);
        total  = (PriceItem * it.It_qtde);
        ExecuteSQL(sql);
    }
    return total;
}

public void RemoveItemsPuchase(int PucID)
{
    sql = @"delete from items where Puc_ID=:PucID";
    Com.Parameters.Clear();
    Com.Parameters.Add("PucID", PucID);
    ExecuteSQL(sql);
}


public int ExecuteSQL(string sSQL)
{
    try
    {
        int ret = 0;
        Com.CommandText = sSQL;
        ret = Com.ExecuteNonQuery();
        return ret;
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

As mentioned in the code, if I execute the commented commit, no problem arises. However, this is not a good option, as it may break the application should any problem happen middleway.

I do not know how to fix this.

Notes:

This webservice works properly on my local server. By local I mean a Server that is located in the building, not my computer.
Aside the connection parameters, the code has no difference between local webservice and the remote one.
The end-user application is sending and receiving response properly, as I can update all the application info from the webservice(Items, Costumers, Sellers, etc.) both on internal and external network.
Sending the queries separately works, so the problem does not seems to be the queries themselves.
Local Oracle version: 11.2.0.2.0
Remote Oracle version: 19.0.0.0.0

Edit:

Connection function: new Oracle.ManagedDataAccess.Client.OracleConnection(@"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XXX.XXX.XXX)(PORT=XXXX)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));PERSIST SECURITY INFO=True;User ID=USER;Password=PASSW");
The code is creating two sessions on Oracle DB, one inactive and one active that waits the inactive to complete. Killing the inactive usually allows the active to complete.
Removing the RemoveItemsPuchase function allows the transaction run with no issues. It also applies if we remove the InsertItens instead of RemoveItemsPuchase.

CodePudding user response：

After days searching, I found the problem.

There is an old process, from the same application, that runs before the RegisterPuchase, where a few others things are validated.

This process always opens a new connection, regardless if there was a open connection or not, and never closes it, that is why there were 2 sessions on my DB. Since this connection is never closed, it stayed on inactive mode, and when the function RegisterPuchase created a new one, the DB waited for the old one to close, which never happens. Probably because both connections were from the same application.

It never crossed my mind that this old function would be the reason of such strange problem, because it does not access the same tables, and because the timeout always started at the InsertItens and at the UpdatePuchase afterwards.

Now, I do not know why removing the RemoveItemsPuchase or InsertItens function or commiting early made it work, nor why it worked on my local server. I suppose that my local server automatically killed inactive sessions, but I am not sure.

So, bottom line: If you opened a connection make sure to close it and verify if there are any open connection before creating a new one for the same function.

Knowing this, I will check my code to see if there is any other place with the same problem.

Thank you everyone for the help.

Note: If this answer/question is not clear enough, name the problem and I will do my best to fix it.