Object Concurrency

[VBCrazyCoder]

I would like to ask how everyone is handling object concurrency. For example, user A loads an object with data from the DB, and makes some changes. User B then loads the same data into an object and makes some changes. User B saves these changes (writes new data to the DB). Now user A goes to save their changes - and at this point the issue arises.

How does everyone handle this?

[Lethal]

What you could do (what we do) is create a rowversion timestamp column that gets loaded with the object. When you go to update the object (data), in your sproc or sql, check and make sure the timestamps match, if not, you know you know the record has been updated since last retrieved.

[VBCrazyCoder]

But where do you do from there? Do you have a copy of the original object and compare all the fields to see what is different so you can notify the user? So you would have 3 objects (original, users modified one, and the one that is in the DB at the time of attempted update)?

[alextyx]

Dear friends, this problem occurs if you really need to work disconnected from the DB. If you need to work on a geographic net, probably you must solve it with timestamp. I think ADO.NET did this work when optimistic locking is used. I've not an idea of how many users have to work on your DB at the same time and, more important, which baudrate they have and which kind of operations they need to do. VB.NET is clearly oriented to an 'INTERNET way of life' and its database tools where tought to download datas, work heavily on local machine and then upload them. If you are trying to write an application that has to run on a LAN, I think (and not only me!) the good way is to work connected to the DB, locking record on which you are making changes (pessimistic locking). Then we can discuss about which kind of DB is better: MDB, SQLServer,..... (their data provider, also implement locking in different ways). I've choosen to use ADO also if working in VB.NET, because of this problem. ADO.NET doesn't implement a pessimistic locking! ( Excuse me for my poor english).

[VBCrazyCoder]

For my application I cannot use pessimistic locking. I must use optimistic so that the record is available. I am currently using the timestamp method but want to investigate something more detailed.

Anyone handle concurrency at the field level?

[alextyx]

Dear friend, If you can't use a pessimistic locking because many users have to access AND MODIFY the same record (reading is not a problem with MDB, a little more with SQL) and you must concentrate on a field level locking, surely it's possible, but it requires a minimum of code. At the beginning of my experience (I'm new to this world, I come from Assembler application,I began to use high level language during the last summer), I was using VB.NET 2002 with Microsoft Jet 4.0 and ADO. I found a strange bug, in pessimistic locking, then disappeared with VS.NET 2003. Briefly, I was forced to implement a procedure that locks a record putting in a special field, the code of the first user that need to lock the record. This code change every interval of 5 seconds ( for ex.:from USER0 01.0 to USER 01.9).
If another user, interested on the same record, find the code not changing for 15 seconds, he can overwrite it with his own code, because it means the other user (USER 01) has become disconnected for a net failure or program crash. Obviously, there are some precautions to keep present. A user (let's call him: USER 02) must read a free code, or a static code (like discussed before) , before to try to write his code, then he can try to write his code, and then try to read it, in the field of DB, for a several number of times, to be sure it was not overwrite by others (it's possible more users try to lock record almost at the same time). At that point other users will read the code of USER 02 and they will not try to write theirs. When USER 02 leave the record, It writes a FREE code in the field. It seems complicate, but you can adapt this logic to lock a single field. But, I think, you need to double the field number of your DB (in a column there is data, in a corresponding one, the user code that is locking it), but it is possible only if you are connected to DB with a sufficient bandwidth to manage this operations. I you are working on a remoted DB and use internet you can't implement something like that.

[stems]

What is best to use timestamp or record version. If one decides to use say ORACLE istead of MS-SQL does tmestamp create any problems. What about having a trigger and increasing the version number by one.