Hello,
i have a table called Codes and having below columns
Declare table Codes(IdCode int identity(1,1) primary key, EmpCode varchar(10) unique key, AllotedStatus bit default 0)
i will get list of EmpCode from the client and i will fill it in this table. my requirement is, users will login to our website and request for the code. it can happen simultaneously. how do i enforce that code can be shared the user in a way one code should not be shared to more than one user. is there a way to lock the record until gets allotted?
please share any sample script how to achieve this.
Thank you.
November 21, 2019 at 6:36 pm
Google "sql server programming for concurrency" or "sql server concurrency issues".
You'll want to know what a "dirty read" is, "repeatable reads", "serializable", "read committed snapshot", "(read /write) skew", etc. or maybe "transaction isolation levels" in general.
You'll want to know how to safely generate session keys, how to protect one concurrent user from taking over another concurrent users' session, etc. While you're reading, save some time for protecting against SQL injection or other web exploits. Also, permissions are a valid concern too.
That's just what I can think of off the top of my head.
November 21, 2019 at 7:52 pm
Indirectly, yes there is, and it's a guaranteed locking approach, since it's provided by the db engine itself, and uses its own locking mechanisms internally.
Use system procs sys.sp_getapplock and sys.sp_releaseapplock.
Before accessing the data, your code would check for an existing app lock. If there was none, the code would take the lock. Once that user was done with that data, your code would free the lock.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 22, 2019 at 2:18 am
Indirectly, yes there is, and it's a guaranteed locking approach, since it's provided by the db engine itself, and uses its own locking mechanisms internally.
Use system procs sys.sp_getapplock and sys.sp_releaseapplock.
Before accessing the data, your code would check for an existing app lock. If there was none, the code would take the lock. Once that user was done with that data, your code would free the lock.
Lordy, be wicked conscious of what you're actually doing if you use sys.sp_getapplock. It's advertised as preventing something from running more than once and it kind of does that but at great expense. It has to to run to determine if it can be run. That means that it takes its place in scheduling and it actually does take a relatively "good" amount of time. Depending on some different factors, you can get a lot of long term blocking (like I did) simply because a shedload of such requests piled up and virtually excluded every thing else from running.
I can't remember offhand how we fixed it in the proc we wrote but I do remember that we did remove the use of sp_getapplock. We do have some smaller uses of it running (which I still think are incredibly stupid) that aren't causing a similar problem simply because they're rarely called. I can dig out what our fix was for the big problem tomorrow if someone needs it but it was alternative code to avoid having to use sp_getapplock.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2019 at 11:47 am
Thanks a lot Jeff and Scott for the reply. i would request to provide some same script to achieve this. this will help me to learn as well. so please provide the sample script to achieve this requirement.
November 22, 2019 at 12:56 pm
Hello,
i have a table called Codes and having below columns
Declare table Codes(IdCode int identity(1,1) primary key, EmpCode varchar(10) unique key, AllotedStatus bit default 0)i will get list of EmpCode from the client and i will fill it in this table. my requirement is, users will login to our website and request for the code. it can happen simultaneously. how do i enforce that code can be shared the user in a way one code should not be shared to more than one user. is there a way to lock the record until gets allotted?
please share any sample script how to achieve this.
Thank you.
Thanks a lot Jeff and Scott for the reply. i would request to provide some same script to achieve this. this will help me to learn as well. so please provide the sample script to achieve this requirement.
So, to be absolutely clear... you're saying that you want to find any single available EmpCode that has an AllocatedStatus of 0 and return that EmpCode ensuring that no other simultaneous request can grab the same EmpCode... that also meas setting the AllocateStatus to 1 at the same time... is all of that correct?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2019 at 1:22 pm
What Jeff wrote is enough to scare me away from even trying this approach. Using internal locking could enable end-users to do things which produce unpredictable performance. You wrote "users will login to our website and request for the code." If they login then you have a user id? Could you insert the code with the user id in a new table with a unique constraint? Then check against the new table when codes are requested? What is the AllotedStatus column and how is it maintained? What happens when all of the codes are allotted?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 22, 2019 at 1:48 pm
What Jeff wrote is enough to scare me away from even trying this approach. Using internal locking could enable end-users to do things which produce unpredictable performance. You wrote "users will login to our website and request for the code." If they login then you have a user id? Could you insert the code with the user id in a new table with a unique constraint? Then check against the new table when codes are requested? What is the AllotedStatus column and how is it maintained? What happens when all of the codes are allotted?
And, what happens if a connection is lost and hasn't returned the token yet by setting the AlocatedStatus column back to 0 whether or not you're using sp_getapplock?
Yeah... I agree... these types of things scare the hell out of me but I can at least show them how to avoid some pretty nasty blocking.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2019 at 4:05 pm
ScottPletcher wrote:Indirectly, yes there is, and it's a guaranteed locking approach, since it's provided by the db engine itself, and uses its own locking mechanisms internally.
Use system procs sys.sp_getapplock and sys.sp_releaseapplock.
Before accessing the data, your code would check for an existing app lock. If there was none, the code would take the lock. Once that user was done with that data, your code would free the lock.
Lordy, be wicked conscious of what you're actually doing if you use sys.sp_getapplock. It's advertised as preventing something from running more than once and it kind of does that but at great expense. It has to to run to determine if it can be run. That means that it takes its place in scheduling and it actually does take a relatively "good" amount of time. Depending on some different factors, you can get a lot of long term blocking (like I did) simply because a shedload of such requests piled up and virtually excluded every thing else from running.
I can't remember offhand how we fixed it in the proc we wrote but I do remember that we did remove the use of sp_getapplock. We do have some smaller uses of it running (which I still think are incredibly stupid) that aren't causing a similar problem simply because they're rarely called. I can dig out what our fix was for the big problem tomorrow if someone needs it but it was alternative code to avoid having to use sp_getapplock.
This is really the perfect scenario for using sp_getapplock. And, trying instead to write your own accurate and efficient locking mechanism (effectively a "mini-app") inside SQL is a formidable task. I say stick with the fully tested method MS already provides.
Used properly, it would literally be impossible to get deadlocks, I would think, as long as you were careful enough to specify a lock timeout limit on the sp_getapplock call; if you didn't, you could get deadlocks, but that's actually your own fault. In the type of scenario of above, one would typically specify 0 as the timeout, i.e., if SQL can't get the lock, it notifies you of that *immediately*.
Typically in this situation, all you need to know is, "is this resource free or not", you don't really need to wait on the resource. If it's not available, skip that resource and try the next resource to see if it can be assigned.
Yes, you could have "left-over" locks, but you can generally automatically free those after a certain period of time. You can see them via sys.dm_tran_locks.
I don't have time to lay out the full details of this now, but I will work on posting it later when I get time.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 23, 2019 at 1:35 am
thanks for the explanation scott and i would request to give me the sample script to achieve this scenario.
November 23, 2019 at 3:05 am
I think you just need to write a stored procedure that's called when a new code is to be allocated.
It just needs the correct locking hints specified on the query.
Maybe something like this:
CREATE PROCEDURE GetNextCode AS
BEGIN
BEGIN TRANSACTION;
WITH CTE AS
(
SELECT TOP(1) *
FROM dbo.Codes c WITH (XLOCK ROWLOCK READPAST)
WHERE c.AllotedStatus=0
ORDER BY c.IdCode
)
UPDATE c
SET c.AllotedStatus=1
OUTPUT inserted.EmpCode
FROM CTE c
COMMIT TRANSACTION
END
GO
November 23, 2019 at 1:32 pm
Thank you Jonathan for the script. Scott mentioned about sp_getapplock. shouldn't we considered those advice. again, i am bringing this point to make the perfect solution. i gone through the some of the articles and some of the people saying to use sp_getapplock and some of saying not to use. i mean some issues might come. i can understand from scott's paint that it is tested from Microsoft and it developed for this scenario. since i am developing the real time application, any issue will impact the users of my website.
Hi Jeff,
as you mentioned in the previous thread, you came across this situation and you have solved this issue in different way. would you be able to pitch in and give your piece of code for this situation. the reason for asking different solutions because to learn and i am sure this thread will be helpful to others too who come across this requirement.
Hi Scott,
Please share the script that you could provide with sp_getapplock
November 24, 2019 at 7:27 pm
Hi Jeff,
as you mentioned in the previous thread, you came across this situation and you have solved this issue in different way. would you be able to pitch in and give your piece of code for this situation. the reason for asking different solutions because to learn and i am sure this thread will be helpful to others too who come across this requirement.
You haven't answered my previous post yet where I asked for rock solid clarification on the problem at hand so I don't have to guess. Here's that question again.
KGJ-Dev wrote:Hello,
i have a table called Codes and having below columns
Declare table Codes(IdCode int identity(1,1) primary key, EmpCode varchar(10) unique key, AllotedStatus bit default 0)i will get list of EmpCode from the client and i will fill it in this table. my requirement is, users will login to our website and request for the code. it can happen simultaneously. how do i enforce that code can be shared the user in a way one code should not be shared to more than one user. is there a way to lock the record until gets allotted?
please share any sample script how to achieve this.
Thank you.
KGJ-Dev wrote:Thanks a lot Jeff and Scott for the reply. i would request to provide some same script to achieve this. this will help me to learn as well. so please provide the sample script to achieve this requirement.
So, to be absolutely clear... you're saying that you want to find any single available EmpCode that has an AllocatedStatus of 0 and return that EmpCode ensuring that no other simultaneous request can grab the same EmpCode... that also meas setting the AllocateStatus to 1 at the same time... is all of that correct?
If so, can you provide some readily consumable data for the Codes table that you cited in your original post, please? Please see the article at the first link in my signature line below for how to do that so I can help you with this the best.
And I was having a helluva time remembering what the original problem was that I solved without using sp_GetAppLock but Jonathan's code reminded me. Jonathan's code is nearly correct but his still requires a BEGIN TRANSACTION. Mine doesn't and I can guarantee no dead locks and virtually no blocking even under very rapid fire.
I'll also talk more about why sp_GetAppLock is such a bad idea in cases like this when I get the clarification and the data I've asked for above.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2019 at 10:33 pm
KGJ-Dev wrote:Hi Jeff,
as you mentioned in the previous thread, you came across this situation and you have solved this issue in different way. would you be able to pitch in and give your piece of code for this situation. the reason for asking different solutions because to learn and i am sure this thread will be helpful to others too who come across this requirement.
You haven't answered my previous post yet where I asked for rock solid clarification on the problem at hand so I don't have to guess. Here's that question again.
Jeff Moden wrote:KGJ-Dev wrote:Hello,
i have a table called Codes and having below columns
Declare table Codes(IdCode int identity(1,1) primary key, EmpCode varchar(10) unique key, AllotedStatus bit default 0)i will get list of EmpCode from the client and i will fill it in this table. my requirement is, users will login to our website and request for the code. it can happen simultaneously. how do i enforce that code can be shared the user in a way one code should not be shared to more than one user. is there a way to lock the record until gets allotted?
please share any sample script how to achieve this.
Thank you.
KGJ-Dev wrote:Thanks a lot Jeff and Scott for the reply. i would request to provide some same script to achieve this. this will help me to learn as well. so please provide the sample script to achieve this requirement.
So, to be absolutely clear... you're saying that you want to find any single available EmpCode that has an AllocatedStatus of 0 and return that EmpCode ensuring that no other simultaneous request can grab the same EmpCode... that also meas setting the AllocateStatus to 1 at the same time... is all of that correct?
your understanding is absolutely correct.
If so, can you provide some readily consumable data for the Codes table that you cited in your original post, please? Please see the article at the first link in my signature line below for how to do that so I can help you with this the best.
And I was having a helluva time remembering what the original problem was that I solved without using sp_GetAppLock but Jonathan's code reminded me. Jonathan's code is nearly correct but his still requires a BEGIN TRANSACTION. Mine doesn't and I can guarantee no dead locks and virtually no blocking even under very rapid fire.
I'll also talk more about why sp_GetAppLock is such a bad idea in cases like this when I get the clarification and the data I've asked for above.
Thanks a lot for your reply and i have below script will generate the sample data for the sample script. thanks you for your precious time for making the sample script. also, please correct me if i did any mistakes in test data.
IF OBJECT_ID('TempDB..#Codes', 'U') IS NOT NULL
DROP TABLE #Codes
--===== Create the test table with
CREATE TABLE #Codes (
IdCode INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
,EmpCode VARCHAR(4000)
,AllotedStatus BIT DEFAULT 0
,CreatedDate DATETIME DEFAULT getdate()
,LastModifiedDate DATETIME
,CONSTRAINT UC_EmpCode UNIQUE (EmpCode)
)
INSERT INTO #Codes (EmpCode)
SELECT TOP 1000000 'Code00' + cast(ROW_NUMBER() OVER (
ORDER BY (
SELECT NULL
)
) AS VARCHAR)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
--select * from #Codes;
--drop table #Codes November 25, 2019 at 4:43 am
OK... You still haven't answered my question but, based on your acceptance of Jonathan's good code, I'll assume the answer is "Yes, that's all correct".
You asked for correction if you made any mistakes in the test data. For what you have, no. For what I think you should have, then not only mistakes in the test data but mistakes in the code table, as well.
First, and I'm sure that Scott Pletcher would enjoy and agree with this, as well, if you're going to keep the EmpCode column the way it is, there is absolutely zero reason to have the IdCode column. There's also no reason to waste the clustered index on it even if you decide to keep it. Put a non clustered PK on it if you must keep it.
As for the CreatedDate and LastModifiedDate... the CreatedDate is totally useless. Get rid of it. As for the LastModifiedDate, change the name of it to "AllocationDate".
Also, change the datatype of AllocationStatus from BIT to TINYINT. That's so you can use the value of the AllocationStatus as a sort of "timer" to play against the AllocationDate. If it's 0, it's not allocated. If it's 255, it never times out. If it's anything else, it's a "code" for how many units of time can pass since the AllocationDate before you log de-allocate them.
Also, to be more correct, change the name of the AllocationStatus column to AllocationType.
With all of that, here's the table I would use and the indexes I would put on it. And there's no way that I'd use a Varchar for the EmpCode but whatever. It's your stuff. But... YOU MUST NOT LEAVE IT AS A VARCHAR (4000). That's just nuts.
--===== If the test table exists, drop it to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#Codes', 'U') IS NOT NULL
DROP TABLE #Codes
;
GO
--===== Create the test table
CREATE TABLE #Codes
(
EmpCode VARCHAR(16) PRIMARY KEY
,AllocationType TINYINT DEFAULT 0
,AllocationDate DATETIME
)
;
--===== Populate the test table with data
INSERT INTO #Codes WITH (TABLOCK) --For Minimal Logging
(EmpCode)
SELECT EmpCode = 'Code00'+CONVERT(VARCHAR(10),t.N)
FROM dbo.fnTally(1,1000000) t
ORDER BY t.N --This is actually for Minimal Logging
OPTION (RECOMPILE) --Just a way to help Minimal Logging
;
CREATE INDEX IX_Unallocated ON #Codes (AllocationType,EmpCode)
WHERE AllocationType = 0
;
SELECT * FROM #Codes;
GO
Now, like I said, Jonathan did it very close to the way I do it except for a couple of things. The way we both do it (UPDATE in a CTE) means there's no need for a transaction and no need to invoke a locking hint for this. There's damned sure no reason to use sp_GetAppLock on this.
And, no... my code does NOT have a mistake in the update. You can set variables and columns in the same update and it IS documented in the official MS documentation for UPDATE. You can even do a SET @VariableName = ColumnName = expression that uses either, neither, or both.
Oh... there's one other thing. If Jonathan's proc runs out of numbers in the table, it returns nothing. If that happens to mine, it returns a NULL. Neither is better than the other but I wanted you to know there's a difference. My personal preference is, off course, to return a NULL for such cases.
CREATE PROCEDURE dbo.GetEmpCode
(
@AllocationType TINYINT
,@EmpCode VARCHAR(20) NULL OUTPUT
)
AS
WITH cte AS
(
SELECT TOP 1 *
FROM #Codes
WHERE AllocationType = 0
)
UPDATE cte
SET AllocationType = @AllocationType
,AllocationDate = GETDATE()
,@EmpCode = EmpCode
;
To use the stored procedure, you can do it as follows...
DECLARE @EmpCode VARCHAR(20);
EXEC dbo.GetEmpCode 1,@EmpCode=@EmpCode OUTPUT;
SELECT @EmpCode;
You can write a stored procedure to set a code back to 0 in a similar fashion.
Personally, it would be easier to just use a NEWID() and call it a day.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply