July 6, 2009 at 9:37 pm
Ooooookay.... and I thought I had a good idea here.:sick:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 6, 2009 at 9:45 pm
BWAA-HAA!!! Well, at least now you know why I'm so opposed to any form of "ID Reservation" or "Sequence" table. While it's possible to make such a thing work, it's a real PITA all the way around.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2009 at 10:07 pm
You'll like this Jeff 🙂
Code windows mangle it a bit so it is attached too - recommend anyone interested opens that instead.
Paul
-- Creates and drops objects in tempdb only
-- Requires dbo permissions
-- You can run it all as one script, but it is better run one batch at a time
USE tempdb;
GO
--
-- This table only ever holds one row, containing the next available range start value
--
CREATE TABLE dbo.AllocationMaster
(
row_idINTEGER IDENTITY PRIMARY KEY
CHECK (row_id = 1),
next_available_valueINTEGER NOT NULL
CHECK (next_available_value BETWEEN 800000000 AND 900000000)
DEFAULT (800000000),
);
GO
--
-- The table of allocated ranges
--
CREATE TABLE dbo.Allocation
(
range_idINTEGER IDENTITY PRIMARY KEY,
range_nameNVARCHAR(30) NOT NULL UNIQUE,
range_startINTEGER NOT NULL
CHECK (range_start BETWEEN 800000000 AND 900000000),
range_endINTEGER NOT NULL
CHECK (range_end BETWEEN 800000000 AND 900000000),
CHECK (range_end >= range_start)
);
GO
--
-- Create a user and schema so we can break ownership chaining on the procedure
--
CREATE USER Demo WITHOUT LOGIN;
GO
CREATE SCHEMA Demo AUTHORIZATION Demo;
GO
--
-- The procedure which does the work
-- Created in the Demo schema so that permissions don't 'just work'
--
CREATE PROCEDURE Demo.usp_Allocate
@range_nameNVARCHAR(30),
@range_sizeINTEGER
WITH EXECUTE AS CALLER
AS
BEGIN
SETXACT_ABORT ON;
SETNOCOUNT ON;
DECLARE@Allocation
TABLE(
range_nameNVARCHAR(30) NOT NULL,
range_startINTEGER NOT NULL,
range_endINTEGER NOT NULL
);
BEGIN TRY
BEGIN TRANSACTION;
-- Atomic update and range allocate
-- We use the table variable rather than OUTPUT directly to dbo.Allocation
-- since that would prevent CHECK constraints, FOREIGN KEYs, or active TRIGGERs
UPDATEdbo.AllocationMaster
SETnext_available_value = next_available_value + @range_size
OUTPUT@range_name,
DELETED.next_available_value,
INSERTED.next_available_value - 1
INTO@Allocation (range_name, range_start, range_end)
WHERErow_id = 1;
-- Insert the range record from the table variable
INSERTdbo.Allocation
(range_name, range_start, range_end)
SELECTrange_name, range_start, range_end
FROM@Allocation;
-- Clean exit
COMMIT TRANSACTION;
RETURN(1 - @@ROWCOUNT);
END TRY
BEGIN CATCH
IFXACT_STATE() 0 ROLLBACK TRANSACTION;
-- Custom error handling here
SELECTErrorMsg = ERROR_MESSAGE();
RETURN999;
END CATCH;
END;
GO
--
-- Initialize the master table (trying to add another row after this produces an error)
--
INSERTdbo.AllocationMaster DEFAULT VALUES;
GO
--
-- Show table contents
--
SELECT * FROM dbo.AllocationMaster;
GO
--
-- Revoke permissions (not that we granted any, but hey, it's a demo)
--
REVOKE SELECT, INSERT, DELETE, UPDATE ON dbo.AllocationMaster FROM public;
REVOKE SELECT, INSERT, DELETE, UPDATE ON dbo.Allocation FROM public;
GO
--
-- Create a test login and user to test the procedure with
--
CREATE LOGIN testuser WITH PASSWORD = N'Test=User=12345';
CREATE USER testuser;
GO
--
-- Grant permission on the procedure to this test user
--
GRANT EXECUTE ON Demo.usp_Allocate TO testuser;
GO
--
-- Check the permissions
--
EXECUTE AS USER = N'testuser';
DECLARE@ReturnCode INTEGER;
--
-- Fails on permissions check (SELECT and UPDATE on dbo.AllocationMaster)
--
EXECUTE @ReturnCode = Demo.usp_Allocate N'Range A', 1000;
SELECTTestRun = @ReturnCode;
REVERT;
GO
--
-- Create a certificate to sign the procedure with
--
CREATE CERTIFICATE RangeAllocationCert
ENCRYPTION BY PASSWORD = '3%67&yU!"-9w4}Z+'
WITH SUBJECT = 'Range Allocation Permissions',
START_DATE = '2000-01-01', EXPIRY_DATE = '9999-12-31';
GO
--
-- Create a user from the certificate (we grant permissions to this 'user')
-- This user cannot log in
--
CREATE USER RangeAllocationUser FOR CERTIFICATE RangeAllocationCert;
GO
--
-- Grant specific object permissions to the certificate 'user'
--
GRANT SELECT ON dbo.AllocationMaster TO RangeAllocationUser;
GRANT UPDATE ON dbo.AllocationMaster TO RangeAllocationUser;
GRANT INSERT ON dbo.Allocation TO RangeAllocationUser;
GO
--
-- Sign the procedure with the certificate
-- Callers of the procedure get the additional permissions
-- *while running the procedure only*
--
ADD SIGNATURE TO Demo.usp_Allocate BY CERTIFICATE RangeAllocationCert WITH PASSWORD = '3%67&yU!"-9w4}Z+';
GO
--
-- Test the allocation procedure with the new permissions arrangements
--
EXECUTE AS USER = N'testuser';
GO
DECLARE@ReturnCode INTEGER;
EXECUTE @ReturnCode = Demo.usp_Allocate N'Range A', 1000-- Succeeds now
SELECTAddRangeA = @ReturnCode;
GO
DECLARE@ReturnCode INTEGER;
EXECUTE @ReturnCode = Demo.usp_Allocate N'Range B', 4000-- Succeeds now
SELECTAddRangeB = @ReturnCode;
GO
DECLARE@ReturnCode INTEGER;
EXECUTE @ReturnCode = Demo.usp_Allocate N'Range C', 9500-- Succeeds now
SELECTAddRangeC = @ReturnCode;
GO
DECLARE@ReturnCode INTEGER;
EXECUTE @ReturnCode = Demo.usp_Allocate N'Range A', 100-- Fails due to duplicate range name
SELECTAddRangeA = @ReturnCode;
GO
--
-- Finished being the test user
--
REVERT;
GO
--
-- Show the contents of the tables
--
SELECT row_id, next_available_value FROM dbo.AllocationMaster;
SELECT range_id, range_name, range_start, range_end FROM dbo.Allocation;
GO
--
-- Tidy up
-- (if you get permissions errors here, make sure you executed REVERT above)
--
DROP TABLE dbo.AllocationMaster;
DROP TABLE dbo.Allocation;
DROP PROCEDURE Demo.usp_Allocate;
DROP USER RangeAllocationUser;
DROP CERTIFICATE RangeAllocationCert;
DROP USER testuser;
DROP LOGIN testuser;
DROP SCHEMA Demo;
DROP USER Demo;
GO
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 7, 2009 at 6:06 am
Paul,
Very cool. The additional sequence table prevents the need for a SELECT or comparison in the code at all and that's important for both concurrency and the prevention of deadlocks. You did a great job especially with correctly short-circuiting the Insert should the update fail by using a combination of an explicit transaction and SET XACT_ABORT ON. The really good part about your code is the correct use of the UPDATE to both update the table and capture what the update was in a single statement.
Because of the correct form of that UPDATE, I believe I'd go the extra mile to get rid of the explicit transaction all together... that would be to, of course, drop the transaction code and drop the Try/Catch and put a simple IF @@ERROR block between the UPDATE and the INSERT for an early exit.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2009 at 8:39 am
Paul,
That was very impressive! They have already started allocating ranges manually. Here is what has happened thus far.
CompanyA 882150000882151000
CompanyB 890100000890199999
CompanyC883550000883559999
CompanyD 807120000807122999
CompanyE 835600100847725972
CompanyF 887000000889999999
CompanyG880206066880208589
CompanyH 880151926880153021
CompanyI 880181386880186862
Is there a way to incorporate these existing ranges into your approach?
July 7, 2009 at 9:03 am
Jeff,
To answer your question:
Do you mean to tell me that these alternate id's will NOT be used anywhere else in the database?
Yes they will be used elsewhere. They will also be stored in the employee table along with the individuals social security number.
Once again I really appreciate your efforts here.
July 7, 2009 at 9:08 am
Another peice of information that might be helpful. The reason we preassign a range of numbers is often times the company will send us a file of new employees to input into our system that contains the alternate id that should be assigned to the employee.
July 7, 2009 at 4:25 pm
Jeff Moden (7/7/2009)
Paul,Very cool. The additional sequence table prevents the need for a SELECT or comparison in the code at all and that's important for both concurrency and the prevention of deadlocks. You did a great job especially with correctly short-circuiting the Insert should the update fail by using a combination of an explicit transaction and SET XACT_ABORT ON. The really good part about your code is the correct use of the UPDATE to both update the table and capture what the update was in a single statement.
Because of the correct form of that UPDATE, I believe I'd go the extra mile to get rid of the explicit transaction all together... that would be to, of course, drop the transaction code and drop the Try/Catch and put a simple IF @@ERROR block between the UPDATE and the INSERT for an early exit.
Thanks Jeff,
I continue to recommend other approaches over the 'sequence table' as a rule of thumb, but (a) they can have their uses; and (b) I'm a contrary individual so love to post against the current 😉
As far as the IF @@ERROR is concerned, I just prefer TRY...CATCH, especially in demo code. (Some idiot will come along one day and insert a SET statement between the UPDATE and the IF test so breaking the @@ERROR test.)
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 7, 2009 at 4:27 pm
wesigler (7/7/2009)
Is there a way to incorporate these existing ranges into your approach?
Firstly - thanks 🙂
Probably, but the posted code is designed to allocate in a contiguous manner, so I'm not sure.
If I get a few minutes later today, I'll have a look.
Though I might just leave it as an exercise for the reader 😀
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 7, 2009 at 8:42 pm
Paul White (7/7/2009)
As far as the IF @@ERROR is concerned, I just prefer TRY...CATCH, especially in demo code. (Some idiot will come along one day and insert a SET statement between the UPDATE and the IF test so breaking the @@ERROR test.)
TRY/CATCH is fine in place of the IF @@ERROR thing... I'm really just suggesting that removal of the explicit 2 part transaction will further reduce the chance of deadlocks especially if the proc is called from within an outer explicit transaction.
Contrary is good... so long as it accomplishes something good. So far, no fault on your part there. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2009 at 9:18 pm
Hey Jeff,
I see... (though the exclusive locks taken for the UPDATE and INSERT will always be held until the outermost transaction ends anyway - and under the default isolation level shared locks are not held to end-of-transaction).
If we don't care about allocating a contiguous range of values, the transaction is not required: The original construct was an UPDATE with OUTPUT straight into the allocation table - which guarantees that the two tables stay in step. I added the transaction when I decided to OUTPUT into the table variable first. I'm sure you appreciate that already, just adding this for other readers' benefit.
A production version of this procedure might also benefit from SET LOCK_TIMEOUT and SET DEADLOCK_PRIORITY statements - depending on requirements - and a WITH (READCOMMITTEDLOCK) hint on the UPDATE.
Oh, and tidying up the bug I introduced with the @@ROWCOUNT-based RETURN value when reformatting 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 8, 2009 at 8:17 am
Jeff and Paul,
Thanks for all your help. I am going to try to implement Paul's approach.
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply