1

My server application that is written in C# starts a new thread every time it needs to insert or remove data from the database. The problem is that since the execution of the threads is arbitrary it is not ensured that a delete command is executed after the insertion of the same object if those events occur almost at the same time.

E.g.: The server receives the command to insert multiple objects. It takes about 5 seconds to insert all the object. After 1 second of execution the server receives the command to delete all those objects again from the database. Since the removal could happen before all objects are completely stored the outcome is unknown.

How can the order of execution of certain thread be managed?

Improve this question
3
  • Look for System.Threading.Mutex or System.Threading.ManualResetEvent Commented Mar 13, 2013 at 11:56
  • Threading.Mutex is a good idea for serializing threads in a particular call. You might also look at message queuing. Commented Mar 13, 2013 at 11:59
  • I don't think using a mutex would work since there could be a insert, delete and another insert operation of the same data. After the first insert would finish, the mutex would not ensure that the delete command is executed before the next insert, or am I wrong? Message queuing is an option but I would have hoped that there is a better solution to this to avoid performance hits. Commented Mar 13, 2013 at 12:49

4 Answers 4

Reset to default
2

You can use transactions for this and specify different levels for different operations.

For example, you can use the highest level of transactions for writes/updates/deletes but a low level for reads. You can also fine-tune this to allow blocking of only specific rows, compared to tables. Specific terminology depends on the database and data access library you use.

I would advice against using any ordering. Parallel and ordered just don't go well together. For example:

  • You need to horizontally scale servers, once you add a second server and a load balancer a mutex solution will not work

  • In a large and distributed systems a message queue won't work either as by the time one thread completed a scan and decided that we good to go, another thread can write a message that should have prevented operation execution. Moreover, given you receive high load, scanning the same queue multiple times is inefficient.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

what do you mean an operation that should prevent operation execution? an insert followed by a delete? in transactions this might also be the case if they are not in the same transaction. and I also don't think there is any ineffiency here if the queue is not scanned until emptied. let's say it has 500 items to begin with, only 500 items are dequeud, even if more items arrived in the meantime, they will have to wait for the next iteration.
In transactions the updated rows will be locked on the database side. Situation 1: insert comes first with predefined ids = {1,2,3} and while it's being executed a delete comes for ids = {4,5,6}. In this case 2 transactions may operate concurrently without locking and both succeed. Situation 2: insert comes first with predefined ids = {1,2,3} and while it's being executed a delete comes for ids = {2,3}. In this case 2 transactions will not operate concurrently. First insert will lock three rows and the second transaction will either wait for the insert to be finished or fail with error
0

If you know that you receive insert before delete and the problem is just that you don't want to interrupt your insertion then you can just use lock on your insertion code.

static object m_Lock = new object();

public void Insert()    
{
   lock (m_Lock)
   {
      InsertRecords();
   }
}

public void Remove()    
{
   lock (m_Lock)
   {
      RemoveRecords();
   }
}

This way you are sure that remove won't happen during insert.

P.S. Seems strange though that you need to insert and then delete right away.

Improve this answer

4 Comments

I think that locking for every insert might cause a performance hit, for a lrage nubmer of requests.
@omerschleifer: This really depends on the load. If a server gets many requests per second this can be a problem of course as all requests wil be executed one by one.
This would require a unique method for each objct that can be inserted/deleted. Otherwise locking insertion/deletion for any object would decrease performance since there could be multiple insertions/deletions for different objects. Since this is a distributed application where multiple clients can work on the same data the pictured scenario can happen anytime
Never lock around I/O (including DB operations), since you have no control over its runtime
0

I think the simplest way is to queue all incoming requests to insert objects in one collection, and to queue all incoming requests to delete objects in a second collection.

The server should have a basic loop that does :

a. check if there are incoming inserts , if so -> perform all inserts.

b. check if there are incoming delete requests, if so -> perform all delete requests.

c. sleep for X milli-seconds.

Now, if you have a delete request on an object that does not exist. you have two options:

a. igore this request and discard it.

b. ignore this request for this round and keep it in the collection for the next N rounds, before deleting it (Finally deleting it- assuming this is simply a bad request and is not a problem of race condition.)

Improve this answer

3 Comments

This does not work unless strict restrictions are placed on the incoming operations. There's nothing to stop insertion of semantically the same record twice in your insert set, and an intervening delete of that record being delayed.
As i understood the question it was about synchronizing requests that might come out of odrer, and since they might come from different clients, I offerd a solution which is scalable to the clients. I did not refer to data integrity, in that you are right. it was not my understanding that duplicate inserts are the issue here
I agree that the question as offered does not allow for a precise answer.
0

Use a Queue (with a single servicing thread) to enforce the ordering. You can also use Task Parallel Library to manage tasks with dependencies on other tasks, though that's very difficult with arbitrary DB operations.

I think you need to rethink how you manage the incoming operations, and whether or not their inter-dependencies are predictable enough that you can safely use multiple threads in this way. You may need to add some "depends on" information into incoming operations to achieve that goal.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.