InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 13:50:02 GMT20http://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxNBSteve: Wow. That was so kind of you to take the time to write that out. Not wordy in the slightest. Instead, it was *clear.* I greatly appreciated it!(Also thank you to the original author. I enjoy articles that explains a person's process/thinking. And also thank you to Paul for posting your interesting solution.)Tue, 09 Apr 2013 17:05:54 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxOK, that's good information regarding the bug affecting @@IDENTITY and SCOPE_IDENTITY(). According to the article you provided, the bug is fixed in 2008 R2 SP 1 and above. Thanks for pointing this out.Tue, 26 Mar 2013 16:19:13 GMTalishahryarhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]alishahryar (3/26/2013)[/b][hr]I came to add exactly the same point - SCOPE_IDENTITY() would guarantee that the just-inserted rows' newly-generated IDENTITY value would be unique.[/quote]As mentioned earlier, the scope_identity function has been known to generate incorrect values at times (when queries make use of parallel execution plans). Refer to this link for more information --> http://support.microsoft.com/kb/2019779Tue, 26 Mar 2013 16:11:43 GMTAjit Ananthramhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxI came to add exactly the same point - SCOPE_IDENTITY() would guarantee that the just-inserted rows' newly-generated IDENTITY value would be unique.Tue, 26 Mar 2013 14:00:05 GMTalishahryarhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote]Ajit, I'm more curious about how you scheduled your diagnostic procedure to run and find the blocks. I've copied and run it, but it found nothing, because I don't think anything was actually going on at the time. Did you just run it once, or did you have it repeat periodically, knowing the approximate time the offending procedure would run?[/quote]Thanks for your question. I wrote this article 2 years ago (it was republished yesterday), and back then I used to use the diagnostic script in a job which executed the script in a loop with a pause of a few seconds (i.e. with a WAITFOR DELAY statement) and had some logic to log to a table. The script only captures details of locks that exist when it executes (i.e. at that instant).If you are looking for a more customisable and optimised version of such a diagnostic script, I'd highly recommend MVP Adam Machanic's sp_whoisactive stored procedure. The latest version of this procedure is v11.11 at the time of writing this comment, and this can be obtained from the following location --> (http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx)Fri, 22 Mar 2013 16:45:56 GMTAjit Ananthramhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]NBSteve (3/22/2013)[/b][hr][quote][b]Paul White (2/26/2011)[/b][hr]It is possible to avoid the dummy column, ever-growing number of rows in the hidden tables, and avoid locking issues by using an approach based on this example code: (the example just uses a single range, but it is easy to extend the idea)[code="sql"]--really cool code from page 5 snipped[/code][/quote]Wow, I stared at this code for a good 30 minutes, just admiring the beauty and cleverness and (unsuccessfully) trying to find a flaw with it. I'm sad that I'm discovering this 2 years late, but this is a really cool (and yes, sneaky) trick. Paul, have you ever made a separate blog post detailing this technique? (Or know of a similar one by someone else?)[/quote]Thanks, and no I have never blogged about it - it seemed a bit of a narrow use case to be honest. Your explanation is spot on by the way.Fri, 22 Mar 2013 15:14:59 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxThis solution would appear to be a sound approach to resolve the locking issue. However, it seems that perhaps a simpler approach would be to move the call to the function to get the key out of the transaction. Call the function to get the key; if it is null then error out; if it is non-null proceed with transaction processing.Fri, 22 Mar 2013 14:58:07 GMTkpmcginnhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxWow! you folks have been busy! I'm just reading this at 2:pm EDT and by time I got to the end, I forgot what the original purpose was. I had to go back and re-read some sections to catch up. We have a mail-order business which also employs a call center and Internet sales. We have from 10-25 people (depending on season) taking orders by phone or from mail and the web runs 24-7. Each entry person uses the same custom OrderEntry program (VB6) which is the front end, SQL '08 is the DB. Each order gets a unique order number. As you can see by the sample of the OrderNumber table below (sorry for alignment), the TYPE of order can determine the ordernumber. Still, if there were only 1 type, it wouldn't matter. Simply put, the calling procedure adds a 1 to the current ordernumber. The next person gets the current ordernumber and adds a 1, etc. etc. the web system pulls from the same table. NOte the column "next availableOrderNumber". In my 2.5 years here, we've never had a collision or duplication and this system has been running since the late 90's. The calling program identifies the type based on location and feeds that as a parameter to the calling sproc. It's just a glorified counter. Sorry if I'm missing the point.CatalogYear OrderType Range Description NextAvailableOrderNumber OrderNumberRangeLow OrderNumberRangeHigh2012 C 1 Credit Card Orders 0 400000 4999992012 C 2 Credit Card Orders w/Cards 0 570000 5799992012 C 3 Credit Card Orders (FAX) 0 890000 8959992012 C 4 Credit Card Orders (EMail) 0 580000 5899992012 C 5 Credit Card Orders (999999) 0 870000 8739992012 C 6 Credit Card Orders (WEB Site) 0 580000 5999992012 E 1 Employee Charge 0 899000 8999992012 R 1 Regular (Check) Orders 0 600000 7999992012 R 2 Regular (Check) Orders 0 855000 8579992012 R 3 Regular(Check) Orders (999999) 0 855000 8579992012 T 1 Telephone Orders 100219 100000 1299992012 W 1 WalkIn Charge 0 897500 8979992012 X 1 WalkIn Check 0 897000 8974992012 Z 1 Employee Check 0 898000 898999Ajit, I'm more curious about how you scheduled your diagnostic procedure to run and find the blocks. I've copied and run it, but it found nothing, because I don't think anything was actually going on at the time. Did you just run it once, or did you have it repeat periodically, knowing the approximate time the offending procedure would run?Fri, 22 Mar 2013 12:56:20 GMTCaruncleshttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]Paul White (2/26/2011)[/b][hr]It is possible to avoid the dummy column, ever-growing number of rows in the hidden tables, and avoid locking issues by using an approach based on this example code: (the example just uses a single range, but it is easy to extend the idea)[code="sql"]--really cool code from page 5 snipped[/code][/quote]Wow, I stared at this code for a good 30 minutes, just admiring the beauty and cleverness and (unsuccessfully) trying to find a flaw with it. I'm sad that I'm discovering this 2 years late, but this is a really cool (and yes, sneaky) trick. Paul, have you ever made a separate blog post detailing this technique? (Or know of a similar one by someone else?) I don't have anything new to add, but want to rehash some of the things going on just to clarify for anybody that's confused or missing the point. The OP's original problem was that he's using a stored procedure to get a key value from another table and seeing blocking because related calls to that stored procedure are trying to access a value from the same row. Note that even if the stored procedure itself doesn't leave an open transaction locking the rows, it's possible that the calling routine, which the DBA may or may not have control over, is calling the key procedure inside of a long running transaction, leaving long-standing locks in place despite the speed or intentions of the actual sproc. The OP's solution to this is to ensure that, rather than having parallel processes calling the sproc accessing the same row (which leads to the potential blocking), each call will create a new row in the table so that as long as only row locks are used, multiple calls will never block one another. The primary drawbacks of this are the potential need for many tables and the creation of many rows of data which are never used after their initial creation. Paul's code doesn't directly address the problem of multiple different applications, although one could easily do the same as the OP and create a new table for each application and still use Paul's trick. One of the cool things about Paul's technique though is that it doesn't result in many rows... in fact, it doesn't result in ANY rows! He's essentially using the table's meta-data to track the keys rather than using the table data itself. And, he's doing it without ANY locking issues at all. (Yes, there's probably an instantaneous lock, but it won't get held even in a long-running transaction.)So what's going on in Paul's code that makes it so sneaky? There's a few things, so I'll start from the middle and work out. The heart of it is the INSERT, which uses an OUTPUT clause to save the new IDENTITY value automatically generated by the table (note that, as others have posted, this OUTPUT technique is safer than using SCOPE_IDENTITY() or related functions). The OUTPUT saves the value in a [b]table variable[/b] which is critical because table variables do not participate in transactions. This becomes important because the INSERT is nested inside a SAVE/ROLLBACK transaction. I'm guessing everybody who reads this is fully aware of BEGIN/COMMIT/ROLLBACK transactions, although I do find that many people don't realize how nested transactions work. Transactions can be named to help clarify nesting levels, however, these names have NO effect on COMMIT/ROLLBACK calls. COMMIT will always commit the [b]innermost[/b] transaction, while ROLLBACK will always rollback the [b]outermost [/b]transaction, including all nested transactions. The exception to this is when creating a SAVE point, as Paul does. A ROLLBACK can not be limited to a nested BEGIN TRANSACTION, but it can be limited to a SAVE TRANSACTION point. So what's happening here is Paul creates a SAVE point right before the insert, then inserts the data, and then immediately rolls back to the save point. The ROLLBACK undoes the INSERT, which includes releasing any locks that were allocated from the INSERT. However, because table variables are not included in transactions, the IDENTITY value which was OUTPUT into the table variable is still present and available for use. Also, the IDENTITY SEED value from the table's meta data isn't included in the transaction and so stays incremented even through the rollback, so that future calls will continue incrementing it rather than repeating values. So the net result here is that, because of the save point and rollback, no rows are written to any table and no locks are being allocated. However, we still get back the new identity value, and the table meta data still has a record of what that newest identity value was so that it doesn't repeat. Sneaky indeed. It's also worth paying attention to how Paul handles the TRY...CATCH block and his COMMIT transactions. Though not directly related to the intent of the post, they're often misunderstood and this is a great example of how to use them properly.Sorry for the wordiness, but hope this helps clarify things.Fri, 22 Mar 2013 10:57:30 GMTNBStevehttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]nycgags (3/22/2013)[/b][hr][quote][b]peter.row (2/24/2011)[/b][hr]Why use the output syntax, table variable and dynamic sql?Wouldn't the following be simpler (no permissions issue on table if user running it only has access to SP either) :insert into ...set @outputId = scope_identity()[/quote]This was my original thought as well, maybe I am missing something.[/quote]The scope_identity function has been known to generate incorrect values at times (when queries make use of parallel execution plans). Refer to this link for more information --> [url=http://support.microsoft.com/kb/2019779][/url]Fri, 22 Mar 2013 06:47:01 GMTAjit Ananthramhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]peter.row (2/24/2011)[/b][hr]Why use the output syntax, table variable and dynamic sql?Wouldn't the following be simpler (no permissions issue on table if user running it only has access to SP either) :insert into ...set @outputId = scope_identity()[/quote]This was my original thought as well, maybe I am missing something.Fri, 22 Mar 2013 05:26:18 GMTnycgagshttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxYou are correct, and I agree. Identity columns should not be used for business data in that way. My point was that the transaction responsible for assigning the "gapless" sequence in the original example SP would achive this, but the function as re-written would not. I would normally expect to assign such numbers in a batch process and allocate them all or not at the end of the day. In some cases, we see internet buisinesses where the (for example) invoice number has to be applied immidiatly to allow a web based order processing system, coulpled with the business requiremet for no gaps in the sequence of tax invoice numbers. In this specific case, the original stored proceedure works, but the re-write may not.Mon, 14 Mar 2011 06:14:17 GMTstephen.learhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]Grigore Dolghin (2/25/2011)[/b][hr]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.[/quote]Please do not label solutions as worst if you do not understand them. What I suggested is that you still keep the locking on the database side, but do not request one key at a time. Any application can request 100 keys at a time and cache them on the application side. I do not see the connection between what I suggested and using the keys in multiple tables. If you must have all the entries in sequential order and can not tolerate missing keys than what I suggest is not usable. By the way in Oracle sequences can have incremental different than 1 same as identity columns in SQL Server.Tue, 01 Mar 2011 10:27:16 GMTVladimir Sotirovhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote]That is what they started with but they had locking and blocking issues while updating that one nextnum table to obtain the next key value.[/quote]I think I was unclear.I am suggesting that instead of one dummy table for each key-value, you create a single dummy table and grab the next identity value from it just as in your solution. The only difference is that the dummy table is shared amongst all of the key value pairs. This may, of course, leave gaps in the sequence for each key-value but you have stated that that doesn't matter in this case.--JimFiveTue, 01 Mar 2011 08:36:50 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]James Goodwin (2/28/2011)[/b][hr]That's a fairly nice solution to the problem, however...(There's always a but...)The biggest problem I see with your approach is that when someone wants to add a new key-value pair they also need to add a new table. I dislike needing to alter the design of the database just because someone wants to add a data value.Therefore: Wouldn't it be easier to use a single nextnum table instead of one for each key? Your get sequence number code would work the same and you wouldn't need to use dynamic SQL.[/quote]That is what they started with but they had locking and blocking issues while updating that one nextnum table to obtain the next key value.I suspect that they would only add a new key when they add a new table, so instead of just adding one table you add two. Is the solution perfect? No, but it fixed the problem they were encountering without having to do a major redesign of the database or the application.Mon, 28 Feb 2011 14:57:26 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxThat's a fairly nice solution to the problem, however...(There's always a but...)The biggest problem I see with your approach is that when someone wants to add a new key-value pair they also need to add a new table. I dislike needing to alter the design of the database just because someone wants to add a data value.Therefore: Wouldn't it be easier to use a single nextnum table instead of one for each key? Your get sequence number code would work the same and you wouldn't need to use dynamic SQL.--JimFiveMon, 28 Feb 2011 14:48:32 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxIt is possible to avoid the dummy column, ever-growing number of rows in the hidden tables, and avoid locking issues by using an approach based on this example code: (the example just uses a single range, but it is easy to extend the idea)[code="sql"]USE tempdb;GO-- Drop the table if it exists from a previous runIF OBJECT_ID(N'dbo.SneakyIdentity', N'U') IS NOT NULLBEGIN DROP TABLE dbo.SneakyIdentity;END;GO-- Create the hidden table used to allocate IDsCREATE TABLE dbo.SneakyIdentity (row_id BIGINT IDENTITY NOT NULL);GO[/code][code="sql"]-- The allocation routineCREATE PROCEDURE dbo.Allocate ( @ID BIGINT OUTPUT )ASBEGIN -- No DONE_IN_PROC messages SET NOCOUNT ON ; -- Almost all errors will abort the batch SET XACT_ABORT ON ; -- Table variable used with the OUTPUT clause -- to safely read the identity value assigned. -- Issues (bugs) have arisen in the past with -- @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT. DECLARE @Output TABLE (row_id BIGINT NOT NULL) ; -- Start error handler BEGIN TRY -- Might not be the top-level transaction! BEGIN TRANSACTION; -- Save point SAVE TRANSACTION AllocateID; -- Allocate an ID using the 'hidden' table INSERT dbo.SneakyIdentity OUTPUT inserted.row_id INTO @Output (row_id) DEFAULT VALUES; -- Rollback to the save point ROLLBACK TRANSACTION AllocateID; -- Correct the transaction count COMMIT TRANSACTION; END TRY BEGIN CATCH -- Remember the error message DECLARE @ErrMsg NVARCHAR(2048); SET @ErrMsg = ERROR_MESSAGE(); -- Uncommittable transaction => unconditional rollback -- (this is the only option) IF XACT_STATE() = -1 ROLLBACK TRANSACTION; -- If our transaction is still alive, roll back to -- the save point, and adjust @@TRANCOUNT IF XACT_STATE() = 1 BEGIN -- Rollback to the save point ROLLBACK TRANSACTION AllocateID; -- Correct the transaction count COMMIT TRANSACTION; END -- Custom error handling goes here RAISERROR('Error in allocation: %s', 16, 1, @ErrMsg); -- Error RETURN 999; END CATCH; -- Capture assigned value -- (Table variables are not affected by transaction rollbacks) SET @ID = ( SELECT TOP (1) row_id FROM @Output ); -- Success RETURN 0 ;END;GO[/code][code="sql"]-- === TEST ===-- VariablesDECLARE @ID BIGINT, @RC INTEGER;;-- Simulate an enclosing user transactionBEGIN TRANSACTION;-- Allocate the next idEXECUTE @RC = dbo.Allocate @ID OUTPUT;-- Show locksSELECT *FROM sys.dm_tran_locks AS TLWHERE TL.request_session_id = @@SPID;-- Show procedure return code and allocated IDSELECT return_code = @RC, allocated_id = @ID;-- Now roll back or commitROLLBACK;-- Uncomment this when finished-- DROP TABLE dbo.SneakyIdentity;-- DROP PROCEDURE dbo.Allocate;[/code]Sat, 26 Feb 2011 21:40:21 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]Solomon Rutzky (2/26/2011)[/b][hr]...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:[code="sql"]UPDATE tbl_kvpSET column_value += 1OUTPUT inserted.column_valueWHERE column_key = @key[/code]This avoids the need for additional locking as "tech.dbmeyer" had suggested.[/quote]Yes, but the problem is that the key-allocation procedure might be called within a transaction. The exclusive row lock needed by the statement above will be held to the end of that transaction. An application that starts a transaction, allocates a key from this procedure, and then sits around waiting for something, will block anyone else needing the next key from the same sequence.I wrote about this issue at length (including robust ways to pre-allocate a range of keys without blocking) in:[url]http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx[/url]Sat, 26 Feb 2011 20:54:32 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]Solomon Rutzky (2/26/2011)[/b][hr]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:[code="sql"]UPDATE tbl_kvpSET column_value += 1OUTPUT inserted.column_valueWHERE column_key = @key[/code]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_". ;-)[/quote]Thanks for your feedback Solomon.Even with the primary key in place, if two user sessions would attempt to increment the same key as part of their own transactions, one session would end up blocking the other.Ideally, incrementing the KVP value should be done outside transactions, but applications developers seldom adhered to this principle. As I've said in previous posts, the main goals for me were to eliminate this blocking, and also make the incrementing process independent of transactions.By making the single horizontal record for a given key vertical, transactions could increment the KVP and obtain appropriate values without blocking each other.Sat, 26 Feb 2011 20:42:41 GMTAjit Ananthramhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxIronically 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"Sat, 26 Feb 2011 19:32:20 GMTirozenberghttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxAjit, 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:[code="sql"]UPDATE tbl_kvpSET column_value += 1OUTPUT inserted.column_valueWHERE column_key = @key[/code]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_". ;-)Sat, 26 Feb 2011 07:31:05 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxI 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.Fri, 25 Feb 2011 10:44:22 GMTGrigore Dolghinhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxI 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.Fri, 25 Feb 2011 10:33:25 GMTVladimir Sotirovhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxHi,[quote]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.[/quote]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.[li]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[/li][li]DB2 / SQL-server are pessimistic DB-servers (a lot of locks)[/li]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.DannyThu, 24 Feb 2011 14:12:06 GMTdvdwouwe 72544http://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]dvdwouwe 72544 (2/24/2011)[/b][hr]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.[/quote]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.[quote]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) [/quote]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.Thu, 24 Feb 2011 13:33:42 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxWow, what a response!I read some strange things in the previous reply.[quote]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. [/quote]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)PSAnd 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 DelaneyMy onion is that nolock, readuncomitted should be deprecated, there are a lot of other techniques that are ACID compliant.DannyThu, 24 Feb 2011 13:00:23 GMTdvdwouwe 72544http://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxEasy 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.Thu, 24 Feb 2011 12:54:51 GMTobalyukhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxBack 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 hereENDWould this approach have solved the problem in the first place?DougThu, 24 Feb 2011 12:50:59 GMTtech.dbmeyerhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]john.arnott (2/24/2011)[/b][hr]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.[/quote]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.Thu, 24 Feb 2011 11:59:16 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxDanny,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.Thu, 24 Feb 2011 11:41:58 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]dvdwouwe 72544 (2/24/2011)[/b][hr]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![/quote]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.Thu, 24 Feb 2011 11:41:50 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxHi,There is a much easier solution for this problem:[code="sql"]alter table dbo.tbl_kvp add constraint PK_tbl_kvp primary key(column_key);go[/code]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 table2) run this script in a session[code="sql"]declare @rc int, @val int = 0;begin transactionexec @rc = dbo.USP_Get_Value_For_Key @key = 'PR[b]1[/b]', @value = @val OUTPUT;select @val--commit transaction[/code]3) run this script in another session[code="sql"]declare @rc int, @val int = 0;begin transactionexec @rc = dbo.USP_Get_Value_For_Key @key = 'PR[b]2[/b]', @value = @val OUTPUT;select @val--commit transaction[/code]==> This will result in a block4) Create the clustered keydo the test again and NO blocking will occur!Notice the plans[code="sql"] UPDATE tbl_kvp SET column_value += 1 WHERE column_key = @key[/code] |--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) |--[b]Table Scan[/b](OBJECT:([Test].[dbo].[tbl_kvp]), WHERE:([Test].[dbo].[tbl_kvp].[column_key]=[@key]) ORDERED)with the clustered key |--[b]Clustered Index Update[/b](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[b]Conclusion[/b]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:[code="sql"]USE [Test]GO/****** Object: StoredProcedure [dbo].[USP_Get_Value_For_Key] Script Date: 02/24/2011 19:23:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[USP_Get_Value_For_Key]( @key NVARCHAR(50), @value INT OUTPUT)ASBEGIN SET @value = 0; UPDATE tbl_kvp SET @value = column_value += 1 WHERE column_key = @key;END[/code]PS.Don't shoot the messenger for bad EnglishDannyThu, 24 Feb 2011 11:30:33 GMTdvdwouwe 72544http://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspxInteresting solution to the problem, thanks for sharing!Thu, 24 Feb 2011 10:27:32 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]john.arnott (2/24/2011)[/b][hr]Or did I completely misunderstand the test framework?[/quote]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.Thu, 24 Feb 2011 09:44:46 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]SanDroid (2/24/2011)[/b][hr][quote][b]adam.everett (2/24/2011)[/b][hr]Can I ask why in the original code the lock on the kvp was required for the duration of the whole buniness transaction. Could they just not of got the required key values into some temps using some short transactions before the main long running business process transaction took place? [/quote]I agree. I have not seen any code like this since a early 90's Paradox database application. Even that system was fast enought not to need a 10 second pause in the middle of a transaction before releasing Key Value table.Did you discover that the wait for ten seconds statement was also causing blocking and locking?Of course all of the Key generation tables and supporting code I used where refactered in the late 90's when Indentity and Scope Identity was made commonplace in RDBMS. I think that was SQL 92.... [/quote]Re: the ten-second wait....That looks to me not to be a slap-dash fix ("better take a nap while the other thread finishes"), but rather an exagerated simulation of extensive business logic processing during which the KVP table would be locked and therefore block other processes trying to use it. Or did I completely misunderstand the test framework?Thu, 24 Feb 2011 09:38:46 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]gdolghin (2/24/2011)[/b][hr]The "no-gaps-number" column is a business requirement and has nothing to do with the database structure. That one is usually managed through a trigger that selects next available value. It is usually the customer number, the invoice reference id, and so on. In my opinion is a mistake to use the customer number as primary key.[/quote]Thank you for clarifying what this was really all about. I am sure I was not the only DBA/DB Developer totaly confused about the need for this type of table.The length of the waitfor statement is still a total confusion and actually reminds me of an excercise out of my 1992 beginning C++ text book. The excercise was about creating a database of customer information for electronic invoicing. :cool:Thu, 24 Feb 2011 08:24:19 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]Mike Dougherty-384281 (2/24/2011)[/b][hr][quote][b]arty 15255 (2/24/2011)[/b][hr]I like newbies :)You can always correct their sql statments :)...UPDATE tbl_kvp SET @value = column_value+1, column_value = @valueWHERE column_key = @key...(that avoids need of "begin transaction")[/quote]Is this guaranteed to be available in future versions of SQL server or is it an undocumented feature like:<code="sql">declare @myvar intselect @myvar = @myvar + intfield from table where [condition(s)]</code>Either way, it's a pretty slick trick :)[/quote]BOL:...[ WITH <common_table_expression> [...n] ]UPDATE [ TOP ( expression ) [ PERCENT ] ] { <object> | rowset_function_limited [ WITH ( <Table_Hint_Limited> [ ...n ] ) ] } SET { column_name = { expression | DEFAULT | NULL } | { udt_column_name.{ { property_name = expression | field_name = expression } | method_name ( argument [ ,...n ] ) } } | column_name { .WRITE ( expression , @Offset , @Length ) } | [u][b]@variable = expression [/b][/u] | [u][b]@variable = column = expression [ ,...n ] [/b][/u] } [ ,...n ] ...Thu, 24 Feb 2011 08:23:37 GMTarty 15255http://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]arty 15255 (2/24/2011)[/b][hr]I like newbies :)You can always correct their sql statments :)...UPDATE tbl_kvp SET @value = column_value+1, column_value = @valueWHERE column_key = @key...(that avoids need of "begin transaction")[/quote]Is this guaranteed to be available in future versions of SQL server or is it an undocumented feature like:<code="sql">declare @myvar intselect @myvar = @myvar + intfield from table where [condition(s)]</code>Either way, it's a pretty slick trick :)Thu, 24 Feb 2011 08:20:17 GMTMike Dougherty-384281http://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]adam.everett (2/24/2011)[/b][hr]Can I ask why in the original code the lock on the kvp was required for the duration of the whole buniness transaction. Could they just not of got the required key values into some temps using some short transactions before the main long running business process transaction took place? [/quote]I agree. I have not seen any code like this since a early 90's Paradox database application. Even that system was fast enought not to need a 10 second pause in the middle of a transaction before releasing Key Value table.Did you discover that the wait for ten seconds statement was also causing blocking and locking?Of course all of the Key generation tables and supporting code I used where refactered in the late 90's when Indentity and Scope Identity was made commonplace in RDBMS. I think that was SQL 92.... Thu, 24 Feb 2011 08:12:18 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1068652-2909-1.aspx[quote][b]stephen.lear (2/24/2011)[/b][hr]Does your solution really solve the issue?What if the requirement was for no gaps in the key sequence? Doesn't using identities in this way lead to gaps in the key sequence if a transaction is rolled back.There is often a requirement to keep a continuous sequence, such as when assigning invoice numbers, in which case the original USP was correct, but your replacement may lead to gaps in sequence.[/quote]Stephen, I am afraid you are confusing two different notions. Identity field is used to uniquely identify a record, disregarding any business-related value.The "no-gaps-number" column is a business requirement and has nothing to do with the database structure. That one is usually managed through a trigger that selects next available value. It is usually the customer number, the invoice reference id, and so on. In my opinion is a mistake to use the customer number as primary key.Thu, 24 Feb 2011 07:52:01 GMTGrigore Dolghin