This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, I'm fairly new to transaction processing in .net, I usually do everything in stored procedures but now with Linq and Lightspeed I'm moving logic into c# code.
The problem that I have is if I look for a certain record in a table matching a key value and it does not exist I want to create the record. This is all well and good except for if another thread is making a similar query at the same time... for example
thread 1: Look for record where date = '2009-02-18', returns 0 records;
thread 2: Look for record where date = '2009-02-18', returns 0 records;
thread 1: Record does not exist so add it, adds the record, no exception
thread 2: Record does not exist so add it, record with this key exists, fail.
The question is, how do I handle this using Linq and LightSpeed? Here is a sample of the code I am currently using that is causing problems when I have multiple threads running.
Any tips on how to manage this would be greatly appreciated.
Thanks
MyBigTable record = (from v in uow.MyBigTables
where v.Date == 20090218
select v).FirstOrDefault();
if (record == null)
{
// add the stat
record = new MyBigTable
{
Date = 20090218,
Name = "Nice new record",
Count = 0
};
uow.Add(record);
}
// inc the stat count
record.Count++;
// update the db
uow.SaveChanges(); // ERROR on second thread, record already exists
|
|
|
(reposting to correct formatting)
Hi,
I'm fairly new to transaction processing in .net, I usually do everything in stored procedures but now with Linq and Lightspeed I'm moving logic into c# code.
The problem that I have is if I look for a certain record in a table matching a key value and it does not exist I want to create the record. This is all well and good except for if another thread is making a similar query at the same time... for example
thread 1: Look for record where date = '2009-02-18', returns 0 records;
thread 2: Look for record where date = '2009-02-18', returns 0 records;
thread 1: Record does not exist so add it, adds the record, no exception
thread 2: Record does not exist so add it, record with this key exists, fail.
The question is, how do I handle this using Linq and LightSpeed? Here is a sample of the code I am currently using that is causing problems when I have multiple threads running.
Any tips on how to manage this would be greatly appreciated. Thanks
MyBigTable record = (from v in uow.MyBigTables
where v.Date == 20090218 select v).FirstOrDefault(); if (record == null)
{ // add the stat record = new MyBigTable { Date = 20090218, Name = "Nice new record", Count = 0 }; uow.Add(record);
} // inc the stat count
record.Count++; // update the db (thread 2 will crash here as record already exists)
uow.SaveChanges(); |
|
|
There are a couple of things you can do. One option is to use client-side synchronisation such as mutexes or the lock keyword to ensure that only one thread can go down the check-create path at a time. However this is very specific to the threading scenario -- lock will protect you only from other threads in the same program, and mutexes only from other threads and programs on the same machine. Once there are two machines involved, client-side synchronisation can't help you. So I wouldn't recommend this approach. This leaves you with relying on the database itself to detect and report the condition. The simple approach here is to just wrap the insert in a try-catch block -- the unique constraint violation will result in an exception and your code can detect this and swallow it. With transactions you may be able to avoid the exception situation altogether. The key here is the isolation level. You will need a very high isolation level (probably Serializable) so that the database locks access to the selected data until the transaction completes. For this to work both the SELECT (the LINQ or Find query) and the INSERT (the SaveChanges) must be within the same transaction. Use IUnitOfWork.BeginTransaction or the TransactionScope class to establish and scope the transaction. I have to say that reading the docs for IsolationLevel.Serializable I am still not sure it will prevent the problem because it does not appear to prevent the second thread doing its SELECT between the first thread doing its SELECT and its INSERT. Plus transaction implementations differ between databases. So I would strongly advise thorough testing before committing to this route! If the transaction approach looks promising then you will also want to performance/load test. A high isolation level has the potential to lock out other queries on the same data, causing "normal" reads to be delayed while transactions complete. The best approach probably depends on how often you expect clashes to occur. If clashes are likely to be fairly rare, then the exception approach (try it and recover) is probably most efficient. If clashes are likely to be common, try the highly isolated transaction approach, but in this case you might want to re-evaluate your design to see if you can reduce the number of clashes. |
|
|
Hi Ivan, thanks for the suggestions, I think the exception method will probably be best as collisions wont occur that often.
I have stumbled onto a command on MySQL that looks interesting..
INSERT INTO tmp_visit_counts(date, visits) VALUES (_date, 1)
ON DUPLICATE KEY UPDATE visits=visits+1;
I don't suppose something like this can be tagged onto the end of the Lightspeed insert command to handle this automatically behind the scenes for MySQL?
Looks like it would be a great feature.
Mike
|
|
|
(it appears that your post code has an issue with formatting on Chrome) Hi Ivan, thanks for the suggestions, I think the exception method will probably be best as collisions wont occur that often. I have stumbled onto a command on MySQL that looks interesting.. INSERT INTO tmp_visit_counts(date, visits) VALUES (_date, 1) ON DUPLICATE KEY UPDATE visits=visits+1; I don't suppose something like this can be tagged onto the end of the Lightspeed insert command to handle this automatically behind the scenes for MySQL? Looks like it would be a great feature. Mike
|
|
|
We'll consider that feature, but in most cases it's preferable to get the exception so that you know your unit of work is out of sync with the database, and the ON DUPLICATE KEY UPDATE mechanism is too specific to entity business logic to be easily implementable in a generic way. In your case, the logic is "add 1 to the existing value" but in other cases it might be to perform a completely different update. Specifically, performing an ON DUPLICATE KEY UPDATE with the client-side entity values does not remove the problem but just disguises it. Imagine that threads A and B both find that the entity doesn't exist, so they create it and set the value to 1. Thread A does the INSERT and it goes through. Then Thread B does the INSERT, but now the key exists so it becomes an UPDATE, but the value that B is sending is 1, not 2. No exception, but incorrect data. To avoid this, we would need to capture that visits -> visits + 1 logic rather than just saving the myBigTable.Visits value. Worse, if what you want is an atomic update of visits -> visits + 1, then the exception handling approach will not guarantee it for you. Consider the case where the date key already exists. (You may already handling this case, but I thought it was worth mentioning just in case.) Threads A and B both get the entity when the value is 1. Thread A updates it to 2, and saves the result. Then Thread B updates it to 2, and saves the result. The final result is 2, but if you wanted it to be +1 each time then the final result should have been 3. The exception-based approach we discussed won't trap this case because both threads are performing an UPDATE of an existing entity, which will not cause the duplicate key exception we relied on for the double-INSERT case. The solution to this is optimistic concurrency checking. Add a LockVersion field to the entity (or set Optimistic Concurrency Checking to true in the designer), and a corresponding column to the database. Then, when you do a SaveChanges, LightSpeed will check that the entity has not been modified since it was loaded, and raise an exception if it has. You can then reload the entity and try again. Apologies if you are already handling this case, I just thought it was worth mentioning because it's exactly the sort of subtle race condition that won't manifest until your most important customer demo... Alternatively, if all you want to do is atomically count visits, an entity-based approach may not be the best way to do it. A stored procedure or a bit of custom SQL may be more natural and efficient than a VisitCount entity that has to be round-tripped to the client and deal with all the concurrency problems that entails. Object-relational mappers are great at dealing with data that you want to manipulate in code, but occasionally there's no point materialising an object just to update it -- or it's positively counterproductive -- and yours may be one of those cases. |
|
|
Hi Ivan, Yes I understand what you are saying, without having the same logic of Visits = Visits + 1 there would be some loss of data, in this case of course it would be better to either wrap the insert/update in a stored proc or trap the exception in code. Thanks as always for your highly detailed and well thought out response! Mike |
|
|
We have to insert n records in the database but if an error happens all of the inserted records should not be commited. For now, what we do is use a lightspeed stored procedure that inserts these records one by one (in c# code we loop through a list and for each item we call the SP).
What are our options of controlling the transaction in this case?
|
|
|
Add each entity to the unit of work, then use a TransactionScope or IUnitOfWork.BeginTransaction to place a transaction around the SaveChanges() call. Then all saves (inserts, updates and deletes) will be performed in the context of that transaction, and if an error occurs the entire transaction can be rolled back. If no error occurs, you can commit the transaction. |
|