Hello all

We have a problem in my project, that I would like some advice on how to slove using LLBLGen and SQLServer.

We have a cluster server setup with two loadbalanced servers (A and B). Requests are comming in through web services.

In part of our application, we need to read some data from the database, and based on the incomming data and the data read from the database, write some new data. In this situation, we never changes existing data.

We have a concurrency issue here. We may end up in a situation where server A receives a request, and while it is processing it, server B receives another request. The request that server B receives depend on the outcome of the data that server A will eventually write to the database.

How do we make sure, that server B waits until server A is finished with its processing, so that server B can make use of the data stored by server A?

Can transactions slove our problem? Assume that server A starts its transaction before server B, but it does not necessarily finish its transaction first, unless we make sure that server B waits for server A to complete.

Our first thought was that server A writes a "lock" in the database saying that it has now started processing. Server B will detect this "lock" and waits for server A to finish.

We don't like this solution from a performance standpoint, but also because these servers are running under IIS, and you never know if somebody decides to do a iisreset after server A has written the "lock", so the "lock" is left in the database.

Do any of you have a better solution?

It sounds like a fairly standard problem, so I would like to hear if any of you have had similary problems, and how you solved it.

Thanks.

Regards Anders

Apart from database locks you can have a common base service that server A and B use to interact with the database, in this service/server you should implement a critical section/ Mutex solution.

Hi,

You can handle this situation using the default readcommited transaction: 1. server A starts a new readcommited transaction and insert new data that server B will try to read. 2. server B starts a new readcommited transaction and try to read the data that server A has just written (this data has not been commited yet and server B has to wait until the data is commited by server A).

You do not need to handle any lock, let the SQL Server do all the locks. Let SQL Server do the rollback in case that the server A be reset and the transaction not be commited.

Hello Rogelio

Thank you for your reply. I'm sorry that I havent replied until now, but I have been busy with other things.

It would be very nice if we can rely on the database to handle transactions for us. I don't know that much about the details of transaction management, so I have a few questions to your message.

Consider this flow:

1. Server A starts a new readcommited transaction and reads data from the database.

2. Server B starts a new readcommitted transaction and reads data from the database.

3. Server A updates the database with information that Server B should see during its read phase.

4. Server A commits the transaction.

5. Server B updates the database, without the information that Server A have written, because it did its read before Server A had written the data.

6. Server B commits the transaction.

Is this scenario possible using the readcommited transatcions?

Our problem is, that both Server A and Server B reads from the database and based on the information read, inserts new information into the database. Neither server updates existing data, but always reads new data.

We want to make sure, that server B will know about the data that Server A is writing before it writes its own data. At least we would like to be notifies (e.g. with an exception) that Server B needs to read from the database again, because Server A have written new data in the mean time.

If that is not possible, we would like Server B to be aware that Server A is currently procesing data, so it must wait until Server A have finished its processing. We can perhaps use locking for this purpose.

Regards Anders