Transactions Using Linq in dotConnect

Transactions Using Linq in dotConnect

Postby emp51302 » Fri 28 Sep 2012 16:04

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.
emp51302
 
Posts: 46
Joined: Fri 19 Aug 2011 20:57

Re: Transactions Using Linq in dotConnect

Postby MariiaI » Mon 01 Oct 2012 13:52

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/Transactions.html#explicit
For more information about isolation levels, please refer to:
http://dev.mysql.com/doc/refman/5.0/en/set-transaction.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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Transactions Using Linq in dotConnect

Postby emp51302 » Thu 11 Oct 2012 14:03

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

Thanks again!
emp51302
 
Posts: 46
Joined: Fri 19 Aug 2011 20:57

Re: Transactions Using Linq in dotConnect

Postby MariiaI » Fri 12 Oct 2012 06:30

Glad to see that the problem was resolved. If you have any further questions, feel free to contact us.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for MySQL