Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Use of Identity Property to Resolve Concurrency Issues Expand / Collapse
Author
Message
Posted Thursday, February 24, 2011 9:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
john.arnott (2/24/2011)
Or did I completely misunderstand the test framework?


John - I think there is a lot of interesting things that are misunderstood about this article. Could be the reason for the four pages of comments from people scratching thier heads.
Post #1069063
Posted Thursday, February 24, 2011 10:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Interesting solution to the problem, thanks for sharing!
Post #1069104
Posted Thursday, February 24, 2011 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 15, 2014 6:29 AM
Points: 4, Visits: 31
Hi,

There is a much easier solution for this problem:
alter table dbo.tbl_kvp
add constraint PK_tbl_kvp primary key(column_key);
go

Now not all rows are locked if you want to increment one key.
Only the row you are incrementing is locked, because of the clustered primary key.
I think that is the intended behavior, nl. block only that row that is being increment.

You can test this with following scripts:

1) insert an additional key SP2 in the table
2) run this script in a session
declare
@rc int,
@val int = 0;

begin transaction

exec @rc = dbo.USP_Get_Value_For_Key
@key = 'PR1',
@value = @val OUTPUT;
select @val
--commit transaction

3) run this script in another session
declare
@rc int,
@val int = 0;

begin transaction

exec @rc = dbo.USP_Get_Value_For_Key
@key = 'PR2',
@value = @val OUTPUT;
select @val

--commit transaction

==> This will result in a block

4) Create the clustered key

do the test again and NO blocking will occur!

Notice the plans
   UPDATE tbl_kvp SET column_value += 1 WHERE column_key = @key

|--Table Update(OBJECT[Test].[dbo].[tbl_kvp]), SET[Test].[dbo].[tbl_kvp].[column_value] = RaiseIfNullUpdate([Expr1004])))
|--Compute Scalar(DEFINE[Expr1004]=[Test].[dbo].[tbl_kvp].[column_value]+(1)))
|--Top(ROWCOUNT est 0)
|--Table Scan(OBJECT[Test].[dbo].[tbl_kvp]), WHERE[Test].[dbo].[tbl_kvp].[column_key]=[@key]) ORDERED)

with the clustered key
|--Clustered Index Update(OBJECT[Test].[dbo].[tbl_kvp].[PK_tbl_kvp]), SET[Test].[dbo].[tbl_kvp].[column_value] = RaiseIfNullUpdate([Expr1003])), DEFINE[Expr1003]=[Test].[dbo].[tbl_kvp].[column_value]+(1)), WHERE[Test].[dbo].[tbl_kvp].[column

Conclusion
So, I have show that the lack of constraints can lead to too much locking and this can have major impact on the system throughput. The optimizer is blind without constraints and/or indices and some other structures!

BTW, you can still optimize the increment stored procedure by eliminating the select statement:
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[USP_Get_Value_For_Key] Script Date: 02/24/2011 19:23:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[USP_Get_Value_For_Key]
(
@key NVARCHAR(50),
@value INT OUTPUT
)
AS
BEGIN
SET @value = 0;

UPDATE tbl_kvp
SET @value = column_value += 1
WHERE column_key = @key;
END

PS.
Don't shoot the messenger for bad English

Danny
Post #1069153
Posted Thursday, February 24, 2011 11:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
dvdwouwe 72544 (2/24/2011)
So, I have show that the lack of constraints can lead to too much locking and this can have major impact on the system throughput. The optimizer is blind without constraints and/or indices and some other structures!


Danny - That is exactly what I thought when I read the first parts of this forum post, or article. I wondered why this application was using a transactional RDMS database if it was not going to follow any of the rules for a transactional RDMS database.

IMHO: Your english and solution are great. You should write a real article detailing it.
I would read it and give it a good review if it said nothing more than what you posted here. Sharing these kind of brief and self eveident solutions that use RDMS features are what we need more of on Sql Server Central.
Post #1069162
Posted Thursday, February 24, 2011 11:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
Danny,

Thanks for the thoughtful and well-documented post. Your English is fine and please be assured that most people visiting this site are aware that it has an international following and that English is not the primary language for many of those posting here.

One thing about using a primary key constraint that makes it less attractive is that it does still lock the row for that key. Although in the original article, a variety of keys are supported in the table, the main difficulty arises when multiple users are running the same code. They would still experience blocking on their chosen key row. A user obtaining a value for key "PR1" in your code would not have a conflict with one obtaining a value for "PR2", but would still block a second user also looking for a "PR1" value.
Post #1069165
Posted Thursday, February 24, 2011 11:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
john.arnott (2/24/2011)
A user obtaining a value for key "PR1" in your code would not have a conflict with one obtaining a value for "PR2", but would still block a second user also looking for a "PR1" value.


John I agree that his solution, like others in the article and posted here seem to be missing Lock Hints in all the SQL statements.

When dealing with a shared transactual database with many differant user connections executing the same code that accesses the same data there is a hint that needs to be in every select and/or update statement.
WITH (NOLOCK) or WITH(ROWLOCK) or another locking hit that matches the requirements better.

This is my production 2 million transaction a day database. The connections stay at about 100-400 at all times and every connection creates a session object that is ID numbered using something similar to what Danny submited. I have never captured any locking or blocking in this DB.

I also have another DB app writen by a third party that was originaly writen for Oracle DB and refactored to work on MS SQL. It locks all the time becuase of the lack of locking hints.
Post #1069181
Posted Thursday, February 24, 2011 12:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 1, 2011 1:43 PM
Points: 1, Visits: 8
Back in the early nineties I worked on an early SQL database implementation that had no IDENTITY functionality, but we of course still needed unique identifiers to satisfy business logic requirements. I was new at SQL and in my naivete discovered a very simple technique to provide the equivalent of an IDENTITY without any blocking. (It does require putting a temporary lock around a transaction consisting of two calls, though).

Essentially the code is an implementation of a recognition that it doesn't matter which order you select and update (or update and select), so long as all participants use the same protocol. And it does, as one person pointed out, allow for voids in the sequence. (We thought of it as the reel of paper number tags you grab a number from when you are waiting for service at the DMV. When you get fed up with the wait, you can drop the ticket on the floor and walk away. They'll just serve the guy who walked in behind you.)

