Devart Forums

Hi Support Team - I wanted to know if we can have transactions in LinqConnect using dotConnect for MySQL?

My issue is that when I submit changes for a record in database, I have to create a duplicate record for the same record and after creating the duplicate record I insert the duplicate record (which obviouusly will have a new AutoIncrement ID) in the same table with the only change is that the table has a BOOLEAN field called DEFUNCT whose value will change.

EXAMPLE: I am using CopyDataMembers method which I previously discussed using Reflection which helps me clone the record successfully... Now the next step I do is that I change the original source record's BOOLEAN value of DEFUNCT column to TRUE and the newly inserted duplicate record has the DEFUNCT value of FALSE, which works perfectly! and thats what I want...

So now my database has 2 same records, but one of the record is DEFUNCT (True) which helps me filter out the DEFUNCT records.

Now the BIG PROBLEM:

Say if 2 users are looking at the same record (for example let's say they are looking at article ID #525) in the GUI ASP.NET application and if both of them click on the SAVE BUTTON on the page, it successfully makes the article ID #525 column DEFUNCT = TRUE and duplicates the record and does the insert, but now with a DEFUNCT value of FALSE, which is perfect and now the new article ID is #526, BUT in the database I see 2 records #526 from user 1 and #527 from user 2. I only want one duplicate record to be created for the same article, how can I achieve that?

Database Snapshot Example: http://dl.dropbox.com/u/619926/expdata.html

All I am looking for is to make sure when I clone (duplicate) I don't do it if the article #525 in this case has already been DEFUNT.

In this case you probably should use explicit transactions, specifying the necessary isolation level(e.g. REPEATABLE READ or SERIALIZABLE).
For example: Documentation about using the transactions in LinqConnect is available here:
http://www.devart.com/linqconnect/docs/ ... l#explicit
For more information about isolation levels, please refer to:
http://dev.mysql.com/doc/refman/5.0/en/ ... ction.html

Also, you could try adding constraints to the table (for example, you could try setting article_unique_guid, article_id and defunct columns as UNIQUE constraint).

Please tell us if this helps.

Thank you! It seems that did the trick! We are still testing it and will provide you with more feedback soon.

Thanks again!

Glad to see that the problem was resolved. If you have any further questions, feel free to contact us.