What is best practice for blocking a dataRow in my Access-db (or any other db)?

[fabianus]

I would like to block a DataRows in my database for the time a user is updating the data. Not only while doing the sql-order, but let's say for a couple of minutes, while the user is changing the data of … whatever – for example on a client.

I thought about creating a table called "blockings" and each time somebody is accessing a datarow in a table, write into this "blockings" table the tablename, the id of the row and the computername that is blocking the row. Each time that somebody wants to access any row, I first check if there is any blocking entered into "blockings".

What do you thing about this? It is quite laborious, is it the right way to go?

I do not know anything about ADO.NET and as my problem is a problem that slightly any company with several computers accessing the same db has, I suppose that there are great solutions for this…
(I heard about transactions - is this something to go deeper in, or doesn't it solve long term blockings (for several minutes, max. 1 hour))

It would be a great help if anybody could give me a hint where I should continue to look at. I would like to find a general and "good practice" way to do it.

Thank you very much!

Regards,
Fabian

[Pirate]

Ok , you mean locking a record from being altered or deleted ??? If so , read this I think it helps : http://www.dotnet247.com/247referenc...24/124232.aspx

[fabianus]

Hi Pirate,

thank you for this link.

I carfully read the whole discussion. But something that I do not understand is why some of the cracks seem to defend the opinion, that pessimistic concurrency is "not good".
I really don't understand that. To me this is not defendable. Imagine several users on the same client (for example in a bank...). And everybody is making changes - how this could be resolved diffrently than with pessimistic concurrency?

Would be nice if somebody could give me a little answer :-)

Regards,
Fabian

[vincentg]

I think one of the best way is making some log file record for every modification or updates and adding or deleting records...
I usually put extra field which the application puts the information about who update it or modified it or add it or delete it...

It helps a bit on user tracking...

[Edneeis]

A strongly typed dataset will handle concurrency issues for you. It does so by sending both the original and new values for updates and throwing an exception if the record to update has changed since it was loaded into the dataset.

Personally I don't think concurrency issues are as big a problem as they sound like, but it really depends on the application and what it does.

[fabianus]

Hi Edneeis,


let's take the case of a big company. Let's say that there are (as it is a big big comany :-) 5 users working on the same supplier. The first who starts to work on it is the CEO, then comes desk-man, etc. The 5 are working at the same time.
Now the CEO finished, he'll save (no problem).
Now the desk-man finishes, he'll save... problem ! Let's say that the exception is handeled, so the only way out of this is to decide: do I write over this or that info that has changed, or don't I? Now the deskman is in the situation where he has to decide for something that he has not in minde - and might even not be able to understand (as it is CEO decision)... And what hapens when the fifth person makes his save after all the others...

In cases like theses isn't pessimistic concurrency the only way to go?

Thank you very much for your opinion.

Regards,
Fabian

-----------------

Hi vincentg,

that's how I thought to go, with a slight diffrence : I thought to create one table to deal with these data, so that I do not have to bothere about it anymore. Would this represent a serious loss of speed?

Regards,
Fabian

[Edneeis]

Take that same situation and say the deskman was the first in now the CEO of the company wants to see the same record but can't because he is locked out. That is even worse in my opinion besides generally business processes divid up work into manageable units so the likely hood of multiple users needing to edit the same data at the same time isn't very likely. If it is then that is a poor business process because multiple people are doing the work of one.

If the two options are:
1 - Only one person can be in a record at a time
2 - If by chance someone does edit the data that has changed since they started editing they get an exception

Then I personally choose 2. In the event of the exception the data could be compared or programatically merged depending on the data but being locked out of something you need, even if you only need read-only to it means I have to keep trying until the other person is done and I lose productivity or I have to contact that person to ask them to get out and that is annoying and may create issues of who needs it most.