June 20, 2018 at 4:47 am
Hi,
Can some one please help how can I acquire lock on rows while it is being worked by some one.
My requirement is if a record is being used by some one then it should not be available for other users to edit. But other users can able to see but cannot edit the rows.
Thanks
June 20, 2018 at 5:47 am
What do you mean by editting? As in, someone in an application is viewing the record? You'll need to implement your own "locked" system. For example, add a column (maybe called [Editting]) of the datatype bit, and set them all the 0 (probably with a default of 0). Then, when someone clicks the "edit" button in the application is tries to update the value to 1, provided the value isn't already. if it manages to change it, then the user can edit the record, and when the save/cancel the value is set back to 0.
I realise this is a vague answer, however. the question is as well, with little detail on the environment, data, software, scope, etc etc. For a much more specific answer, you'll need to write a far for specific question.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 20, 2018 at 5:53 am
Hi Thom,
Thanks for your reply. My requirement is like some thing like support system. From the application we can able to see issues and analysts need to work on them.
Suppose if an analyst starts working on an issue then it should not be available for other analysts to work on but it should be visible to others.
All the users can see the data but only one person can work at a time.
Thanks,
June 20, 2018 at 6:05 am
KGNH - Wednesday, June 20, 2018 5:53 AMHi Thom,Thanks for your reply. My requirement is like some thing like support system. From the application we can able to see issues and analysts need to work on them.
Suppose if an analyst starts working on an issue then it should not be available for other analysts to work on but it should be visible to others.All the users can see the data but only one person can work at a time.
Thanks,
That doesn't really add anything to your question, I'm afraid. SSC isn't a free code writing service (and we don't even know what language your application, website, other is written in.
It's up to you, or your developers, to implement a solution. You've only given us an idea of what your environment is, so all we can give you are ideas for a solution. If you have a specific question/issue, then post about that.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 20, 2018 at 6:22 am
KGNH - Wednesday, June 20, 2018 5:53 AMHi Thom,Thanks for your reply. My requirement is like some thing like support system. From the application we can able to see issues and analysts need to work on them.
Suppose if an analyst starts working on an issue then it should not be available for other analysts to work on but it should be visible to others.All the users can see the data but only one person can work at a time.
Thanks,
Have you considered changing your viewing application to include having data to represent the "assignee" ? There's no amount of magic SQL you can add or configuration item you can change that will suddenly and magically solve this problem for you. Even if you add a column to support having an assigned person, and then perhaps change the viewing application to support only allowing the "assigned" user to do any editing, that's not going to stop someone who has SSMS installed from being able to update that row. You may want to start looking at problem management software, where such locking may well be implemented already. No matter what you do, no one here is likely to be in a position to write the code needed, even if you come forward with the exact table details, as 1.) we're unpaid volunteers who are unlikely to have the kind of time needed to write such code, 2.) that's far more effort than could reasonably be expected from an online forum, and 3.) this is something you pay a consultant to do.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 6:22 am
I am not asking for a code. I am asking ways how we can achieve the requirement. Is there any way where we can implement the lock on the specific row when that is being started working from application
June 20, 2018 at 6:30 am
KGNH - Wednesday, June 20, 2018 6:22 AMI am not asking for a code. I am asking ways how we can achieve the requirement. Is there any way where we can implement the lock on the specific row when that is being started working from application
As I said, there is no special magic you just add to your SQL and it's dealt with. There is no such feature. You have to design and implement your methodology on your own, using only the T-SQL that SQL Server supports - and none of which says "LOCK THIS ROW"... Besides, have you given thought to the extraordinary set of problems that Microsoft would be foisting on it's SQL Server users if they DID provide such a feature? What would happen if an application crashed after locking several thousand rows? Who would be able to "UNLOCK:" said rows? If the application crashes, users would no longer have access to the SQL session they were in, so any code that would limit the unlock to the same session would thus be a problem. And then you need a DBA to run an UNLOCK, when he may well need to know exactly which rows to UNLOCK, which seems likely, given what would be needed to have the system already know which rows are locked would probably destroy performance. Way too complicated and even the thought process that idea engenders would probably have been rejected by the need for an RDBMS to be ACID compliant. You're asking for magic that doesn't exist. Sorry...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 6:34 am
An object can be locked at different levels, using hints, for example: select column
from
WITH (ROWLOCK)
-> Row Lock
or select column
from
WITH (TABLOCK)
-> Table Lock
You will find more info, here:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017
And other lock options, to suit your needs (maybe you should look at UPDLOCK as well).
But, normally the lock needed should be set by default, based on what is needed for that particular query.
If there's an issue with not being able to read the object, while there's a write happening, because of the lock, you can all the WITH (NOLOCK) hint, on your SELECT statements.
Be wary, though, when using the NOLOCK hint with the Dirty Reads situation. You will find more info on the NOLOCK in the Books Online link above.
June 20, 2018 at 6:39 am
ralu_k_17 - Wednesday, June 20, 2018 6:34 AMAn object can be locked at different levels, using hints, for example:select column
fromWITH (ROWLOCK)
-> Row Lockorselect column
fromWITH (TABLOCK)
-> Table LockYou will find more info, here:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017And other lock options, to suit your needs (maybe you should look at UPDLOCK as well).
But, normally the lock needed should be set by default, based on what is needed for that particular query.
If there's an issue with not being able to read the object, while there's a write happening, because of the lock, you can all the WITH (NOLOCK) hint, on your SELECT statements.
Be wary, though, when using the NOLOCK hint with the Dirty Reads situation. You will find more info on the NOLOCK in the Books Online link above.
That doesn't really answer the OPs questions here. They want to "lock" a row while they are editting the data at the client application; a rowlock won't help with that, and a table lock (while they are doing that) would mean no one could update any other rows while someone is amending a single row. Certainly not what the OP wants.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 20, 2018 at 8:17 am
ralu_k_17 - Wednesday, June 20, 2018 6:34 AMAn object can be locked at different levels, using hints, for example:select column
fromWITH (ROWLOCK)
-> Row Lockorselect column
fromWITH (TABLOCK)
-> Table LockYou will find more info, here:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017And other lock options, to suit your needs (maybe you should look at UPDLOCK as well).
But, normally the lock needed should be set by default, based on what is needed for that particular query.
If there's an issue with not being able to read the object, while there's a write happening, because of the lock, you can all the WITH (NOLOCK) hint, on your SELECT statements.
Be wary, though, when using the NOLOCK hint with the Dirty Reads situation. You will find more info on the NOLOCK in the Books Online link above.
Unfortunately, what Thom A said is accurate... that doesn't actually help the original poster, as they are looking to hold a row lock beyond the duration of a query, and that's just not practical in an RDBMS. The issues such a thing would cause would likely break ACID, so the very idea is just not practical. In any case, at least your thought process was in the direction of trying to help, and hopefully, you get to learn something in the process as well. The problems associated with trying to reach the ideal are numerous, as concurrency of data access at the update level requires serialization of the updates, and without any special provisions in place, a multiple updates to the same row scenario is always a "last-in wins" type of thing. Thus the difficulty in trying to prevent updates to a row that someone is working with needs special attention. You can do a number of things. You can add a column named IsUpdating as a bit, and you attempt to set it to 1 only when it's 0 to do a "virtual lock" on the row. You then add a trigger that is INSTEAD OF UPDATE, that only updates the row if that column is set to 1, and the update is including setting the value back to 0, or the update is only to set the value to 1 from a previous value of 0. If you then allow only the application's database user to have update access to the table, and not the actual users, you can prevent some user from using SSMS to mess with updating the table. That kind of technique or something fairly similar is usually necessary to successfully implement that "kind" of locking mechanism, and it has to be done in an application and the application designed around using that kind of methodology. Mind you, there is potential downside to this kind of locking mechanism, as it's always possible for a user to accidentally kick their PC power cord out of the socket under their desk, which blows away the SQL Session, and if they did that after acquiring such a "virtual lock", some administrator is going to need an id that can run a reset on that row (a "virtual unlock"), to restore the ability to update that row via the application. Which then means that the business needs to have a business process in place to not only control WHO can do that reset, but procedures for performing it along with developed and tested code to do the unlock. So it's not a simple technique, nor is it cost-free. You pay the price in additional development costs and ongoing additional business process costs.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 1:12 pm
My guess would be that you can grant update privileges on the table to one user, and restrict everyone else to read only privileges. Your application level will have to handle logging on to the "updater" account. We never spend time on DCL, and try to everything in DDL and DML.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 20, 2018 at 3:12 pm
KGNH - Wednesday, June 20, 2018 5:53 AM>> All the users can see the data but only one person can work at a time.<<My guess would be that you can grant update privileges on the table to one user, and restrict everyone else to read only privileges. Your application level will have to handle logging on to the "updater" account. We never spend time on DCL, and try to everything in DDL and DML.
Do you have any idea how useless that would be? One account would prevent anyone else from making updates, severely limiting productivity. It's pretty clear that multiple people have to be able to make updates, just not more than one of them to the same row during the duration of the first one to start editing. What you suggested indicates you didn't understand anything posted before.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 11:49 pm
Thanks every one for your suggestions
June 21, 2018 at 12:45 am
Thank you Thom and Steve!
Just realized I opened the question in my browser and left it open for a while and posted a reply without refreshing the content of the thread. So, I was replying to the original information, which I didn't totally get as it turns out.
And yes, that is my goal, for reading and replying to questions, to learn, both to read properly and to grow technically.
Thanks again!
June 22, 2018 at 12:26 pm
KGNH - Wednesday, June 20, 2018 5:53 AM
Do you have any idea how useless that would be? One account would prevent anyone else from making updates, severely limiting productivity. It's pretty clear that multiple people have to be able to make updates, just not more than one of them to the same row during the duration of the first one to start editing. What you suggested indicates you didn't understand anything posted before.
The whole point of using DCL to control access to the database is to limit it to one user! Many decades ago, I consulted for a company that did not have any controls. All the developers were given full data admin privileges on an Informix database. And they all used it.. In particular, they were getting information on doctors from two different sources (Medata and Phoenix Data). The updates to the database which salesman used varied from day-to-day, depending who had loaded which tape when. The two systems had different encoding schemes, different timeliness, etc. This was so long ago that the CASS standards had not been established for adresses, etc.
When we moved the privileges away from the developers, and put it in the control of the gatekeeper for that particular database, we finally got consistent quality. Suddenly the doctor specialties were encoded the same way, the addresses were all formatted the same way, the phone numbers were checked for validity, etc. perhaps even more important, was the fact that we now had single entry points and assigned responsibility for the data we were using. We knew when the tapes are going to be loaded. We knew that they were going to be loaded only after they had gotten a good data scrub.
Later, he found another advantage to using the DCL. While not 100% portable from one database to the other (yes, there are shops that have more than one kind of database or who do business with companies that have another database product), the DCL scripts were close enough, so it was easy to port them. And since you were using a standard DCL, whenever one of the vendors improved their database product, we simply recompiled and got the benefits.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply