' Optimistic concurrency' exception not firing

[daryl]

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.
}

[Pinturiccio]

We are investigating the issue and will post here about the results as soon as possible.

[daryl]

Hi Pinturiccio,

Has there been any progress on this issue.

Regards
Daryl

[daryl]

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

[Pinturiccio]

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

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/librar ... ionToggle0

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.

[daryl]

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

[Pinturiccio]

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/librar ... 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");

[daryl]

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

[Pinturiccio]

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)
{
    
}

[daryl]

Hi Pinturiccio,

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

Regards
Daryl