Transactions Using Linq in dotConnect

[emp51302]

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.

[MariiaI]

In this case you probably should use explicit transactions, specifying the necessary isolation level(e.g. REPEATABLE READ or SERIALIZABLE).
For example:

Code: Select all

YourDataContext context = new YourDataContext();
context.Connection.Open();
// open transaction with some IsolationLevel
context.Transaction = context.Connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
 ....
 // your code here
 ....
 context.SubmitChanges();

// commit transaction
 context.Transaction.Commit();

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.

[emp51302]

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

Thanks again!

[MariiaI]

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