Use of Identity Property to Resolve Concurrency Issues

Question

Use of Identity Property to Resolve Concurrency Issues

PostgreSQL/ Oracle / Interbase / firebird are optimistic (readers don't block writers and writers don't block readers), but they have special anomalies like read/write skew
DB2 / SQL-server are pessimistic DB-servers (a lot of locks)

So, locks (or lock hints) aren't always the solution to do the work, only in some very special cases. And DB-systems are getting smarter and smarter if you feed them with correct information (constrainst etc..) to reduce the manual interactions of giving hints.

I did some sessions in Belgium about the key difference between those systems to database developers.

Danny

SanDroid SSChampion Points: 10068 More actions · Answer 1

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.

UMG Developer SSChampion Points: 13482 More actions · Answer 2

Interesting solution to the problem, thanks for sharing!

dvdwouwe 72544 SSC Rookie Points: 38 More actions · Answer 3

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

SanDroid SSChampion Points: 10068 More actions · Answer 4

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.

john.arnott SSChampion Points: 11882 More actions · Answer 5

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.

SanDroid SSChampion Points: 10068 More actions · Answer 6

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.

tech.dbmeyer Newbie Points: 9 More actions · Answer 7

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

obalyuk Grasshopper Points: 22 More actions · Answer 8

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.

dvdwouwe 72544 SSC Rookie Points: 38 More actions · Answer 9

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

SanDroid SSChampion Points: 10068 More actions · Answer 10

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.

dvdwouwe 72544 SSC Rookie Points: 38 More actions · Answer 11

Hi,

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.

I use SQL server from version 2005, but I use DB-systems from the 90's, nl. Oracle and Interbase.

And Interbase did have some unbelievable powerful transaction management, and Inprise screwed this powerfull system.

And from 2001 I use also open-source Firebird DB (what is a fork of Interbase) and PostgreSQL. I studied the source files of firebird for 8 years and learned a lot how an DB-engine works inside.

And I believe that Interbase/Firebird introduced the system what is introduced in the Yukon engine as read-committed snapshot for over 20 years ago.

Using different database systems give me other ideas how to solve solutions.

And there is a huge difference between optimistic en pessimistic DB-systems.

Vladimir Sotirov SSC Eights! Points: 921 More actions · Answer 12

I think your solution is really good. One more time DBA has to solve issues caused by developers that do not know that database calls are expensive and locking is not so difficult to be implemented on application side. It would be better if the developer creates a call to the database and gets 100 keys at one time that he stores in memory in his application and uses the keys from there. Now instead of 100 calls to the database for each record he makes only one for each 100 records.

Grigore Dolghin SSC Rookie Points: 48 More actions · Answer 13

I am afraid this approach is even worse. What if for each of those keys the developer has to insert some records in a related table, then use those keys to insert related records in a third table? and what if those keys are actually autoincrement values and can't be obtained beforehand? and what if the values have to be generated taking in account the other possible users working in the same time? (such as secvential invoice numbers?)

Locking must be implemented on database side, period.

Microsoft MVP 2006-2010

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 14

Ajit, this is an interesting approach to the problem, but most likely it is just adding complication via over-engineering when a much simpler solution appears to be available.

1) The most important and easiest thing to do is, as "dvdwouwe 72544" mentioned, create a PK on the "key" column. There is really no reason to have this table without a PK defined. This will also ensure that a duplicate "key" doesn't accidentally get added to the table.

2) You don't need to worry about the SELECT of the new value getting something updated from another session if you use the OUTPUT clause in the UPDATE as follows:

UPDATE tbl_kvp

SET column_value += 1

OUTPUT inserted.column_value

WHERE column_key = @key

This avoids the need for additional locking as "tech.dbmeyer" had suggested.

3) You can always tell the system to only lock a single row (although given the singular nature of the UPDATE statement I am not sure why it would ever do anything else, but still) by using the WITH (ROWLOCK) hint in the UPDATE as "SanDroid" mentioned.

Minor note: I find it interesting / amusing that whoever created this table (a developer I think you said) prefixed the column names with "column_".

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

irozenberg SSC-Addicted Points: 478 More actions · Answer 15

Ironically a new version of SQL Server would have an Oracle-style SEQUENCE identity capability, so hopefully there will not be need to "re-invent the wheel"

Use of Identity Property to Resolve Concurrency Issues

Cookies on SQLServerCentral