Lock only one row in update (lock a row)
-
Table definition
USE [desarrollo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[reque_pa](
[CEREQ] [decimal](4, 0) NOT NULL,
[CENTR] [decimal](4, 0) NOT NULL,
[SUCUR] [decimal](4, 0) NOT NULL,
[EMPRE] [decimal](4, 0) NOT NULL,
[NOMBR] [char](45) NOT NULL,
[TINFO] [decimal](2, 0) NOT NULL,
[FONUM] [decimal](1, 0) NOT NULL,
[PROXIMO] [decimal](8, 0) NOT NULL,
[PENDIENTE] [decimal](8, 0) NOT NULL,
[MINIMO] [decimal](8, 0) NOT NULL,
[MAXIMO] [decimal](8, 0) NOT NULL,
[FECIP] [date] NULL,
[FECIE] [date] NULL,
[ETAPA] [decimal](2, 0) NOT NULL,
[FEALT] [date] NULL,
[MODIDAL] [char](3) NOT NULL,
[FEBAJ] [date] NULL,
[MODIDBJ] [char](3) NOT NULL,
[FEMOD] [datetime] NOT NULL,
[MODID] [char](4) NOT NULL,
[CLUNI] [decimal](16, 0) IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[reque_pa] SET (LOCK_ESCALATION = DISABLE)
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_CEREQ] DEFAULT ((0)) FOR [CEREQ]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_CENTR] DEFAULT ((0)) FOR [CENTR]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_SUCUR] DEFAULT ((0)) FOR [SUCUR]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_EMPRE] DEFAULT ((0)) FOR [EMPRE]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_NOMBR] DEFAULT ('') FOR [NOMBR]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_TINFO] DEFAULT ((0)) FOR [TINFO]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_FONUM] DEFAULT ((0)) FOR [FONUM]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_PROXIMO] DEFAULT ((0)) FOR [PROXIMO]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_PENDIENTE] DEFAULT ((0)) FOR [PENDIENTE]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_MINIMO] DEFAULT ((0)) FOR [MINIMO]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_MAXIMO] DEFAULT ((99999999)) FOR [MAXIMO]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_FECIP] DEFAULT (NULL) FOR [FECIP]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_FECIE] DEFAULT (NULL) FOR [FECIE]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_ETAPA] DEFAULT ((0)) FOR [ETAPA]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_FEALT] DEFAULT (NULL) FOR [FEALT]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_MODIDAL] DEFAULT ('') FOR [MODIDAL]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_FEBAJ] DEFAULT (NULL) FOR [FEBAJ]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_MODIDBJ] DEFAULT ('') FOR [MODIDBJ]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_FEMOD] DEFAULT (getdate()) FOR [FEMOD]
GO
ALTER TABLE [dbo].[reque_pa] ADD CONSTRAINT [DF_reque_pa_MODID] DEFAULT ('') FOR [MODID]
GO
Index definition
USE [desarrollo]
GO
CREATE UNIQUE NONCLUSTERED INDEX [RequeUnico] ON [dbo].[reque_pa]
(
[SUCUR] ASC,
[EMPRE] ASC,
[CEREQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
Note in table definition:
ALTER TABLE [dbo].[reque_pa] SET (LOCK_ESCALATION = DISABLE)
Note in unique index definition
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF
QUERY 1 => Transaction 1.
Intent to lock row with select statement. Try to lock cereq = 1 row
begin transaction
SELECT * FROM Reque_pa with (updlock) WHERE CEREQ = 1
WAITFOR DELAY '00:00:10';
commit transaction
QUERY 2 => Transaction 2
Try to lock cereq = 2 row
begin transaction
SELECT * FROM Reque_pa with (updlock) WHERE CEREQ = 2
commit transaction
see locks if it helps
-
WITH (HOLDLOCK XLOCK ROWLOCK) is probably what you're looking for if you don't want anyone to be able to read the row.
-
abitguru (3/25/2013)
[/b]begin transaction
SELECT * FROM Reque_pa with (updlock) WHERE CEREQ = 1
WAITFOR DELAY '00:00:10';
commit transaction
This is seriously exactly what you have in your application, a select with a waitfor? Just select, wait 10 seconds then do nothing else and commit the transaction?
btw, the reason you are getting page locks is because you have no useful index for that query, so SQL is having to read the entire table, read and lock the entire table. Fix the indexing and you'll very likely get the row locks you want without all the locking hint shenanigans.
Proper query, proper index first, index or lock hints if you still need them after you have proper query and proper index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (3/25/2013)
abitguru (3/25/2013)
begin transaction
SELECT * FROM Reque_pa with (updlock) WHERE CEREQ = 1
WAITFOR DELAY '00:00:10';
commit transaction
This is seriously exactly what you have in your application, a select with a waitfor? Just select, wait 10 seconds then do nothing else and commit the transaction?
btw, the reason you are getting page locks is because you have no useful index for that query, so SQL is having to read the entire table, read and lock the entire table. Fix the indexing and you'll very likely get the row locks you want without all the locking hint shenanigans.
Proper query, proper index first, index hints if you still need them after you have proper query and proper index.
No, I dont have a wait for in the app, it's only for test purposes.
Thanks GilaMonster I will test with an index on cereq field, an post the results.
Thanks again for your help.
McSQL (3/25/2013)
WITH (HOLDLOCK XLOCK ROWLOCK) is probably what you're looking for if you don't want anyone to be able to read the row.Thanks McSQL, when I try those hits, I can't access other ID when I select ID 1 for example.
I'll try as GilaMonster suggested.
Thanks both!
-
abitguru (3/25/2013)
Thanks McSQL, when I try those hits, I can't access other ID when I select ID 1 for example.Probably because SQL is taking row locks on every single row of the table, because it has to read every single row (no useful index) and you've explicitly told it to take those locks as update locks and to hold those locks til the end of the transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Not exactly sure how you are using this table, but you should limit the time that a record is locked to as short a time as possible. Users attempting to use the same ID should not have their transaction error out or be blocked for an excessive period because someone else happens to be using that same ID in that one table.
-
GilaMonster (3/25/2013)
abitguru (3/25/2013)
Thanks McSQL, when I try those hits, I can't access other ID when I select ID 1 for example.Probably because SQL is taking row locks on every single row of the table, because it has to read every single row (no useful index) and you've explicitly told it to take those locks as update locks and to hold those locks til the end of the transaction.
Yup that's right, RID lock on every row in the table.
Now I add this index. On the field I use in where clause
USE [desarrollo]
GO
CREATE NONCLUSTERED INDEX [cereq] ON [dbo].[reque_pa]
(
[CEREQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
I have the same results,
Am I understating you well GilaMonster ?
Lynn Pettis (3/25/2013)
Not exactly sure how you are using this table, but you should limit the time that a record is locked to as short a time as possible. Users attempting to use the same ID should not have their transaction error out or be blocked for an excessive period because someone else happens to be using that same ID in that one table.Lynn thanks. In this case I want to do this, because its a parameter table, if one user is saving something, no one can access the parameter until the transation is finish.
I understand your point, but in this case I need to block any attempt to read or update the lock row until transaction who blocks ends.
The main problem is that one transaction blocks severals rows in the table that I don't want, I only want to block one row.
-
Actually, I don't think you do. Instead of working to keep others from reading/updating a particular parameter row (again, not sure how you are using this), you should be working to limit how long the row is locked to begin with. The faster the row is updated and released by one transaction the sooner another transaction that needs the same row can use it.
The way you are going you are creating a potential performance issue as the number of transactions increase that need specific parameter values.
-
Lynn Pettis (3/25/2013)
Actually, I don't think you do. Instead of working to keep others from reading/updating a particular parameter row (again, not sure how you are using this), you should be working to limit how long the row is locked to begin with. The faster the row is updated and released by one transaction the sooner another transaction that needs the same row can use it.The way you are going you are creating a potential performance issue as the number of transactions increase that need specific parameter values.
Now I'm using it with (updlock) hint, but with page lock.
Now I'm try yo avoid page lock, and lock only register I need (with page lock, sql server locks many register until transaction ends).
I need the lock to ensure that two users not recibe the same value of the parameter (I explain myself well?)
Thats why I want a rowlock, to prevent other users wait becouse sql takes a page lock. (keep in mind that this table has 20 rows aprox)
-
abitguru (3/25/2013)
Lynn Pettis (3/25/2013)
Actually, I don't think you do. Instead of working to keep others from reading/updating a particular parameter row (again, not sure how you are using this), you should be working to limit how long the row is locked to begin with. The faster the row is updated and released by one transaction the sooner another transaction that needs the same row can use it.The way you are going you are creating a potential performance issue as the number of transactions increase that need specific parameter values.
Now I'm using it with (updlock) hint, but with page lock.
Now I'm try yo avoid page lock, and lock only register I need (with page lock, sql server locks many register until transaction ends).
I need the lock to ensure that two users not recibe the same value of the parameter (I explain myself well?)
Thats why I want a rowlock, to prevent other users wait becouse sql takes a page lock. (keep in mind that this table has 20 rows aprox)
No, you haven't explained this at all. I for one have no idea how these parameter rows are being used or why they need to locked for the duration that transaction uses them (heck, not even sure what these transactions are doing in the first place).
Again, you are creating a performance bottleneck by trying to keep the rows locked for the duration of a transaction using each row instead of working to limit how long a given transaction needs to have a given row locked to begin with.
-
Lynn Pettis (3/25/2013)
abitguru (3/25/2013)
Lynn Pettis (3/25/2013)
Actually, I don't think you do. Instead of working to keep others from reading/updating a particular parameter row (again, not sure how you are using this), you should be working to limit how long the row is locked to begin with. The faster the row is updated and released by one transaction the sooner another transaction that needs the same row can use it.The way you are going you are creating a potential performance issue as the number of transactions increase that need specific parameter values.
Now I'm using it with (updlock) hint, but with page lock.
Now I'm try yo avoid page lock, and lock only register I need (with page lock, sql server locks many register until transaction ends).
I need the lock to ensure that two users not recibe the same value of the parameter (I explain myself well?)
Thats why I want a rowlock, to prevent other users wait becouse sql takes a page lock. (keep in mind that this table has 20 rows aprox)
No, you haven't explained this at all. I for one have no idea how these parameter rows are being used or why they need to locked for the duration that transaction uses them (heck, not even sure what these transactions are doing in the first place).
Again, you are creating a performance bottleneck by trying to keep the rows locked for the duration of a transaction using each row instead of working to limit how long a given transaction needs to have a given row locked to begin with.
Thanks Lynn!!!
Several post later I explain what I'm try to do. 😛
I understand what you say about bottleneck, but I have no option (in my knowleage :-P). Because I can't repeat values that i get from the parameter table
This is my best option, rightnow.
Only lock one row for update and maintein this row locked until the transaction ends. I know and understand that other transactions must wait until this firts transaction ends, but I need this way to preserve integrity. (If two transaction read the same row at the same time will get the same parameter and I don't want this)
Right now I use updlock hint, but this makes a page lock and locks other rows that I dont want to be locked. (others rows that can be used for other transactions)
example, I lock row number 1 (transaction 1), row number 2,3,4 or 5 are avaible to be used by other transaction
Now with updlock, all rows are locked by (transaction 1)
understand my point?
So I think... if I can lock only the row i need, other transactions can go ahead, and dont have to wait for transaction 1 to finish because a page lock.
-
abitguru (3/25/2013)
Thanks Lynn!!!
Several post later I explain what I'm try to do. 😛
I understand what you say about bottleneck, but I have no option (in my knowleage :-P). Because I can't repeat values that i get from the parameter table
This is my best option, rightnow.
Only lock one row for update and maintein this row locked until the transaction ends. I know and understand that other transactions must wait until this firts transaction ends, but I need this way to preserve integrity. (If two transaction read the same row at the same time will get the same parameter and I don't want this)
Right now I use updlock hint, but this makes a page lock and locks other rows that I dont want to be locked. (others rows that can be used for other transactions)
example, I lock row number 1 (transaction 1), row number 2,3,4 or 5 are avaible to be used by other transaction
Now with updlock, all rows are locked by (transaction 1)
understand my point?
So I think... if I can lock only the row i need, other transactions can go ahead, and dont have to wait for transaction 1 to finish because a page lock.
Don't you already have a check constraint against the table to prevent the duplicates you are talking about from your parameters?
a check constraint, normal updates and some error handling for if the check constraint gets violated sounds like all you need.
To me it sounds like you are second guessing how well SQL server handles updates; I've seen throughputs in the thousands of updates per second on the same table handled automatically by SQL, but you seem to think there's a better way to do it.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (3/25/2013)
abitguru (3/25/2013)
Thanks Lynn!!!
Several post later I explain what I'm try to do. 😛
I understand what you say about bottleneck, but I have no option (in my knowleage :-P). Because I can't repeat values that i get from the parameter table
This is my best option, rightnow.
Only lock one row for update and maintein this row locked until the transaction ends. I know and understand that other transactions must wait until this firts transaction ends, but I need this way to preserve integrity. (If two transaction read the same row at the same time will get the same parameter and I don't want this)
Right now I use updlock hint, but this makes a page lock and locks other rows that I dont want to be locked. (others rows that can be used for other transactions)
example, I lock row number 1 (transaction 1), row number 2,3,4 or 5 are avaible to be used by other transaction
Now with updlock, all rows are locked by (transaction 1)
understand my point?
So I think... if I can lock only the row i need, other transactions can go ahead, and dont have to wait for transaction 1 to finish because a page lock.
Don't you already have a check constraint against the table to prevent the duplicates you are talking about from your parameters?
a check constraint, normal updates and some error handling for if the check constraint gets violated sounds like all you need.
To me it sounds like you are second guessing how well SQL server handles updates; I've seen throughputs in the thousands of updates per second on the same table handled automatically by SQL, but you seem to think there's a better way to do it.
The check constrait is to prevent repeated IDs of parameters, not the value.
The value of the parameters is increasing and is managed by the application.
I want to confirm/check if there is a way to lock a single row, and meanwhile this row is locked, no one can access it (read, delete, update)
-
Nope, still no actual explaination as to how these parameters are used or updated. Just keep repeating what it is you want to do even if it is wrong.
-
Lynn Pettis (3/25/2013)
Nope, still no actual explaination as to how these parameters are used or updated. Just keep repeating what it is you want to do even if it is wrong.Sorry english is not my natural language and I can misunderstand something. problably I am wrong (not a pro in sql server 😀 )
Now a completly explanation of how this params are used. Hope this helps to clarify.
1 - To numerate internal requirement, I use a table where I store next number of interal requirement.
2 - A user can generate a internal requirement, so when I store this on the db, I take next number to save the internal requirement with this number.
3 - Sopouse two users want to save internal requirements at the same time. If I take next number (stored in param table), I get the same (if I dont lock the row or page).
4 - So, I think how I can prevent this situation?
Ok, when a user is saving a internal requirement, ask for next number, save the internal requirement and increase the parameter +1. Asking for next number and saving is in the same transaction, so I implement a lock to prevent two uses get the same next number.
5 - the second user have to wait until first user transaction ends.
Lynn this explanation helps to understand the problem? hope I understand your in this case
This is my case, what do you suggest? sorry if I insist on rowlock 😛
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply