Concurrency violation

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
xterm
Posts: 9
Joined: Tue 15 Nov 2005 13:24

Concurrency violation

Post by xterm » Mon 16 Jan 2006 23:12

i am using Optimistic Concurrency and mysqldirect .net 3.20.6 in a vb.net 2005 project with the following way. My tableadapter uses the updatecommand:

Code: Select all

UPDATE `protocol`.`proto` SET `id` = :id, `proto_number` = :proto_number, `proto_date` = :proto_date, `Last_Update` = now() WHERE `id` = :id_1 AND `Last_Update` = :Last_Update
Last_Update` describes when the row was last updated.
When i try to update the dataset with the adapter, i get a Concurrency violation error.This isn't supposed to happen because i am the only one who updates the db.My database has one table the 'proto'.

Please Help, Sorry for my english

Serious

Post by Serious » Tue 17 Jan 2006 09:11

We think you use parameters in incorrect way. Here is small sample of using TIMESTAMP fields.
Table definition

Code: Select all

CREATE TABLE test.opt_lck (
  id INT(11) NOT NULL AUTO_INCREMENT,
  f_char VARCHAR(45),
  f_timestamp TIMESTAMP(0) default 'CURRENT_TIMESTAMP',
  PRIMARY KEY (id)
)
Update command text

Code: Select all

UPDATE
  test.opt_lck
SET
  f_char = :f_char,
  f_timestamp = now()
WHERE (id = :orig_id) and (f_timestamp = :orig_timestamp);
Parameters created with DataAdapter designer

Code: Select all

updateCommand.Parameters.Add(new CoreLab.MySql.MySqlParameter("f_char", CoreLab.MySql.MySqlType.VarChar, 4, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "f_char", System.Data.DataRowVersion.Current, null));

updateCommand.Parameters.Add(new CoreLab.MySql.MySqlParameter("orig_id", CoreLab.MySql.MySqlType.Int, 1, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "id", System.Data.DataRowVersion.Original, null));

updateCommand.Parameters.Add(new CoreLab.MySql.MySqlParameter("orig_timestamp", CoreLab.MySql.MySqlType.TimeStamp, 19, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "f_timestamp", System.Data.DataRowVersion.Original, null));
Note that orig_id and orig_timestamp parameters have Original DataRowVersion.

xterm
Posts: 9
Joined: Tue 15 Nov 2005 13:24

Post by xterm » Tue 17 Jan 2006 10:00

I have already done what you suggested me...
the problem is that when i change something and then i try to update a record for the first time everything works fine.But if ichange something again in the same record and i update my record it raises an concurrency error.

Serious

Post by Serious » Tue 17 Jan 2006 12:38

You have to fetch inserted timestamp value when updating table. New command text for DataAdapter.UpdateCommand

Code: Select all

 update
  test.opt_lck
set
  f_char = :f_char,
  f_timestamp = now()
where (id = :orig_id) and (f_timestamp = :orig_timestamp);

select f_timestamp from opt_lck where id = :orig_id;
You must set MySqlCommand.UpdatedRowSource to UpdateRowSource.Both.

Post Reply