Pessimistic Concurrency

Where to start the transaction for a pessimistic concurrency????

My question is : Isn't true when dealing with pessimistic concurrency the transaction has to start in the client?
In other words, for example, you have a C# client window application that retrieves a record for updating from
a SQL Server database. You retrieve the record and you update it on the window screen  and send it back to the sql server ---in this process you don't want anyone else grabbing that record for possible updating  and so you must start the transaction on the client side and not in say a stored procedure on the sql server database.

Is that correct what I am saying?
Lawrence AverySystem DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lawrence AverySystem DeveloperAuthor Commented:
Thank you aikimark
The transaction is handled at the server side. Always.

If two clients invoke same process which starts the transaction then the only first one will succeed and all others must wait or obtain some deadlock or timeout error.

Thus you may start the transaction or lock in any SP and the SQL Server engine will do the rest for you or for other clients doing the same thing.

Issuing the BEGIN TRANSACTION command is processed at the server and the only client role is to post this command the the server and then wait for result.
Lawrence AverySystem DeveloperAuthor Commented:
Please read article below

What I am talking about is locking the records when I first read them and hold the lock  until my client side window updates them on the client side and then sends the updates back  to the server in the same transaction.
Then commit the update. and end the transaction. This way no one else can come in and grab the record and do update until you done all the necessary tasks to update. However, if you do that, the user could hold the records in lock mode until the user sends the update back to the server. Does this make sense to you?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

I am not sure who is asking the question now :-)

Yes, your post is correct but you've been asking something else:

Your concerns were about the "so you must start the transaction on the client side and not in say a stored procedure on the sql server database."

Almost everything is "started" by the client requests sent to the server. BUT the server alone decides what will be done and what will be refused. Thus even the Stored procedure which places the lock may be "started" by the client BUT the server decides for which client/process is the lock placed if there are more such requests from concurrent clients.

Pessimistic locking is let say more reliable BUT you cannot use it when the number of concurrent clients exceeds certain level. The application is then unusable because records are blocked for too long time.

Pessimistic locking means to lock the record before user starts to edit data and this approach avoids any data conflicts but brings delays to others... This is acceptable even for 10 concurrent users BUT all of them must be sitting in one small room so they can ask "Who locked my record?"

Once you place the lock nobody else can do the same thing so the question "Where to start the transaction for a pessimistic concurrency?" is irrelevant and you may start the transaction whenever you need it. Others are waiting or reading the error message...

And remember, you don't need to place the lock when you read the record first time but when the user decides to edit the record. Of course, depends on your application if it allows to implement lock this way.

Optimistic locking means "The last is the winner". In other words the client which saves his changes as the last one decides about the final data contents. Of course, techniques which compare the original data state against the current data state also exist. They may prevent unwanted data changes but they are more complex to implement.

I am always voting for optimistic data locking and if you need more advanced approach please read following articles:
Vadim RappCommented:
>  You retrieve the record and you update it on the window screen  and send it back to the sql server ---in this process you don't want anyone else grabbing that record for possible updating  and so you must start the transaction on the client side and not in say a stored procedure on the sql server database.

Is that correct what I am saying?

Yes, it's correct, but the problem is, when you implement this on practice, which is possible, in the next 15 minutes you will have a situation when employee A reads the record into the application, then leaves for lunch, or leaves for the day, or the phone rings, etc etc. Meanwhile, not only other people won't be able to read the same record, but any query from any application that would potentially include the locked record, would be locked out.

Worse yet, those others will have no idea what/who they are waiting for. Their first conclusion would be that their computer has hung and needs to be restarted. In our company we even wrote a special stored procedure that monitors if anybody is locked out, and sends a message to both parties so they become aware what's going on and usually cooperate.

Because of this problem, usually database applications don't lock the record for the interactive session, but when the record is saved back to the database, they verify if the record is the same as when it was read, and if not, which means that it had been changed by another user, they show an error. The real database locking occurs only for the short period of time of actual uploading the data.

If you need higher level of locking, you will need to implement it by yourself, probably basing on "check out/check in" concepts.

That said, it's possible to implement server-based locking that would achieve what you want. If you are sure you want it, we can show sample code that will do it.
Lawrence AverySystem DeveloperAuthor Commented:
If you could give me a sample of the check out/ check in concepts, that would be appreciated.
Vadim RappCommented:
I meant sample of "true locking" that you wanted.

Check in/check out would go like this: let's say, we want to implement it for the table1 with primary key PK1. We create new table table2:

PK1 - foreign key to table1.PK1
username - the user who checked out the record from table1

Then, when user1 checks out a record from table1, your code inserts a line into table2 with the PK of the record, and information about the client. Then user2 comes in and wants to check out the same record. Your code first checks if there's no record in table2 for this PK. Because there is, it tells user2 that this record had been checked out by user1 N hours ago (as an added bonus, it can even do active directory lookup by username of user1 and tell his phone number). You will have to decide what will be the next steps - ask user2 if it's OK to ignore user1; ignore automatically if check out happened more than X hours ago, and so forth. Accordingly, when user1 has finished working with the record, your application removes checkout record (or marks as checked in using yet another column - this may be very useful for audit purposes, to see who changed what and when). Note that this last step may never happen if the application crashes etc., which is why I mentioned the possibilities of overriding.
Lawrence AverySystem DeveloperAuthor Commented:
Ok. After reading comments on pessimistic concurrency, I think my question should of been the following:
 How do I indicate the record I am updating is locked to prevent other users from attempting to retrieve and update the same record while I the user  is viewing and editing the record on the client screen before sending it back to the database for update?
From what I have read above, you have to have record columns indicating it is locked and  possibly the user who has it locked. it could be in the same table or another table indicating the record is being modified at the moment. So check in/ check out it is..
What got me confused on this is I thought there might be a lock command to lock the record versus marking the record in a column indicating it has been checked out for update purposes.
   So bottom  line there is no such kind of lock - only a marking of the record in a particular column or possibly another table.  So when another user comes in to grab the record it looks at those specific columns for indication of a check out and check in situation.

AM I right????
If I am right,  I am going to give everyone credit because my question was not worded correctly?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
This has been mentioned already. You can apply a lock on row level in the DB. But depending on which lock you use, others are locked completely from getting information about the row or even the table, no matter what the intention is. A simple count(*) might be blocked, needing to wait for a lock timeout to proceed.

So managing the lock at the application level is a much better idea. Of course it does not work if there is access outside of the application, e.g. with data access tools.
Lawrence AverySystem DeveloperAuthor Commented:
Ok so there is no actual LOCK command to lock the specific record? If you were to say a lock command that would be for example the UPDATE command.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
There are lock commands e.g. in Oracle, but not in MSSQL. Here you have to provide lock hints, or change the Transaction Isolation Level.
Vadim RappCommented:
Since you are trying to achieve pessimistic concurrency from visual studio, note the following phrase from the article "Introduction to Data Concurrency in ADO.NET":

However, pessimistic concurrency control is not possible in a disconnected architecture. Connections are open only long enough to read the data or to update it, so locks cannot be sustained for long periods. Moreover, an application that holds onto locks for long periods is not scalable.
Note   If your underlying data source supports transactions, you can simulate pessimistic concurrency by updating your data within a transaction. For more information, see Transactions in ADO.NET.

So as you can see, simulation is the best you can do. As for the disconnected architecture:

ADO.NET Architecture
Data processing has traditionally relied primarily on a connection-based, two-tier model. As data processing increasingly uses multi-tier architectures, programmers are switching to a disconnected approach to provide better scalability for their applications.
Each approach has it own pros and cons...

Optimistic locking is simple to implement and what you see is saved...
Pessimistic locking can block other users.
Data changes check before saving requires more coding.
Etc. etc.

I wouldn't go the pessimistic locks way...

If you want to be perfect then check the record "consistency" before each write operation and if somebody else changed the data during your read/save operation then you would refuse the save request or ask user what to do. SQL Server offers record check sum calculation which is suitable for this task. But again, this will ensure data consistency to the last updating user. Others having the data displayed do not see the change until they do some refresh... So the app itself should refresh data periodically which in turn causes network traffic...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.