Solved

Pessimistic Concurrency

Posted on 2014-10-10
14
195 Views
Last Modified: 2014-10-15
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?
0
Comment
Question by:metro156
  • 5
  • 3
  • 3
  • +1
14 Comments
 

Author Comment

by:metro156
Comment Utility
Thank you aikimark
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 216 total points
Comment Utility
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.
0
 

Author Comment

by:metro156
Comment Utility
Please read article below

http://www.techrepublic.com/article/pessimistic-locking-solves-adonet-concurrency-problems/

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?
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 216 total points
Comment Utility
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:

http://msdn.microsoft.com/en-us/magazine/cc163924.aspx
http://www.techrepublic.com/article/how-to-handle-optimistic-concurrency-violations-in-adonet/
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 213 total points
Comment Utility
>  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.
0
 

Author Comment

by:metro156
Comment Utility
If you could give me a sample of the check out/ check in concepts, that would be appreciated.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 213 total points
Comment Utility
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
timestamp

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.
0
 

Author Comment

by:metro156
Comment Utility
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?
0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 71 total points
Comment Utility
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.
0
 

Author Comment

by:metro156
Comment Utility
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.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 213 total points
Comment Utility
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.
0
 
LVL 41

Accepted Solution

by:
pcelba earned 216 total points
Comment Utility
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...
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now