1 database, 2 threads

[RogerGarrett]

I'm doing some queries on a SQL Server 2000 database. (MFC C++ dialog-based application).

I start up a transaction with:

m_Session.StartTransaction(ISOLATIONLEVEL_SERIALIZABLE)

I then do a query that determines whether or not a particular item is in a particular table. If it's already there I then do a query which UPDATEs the existing item. If it's not already there I do an INSERT query. If everything goes OK I do a m_Session.Commit(), otherwise I do a m_Session.Abort().

However, this is done within a thread. There can also be another thread doing the exact same thing for the exact same item in the table. It was my understanding that specifying ISOLATIONLEVEL_SERIALIZABLE in the StartTransaction call assures that the two threads don't interfere within one another, and that when one thread determines that the item does not exist and proceeds to do an INSERT, it's not possible that at the same time the other thread has just done the same thing but already performed the INSERT.

But it doesn't behave as I had expected. I do indeed get clashes between the two threads.

It appears I need some way to tell it to "lock" specific tables, or maybe even specific items in the tables, in order to asure that my two threads don't clash.

What is the proper way to do this?

Roger Garrett

[exterminator]

First, I don't like you hitting the database twice for one job - you hit for record existence and then you hit again to update/insert. What I would suggest is make a stored procedure in the database and have it do all this stuff.. and you just call it for a run... that's better - elegant and efficient.

Secondly, what you are facing is quite a common issue. This happens more frequently in web-applications or in any client-server architecture in general.

The database will handle all the things but the problem comes in a specific scenario. Now lets take the case of 2 concurrent viewing of the same record. One goes and updates the record. Other is still viewing the old data and now makes changes and goes for update - what happens? The first change gets over-written. What you need to is - make a timestamp column for your tables. So before update you check if the timestamp that you have for your record to be updated is latest one or is there a change later than that. If there is a change - come back - dont update and notify the user. Think along the same line for a multi-threaded client.

In case of inserts - I guess the uniqueness constraints should take care of this issue. Hope this helps. Regards.