Anyway, we found that the following reversal of the order of the two DML calls worked perfectly fine for many years. I think that it still works.

CREATE PROCEDURE [dbo].[USP_Get_Value_For_Key]
(
@key NVARCHAR(50),
@value INT OUTPUT
)
AS
BEGIN
'apply lock here
UPDATE tbl_kvp SET column_value += 1
SELECT @value = column_value FROM tbl_kvp
'unlock here
END

Would this approach have solved the problem in the first place?

Doug
Post #1069249
Posted Thursday, February 24, 2011 12:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:44 AM
Points: 14, Visits: 182
Easy way to get blocking issues is setup blocking threshold and analyze trace files, also you can use analyze two or more snapshots of sys.dm_db_index_operational_stats to find most locked indexes\tables.
Post #1069252
Posted Thursday, February 24, 2011 1:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 15, 2014 6:29 AM
Points: 4, Visits: 31
Wow, what a response!

I read some strange things in the previous reply.

When dealing with a shared transactual database with many differant user connections executing the same code that accesses the same data there is a hint that needs to be in every select and/or update statement.
WITH (NOLOCK) or WITH(ROWLOCK) or another locking hit that matches the requirements better.


I have developed many DB's where speed is important, and I use almost never hints, because my experience (20+ years) told me that start using those hints always trigger some other problems and to solve these, oh yes, hints again.

There are indeed special cases when you want to elevate the lock.

In this case, you can use hints on the UPDATE statement but NOT NOLOCK or READUNCOMMITTED, that isn't allowed, this means that updating a row always use a writer lock, and the lifetime of a writer lock ends with the end of the enlisted transaction.

If you want that readers aren't blocked by the writers use the read-committed snapshot mode introduced in the yukon engine. Then you still can read all the values without blocking (for the ones that are updating and not committed, you will see the latest committed version)

PS
And using NOLOCK can lead to rare conditions:
* If you have a page-split when reading in this mode, then you can read the same record twice (what will happen then with your bussiness logic?)
* I don't know anymore the precise error, but an error can be thrown in this mode
* And what about ACID, you can read uncommitted records, what will happen if you read records that are rollbacked?
These are described in the great books of Kalen Delaney

My onion is that nolock, readuncomitted should be deprecated, there are a lot of other techniques that are ACID compliant.

Danny
Post #1069258
Posted Thursday, February 24, 2011 1:33 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
dvdwouwe 72544 (2/24/2011)

In this case, you can use hints on the UPDATE statement but NOT NOLOCK or READUNCOMMITTED, that isn't allowed, this means that updating a row always use a writer lock, and the lifetime of a writer lock ends with the end of the enlisted transaction.

Ummm... Of course. I did not realize that my comments suggested such an ignorant thing as UPDATE with(NOLOCK). I was just pointing out that NO locking Hints of any type whatsoever was mentioned and that is a solution to these sorts of problems that does work. You are correct in stating that once you start using this method, it must be used everywhere, but that's the key to using locking hints to resolve these issues.
Can i ask what database you used that had locking hints in 1991?
I thought this was not added until SQL92 in 1992, but 20 years is a long time.


If you want that readers aren't blocked by the writers use the read-committed snapshot mode introduced in the yukon engine. Then you still can read all the values without blocking (for the ones that are updating and not committed, you will see the latest committed version)

Sounds like you have another good topic for an article, I hope your write it. In my experiance this feature is not available in all SQL environments so we have never used it in production code.

It seems you feel that I was reducing the value of your opinion by posting mine.
Good luck with that.
Post #1069277
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse