' Optimistic concurrency' exception not firing

' Optimistic concurrency' exception not firing

Postby daryl » Thu 26 Mar 2015 05:50

Hi,
I am looking into finding out why an update using the Update method for the OracleDataAdapter does not work as expected.

BTW I have used the same code modified for SQL Server and found it to work as expected i.e. throw the DBConcurrencyException exception.

Steps taken to test.
a) Basically I fill the datatable.
b) Manually update record 5 and commit the changes using TOAD
c) Programmatically update the same record within the console application
d) Expect the objAdapter.Update(dtPerson.Select(null, null, DataViewRowState.ModifiedCurrent)); to return a 0. In this case it returns a 1.
e) Throw a DBConcurrencyException exception. Exception not thrown.
d) A check of the database shows that the values in record 5 are NOT updated.

I have added all the necessary adapter commands - Insert, Delete, Update and Select.

Any help will be very good.

Thanks
Daryl

Please see code extract below for details.

Code: Select all
// Devart Oracle Client       
OracleConnection objConn = new OracleConnection("Data Source=DEVDB; User ID=SA; Password=password");
OracleDataAdapter objAdapter = new OracleDataAdapter();

// Update Command
OracleCommand objUpdateCmd = new OracleCommand();
objUpdateCmd.Connection = objConn;
objUpdateCmd.CommandText = "pkge_family.update_person";
objUpdateCmd.CommandType = CommandType.StoredProcedure;           
objUpdateCmd.Parameters.Add("p_id", OracleDbType.Integer, 4, "ID");
objUpdateCmd.Parameters.Add("p_name", OracleDbType.VarChar, 40, "NAME");
objUpdateCmd.Parameters.Add("p_age", OracleDbType.Integer, 4, "AGE");
objUpdateCmd.Parameters.Add("p_lastupdated",  OracleDbType.TimeStamp, 8, "LASTUPDATED");
objUpdateCmd.Parameters.Add("p_NewLastUpdated", OracleDbType.TimeStamp, 8, ParameterDirection.Output, false, 0, 0, "LASTUPDATED", DataRowVersion.Current, null);           
objAdapter.UpdateCommand = objUpdateCmd;

// Get all person records
DataTable dtPerson = new DataTable();
objAdapter.Fill(dtPerson);

// Perform a manual to record with ID of 5

// Programmatic Update
DataRow[] personRow = dtPerson.Select("ID = 5");
personRow[0]["Age"] = 50;
personRow[0]["Name"] = "Jimmey";
int x1 = objAdapter.Update(dtPerson.Select(null, null, DataViewRowState.ModifiedCurrent));

catch (DBConcurrencyException exDBC)
{
// No exception is thrown x1 = 1 but note that the database contents are not changed
// from the manual updated values.
}

daryl
 
Posts: 6
Joined: Thu 26 Mar 2015 05:20

Re: ' Optimistic concurrency' exception not firing

Postby Pinturiccio » Mon 30 Mar 2015 13:19

We are investigating the issue and will post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1884
Joined: Wed 02 Nov 2011 09:44

Re: ' Optimistic concurrency' exception not firing

Postby daryl » Sat 23 May 2015 06:56

Hi Pinturiccio,

Has there been any progress on this issue.

Regards
Daryl
daryl
 
Posts: 6
Joined: Thu 26 Mar 2015 05:20

Re: ' Optimistic concurrency' exception not firing

Postby daryl » Mon 25 May 2015 21:22

Hi Pinturiccio,

I thought the current version may have had the change so I just tested it and the concurrency issue that I have asked about still appears to exist.

Any help would be very good.

Regards
Daryl
daryl
 
Posts: 6
Joined: Thu 26 Mar 2015 05:20

Re: ' Optimistic concurrency' exception not firing

Postby Pinturiccio » Tue 26 May 2015 14:39

We have reproduced the issue, but we think this is the designed behavior.

daryl wrote:BTW I have used the same code modified for SQL Server and found it to work as expected i.e. throw the DBConcurrencyException exception.

The TIMESTAMP type has a special meaning in SQL Server. A table cannot have more than one TIMESTAMP column. The current timestamp is written to this column for a row each time when the row is created or edited. When the row is updated, the value of the TIMESTAMP column of the modified row is compared with the actual database value. If the values are different, an optimistic concurrency violation occurs.

In your example you read data to the DataTable, including the LASTUPDATED column values. After this you edit a row in Toad. The LASTUPDATED value is changed for this row, and when you called objAdapter.Update(), the LASTUPDATED value in the database is different from the value in your DataTable, and this causes the optimistic concurrency violation. The server returns this error to your application. For more information, please refer to https://msdn.microsoft.com/en-us/library/aa0416cz%28v=vs.110%29.aspx#sectionToggle0

In Oracle, TIMESTAMP columns are treated in the same way as columns with other data types. Probably there is a condition "LASTUPDATED = p_lastupdated" in the UPDATE statement in the procedure. When you updated data in Toad, probably you have modified the LASTUPDATED value. After this, the LASTUPDATED value in the database is different from the corresponding value in the DataTable, and the row will not be updated. The procedure itself does not cause any errors and is executed successfully. There is no rows that satisfy the WHERE condition of the UPDATE statement.

The optimistic concurrency violation in SQL Server is the specifics of the database behavior not implemented in Oracle. We have tested another Oracle provider ODP.NET, and it has the same behavior as dotConnect for Oracle.
Pinturiccio
Devart Team
 
Posts: 1884
Joined: Wed 02 Nov 2011 09:44

Re: ' Optimistic concurrency' exception not firing

Postby daryl » Wed 27 May 2015 09:05

Hi Pinturiccio,

Thanks for your response. I can add the following to assist in getting to the heart of the problem. Perhaps I am doing something wrong.

I am not using Timestamp with SQLServer I am just using the type DateTime. Please see the script/code below for table and procedure.
SQL Server Table.
Code: Select all
CREATE TABLE [dbo].[Person](
   [ID] [int] NOT NULL,
   [NAME] [varchar](30) NOT NULL,
   [AGE] [int] NOT NULL,
   [LASTUPDATED] [datetime] NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


SQL Server Procedure.
Code: Select all
-- Update a Person's details
ALTER PROCEDURE [dbo].[prUpdate_Person]
   @ID INT,
   @LastUpdated DATETIME,
   @Name NVARCHAR(35),
   @Age INT,
   @NewLastUpdated DATETIME OUTPUT   
AS
   SET @NewLastUpdated = getdate()
   
   UPDATE   Person
   SET      Name = @Name,
         Age = @Age,
         LastUpdated = @NewLastUpdated
   WHERE   ID = @ID
     AND   LastUpdated = @LastUpdated
   
   IF @@ROWCOUNT = 0
      SET @NewLastUpdated = @LastUpdated

   RETURN


As for Oracle I am using the following Table and Procedure to update the information.

Oracle Table.
Code: Select all
CREATE TABLE SA.PERSON
(
  ID           INTEGER,
  NAME         VARCHAR2(50 CHAR),
  AGE          INTEGER,
  LASTUPDATED  TIMESTAMP(8)
)


Oracle Procedure.
Code: Select all
procedure update_person(p_id number, p_name varchar2, p_age number, p_lastupdated timestamp, p_NewLastUpdated out timestamp)
is
begin

    p_NewLastUpdated := systimestamp;
   
   update person
   set name = p_name, age = p_age, LASTUPDATED = p_NewLastUpdated
   where id = p_id AND LASTUPDATED = p_lastupdated;
   
    IF sql%rowcount = 0 THEN
        p_NewLastUpdated := p_lastupdated;
    END IF;
   
end update_person;


My expectation is that the procedure assigned to the DataAdaper's Update command will expect a row to be processed but if the ID and the date LastUpdated != the passed in lastUpdated no row will be processed so the procedure will fail leading to an exception.

This works with SQL Server but not Oracle and I can't understand why.

Any help would be well received.

Regards
Daryl
daryl
 
Posts: 6
Joined: Thu 26 Mar 2015 05:20

Re: ' Optimistic concurrency' exception not firing

Postby Pinturiccio » Thu 28 May 2015 12:19

If you don't use a TIMESTAMP column, the DBConcurrencyException probably occurs because zero records affected by the INSERT, UPDATE or DELETE statement. For more information, please refer to https://msdn.microsoft.com/en-us/library/9ab5x5cd%28v=vs.90%29.aspx

dotConnect for Oracle analyzes the return value of the ExecuteNonQuery() method of OracleCommand for performing concurrency check when executing Update(). The ExecuteNonQuery() method returns the actual number of changed records only for INSERT, UPDATE or DELETE statements, not for stored procedure calls.

If you replace your stored procedure call with the following statement:
Code: Select all
"update person set name = :p_name, age = :p_age, LASTUPDATED = CURRENT_TIMESTAMP where id = :p_id AND LASTUPDATED = :p_lastupdated"

you will get the DBConcurrencyException: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.".

Unfortunately, there is no way to implement the Update method for an Oracle provider so that it generates DBConcurrencyException when the corresponding INSERT, UPDATE or DELETE statement affects 0 rows inside a stored procedure.

As a workaround, you can add one more output parameter, returning a number of affected rows to your procedure. The procedure will look like the following:
Code: Select all
CREATE OR REPLACE PROCEDURE SEMENB.update_person(p_id NUMBER,
                                                 p_name VARCHAR2,
                                                 p_age NUMBER,
                                                 p_lastupdated TIMESTAMP,
                                                 p_NewLastUpdated OUT TIMESTAMP,
                                                 row_affected OUT Number)
  IS
  BEGIN

    p_NewLastUpdated := systimestamp;

    UPDATE person
      SET name = p_name, age = p_age, LASTUPDATED = p_NewLastUpdated
      WHERE id = p_id
      AND LASTUPDATED = p_lastupdated;

    IF SQL % ROWCOUNT = 0
    THEN
      p_NewLastUpdated := p_lastupdated;
    END IF;

    row_affected:=SQL % ROWCOUNT;
  END update_person;

Then add this parameter to your code:
Code: Select all
objUpdateCmd.Parameters.Add("row_affected", OracleDbType.Integer, ParameterDirection.Output);

After this you can throw an exception yourself when 0 rows affected after the objAdapter.Update method call:
Code: Select all
objAdapter.Update(dtPerson.Select(null, null, DataViewRowState.ModifiedCurrent));
int x1 = (int)objUpdateCmd.Parameters["row_affected"].Value;
if (x1 == 0) throw new DBConcurrencyException("Concurrency violation: the UpdateCommand affected 0 of the expected "+personRow.Length+" records");
Pinturiccio
Devart Team
 
Posts: 1884
Joined: Wed 02 Nov 2011 09:44

Re: ' Optimistic concurrency' exception not firing

Postby daryl » Thu 28 May 2015 20:49

Thank-you for your response. I can see from the information provided why it is working with SQLServer and not via the Oracle provider which is unfortunate as it takes away a major advantage of ADO.NET - the ability to attach the programmer's procedures to the UPDATE command and get a consistent known behaviour from the framework.

Microsoft have the advantage of controlling both products i.e. .NET framework and SQLServer...

I have tried a similar approach, I think to yours, by adding a raise_application_error statement to the Oracle procedure and this can be caught in the .NET application.

Oracle procedure
Code: Select all
    IF sql%rowcount = 0 THEN
        p_NewLastUpdated := p_lastupdated;
        raise_application_error(-20110, 'Update failed');
    END IF;


c# method
Code: Select all
            catch (OracleException exOracle)
            {
                if (exOracle.Code == 20110)
                {
                    throw new DBConcurrencyException();
                }
            }

However you don't get the information/contents of the data row within the database that caused the exception to occur. As you would know this is available when checking the properties of the DbConcurrency exception.

This row information can then be used to assist the user to make a decision as to how to proceed with their changes.

Am I right in thinking that the workaround you explained would also not have the row information? Can you think of an efficient way to get this information?

For example can this row information be passed out of the Oracle procedure as a data type? or is it best to query the database using the record's ID?


Regards
Daryl
daryl
 
Posts: 6
Joined: Thu 26 Mar 2015 05:20

Re: ' Optimistic concurrency' exception not firing

Postby Pinturiccio » Fri 29 May 2015 08:40

daryl wrote:I have tried a similar approach, I think to yours, by adding a raise_application_error statement to the Oracle procedure and this can be caught in the .NET application.

Yes, in this case you can change your procedure without adding a second parameter, as we offered before.

OracleDataAdapter has the RowUpdated event, which can do the actions you want. Add this event to your code:
Code: Select all
objAdapter.RowUpdated += new OracleRowUpdatedEventHandler(objAdapter_RowUpdated);

You can get the information on the row in the event handler:
Code: Select all
static void objAdapter_RowUpdated(object sender, OracleRowUpdatedEventArgs e)
{
    if (e.Status == UpdateStatus.ErrorsOccurred)
    {
        OracleException oracleError = e.Errors as OracleException;
        if (oracleError != null && oracleError.Code == 20110)
            throw new DBConcurrencyException("Concurrency violation: " + e.Errors.Message, e.Errors, new DataRow[] { e.Row });
    }
}

After this you can catch DBConcurrencyException with the row information:

Code: Select all
try
{
    objAdapter.Update(dtPerson.Select(null, null, DataViewRowState.ModifiedCurrent));
}
catch (DBConcurrencyException ex)
{
   
}
Pinturiccio
Devart Team
 
Posts: 1884
Joined: Wed 02 Nov 2011 09:44

Re: ' Optimistic concurrency' exception not firing

Postby daryl » Tue 02 Jun 2015 06:47

Hi Pinturiccio,

I have implemented this change and so far it looks good. Thanks for your help and advice.

Regards
Daryl
daryl
 
Posts: 6
Joined: Thu 26 Mar 2015 05:20


Return to dotConnect for Oracle