Strictly speaking, these three production methods have certain intersections, and their positioning methods will be explained below
Strictly speaking, these three production methods have certain intersections, and their positioning methods will be explained below
There are three primary key generation methods:
1. Automatic database generation
Ii. GUID
Iii. Development and Creation
Strictly speaking, these three production methods have certain intersections, and their positioning methods will be explained below.
The first method is mainly to locate it in the self-growth identification seed: You can set the starting value and the Growth step. The advantage is that concurrent tasks are completely handed over to the database engine for management. You do not have to worry that two identical IDs will be generated when multiple users use them. The disadvantage is that most databases do not directly obtain the ID. for developers, the ID generation method is transparent, and developers can hardly intervene in this method. Migration of data is not very convenient.
Because of the above advantages and disadvantages, this self-increasing ID is generally used to design the primary key of the basic table (basic information for system operation, such as the employee table), with very few (not at all) used for the primary and Foreign keys of a master-slave table, because the ID of the master table must be determined before the associated ID of the slave table can be located when data is generated and associated with the master table.
Example (MsSQL ):
The Code is as follows:
-- Create a test table
Create table [Identity] (
Id int identity (1, 2) not null primary key, -- start value of seed 1, step 2
Number VARCHAR (20) unique not null,
Name VARCHAR (20) not null,
Password VARCHAR (20) defaults (123 ),
Description VARCHAR (40) NULL
)
-- Insert record
Insert into [Identity] (Number, Name, Description) VALUES ('001', '1st', 'Id = 1, because the start value is 1 ')
Insert into [Identity] (Number, Name, Description) VALUES ('002 ', '2nd', 'Id = 3, because the start value is 1, step 2 ')
Insert into [Identity] (Number, Name, Description) VALUES ('003 ', '3rd', 'Id = 5. An error is returned because the characters are too long, this Id is discarded after being generated ')
Insert into [Identity] (Number, Name, Description) VALUES ('004 ', '4th', 'Id = 7 not 5, because the insertion of the third record fails ')
-- Retrieve records and view results
SELECT * FROM [Identity]
Result:
(One row is affected)
(One row is affected)
Message 8152, level 16, status 14, 3rd rows
Truncates string or binary data.
The statement has been terminated.
(One row is affected)
(3 rows affected)
Id Number Name Password Description
1 001 1st 123 Id = 1 because the starting value is 1
3 002 2nd 123 Id = 3, because the start value is 1, step 2
7 004 4th 123 Id = 7 not 5, because the insertion of the third record fails.
Second, GUID is Globally Unique Identifier, also known as UUID (Universally Unique IDentifier). It is a Globally Unique Identifier. GUID is generally composed of 32-bit hexadecimal values, it contains the NIC address, time, and other information. No two computers will generate the same GUID, and its advantages are unique. When database integration is required, it can save a lot of labor. For example, the system of the Headquarters and the branch offices runs independently, and the data of all the branches needs to be submitted to the Headquarters on a regular basis, which can avoid primary key conflicts during data merging. Meanwhile, the GUID also has the characteristics of self-growth identification seed, developers do not need to pay too much attention. However, the GUID information is large and occupies a large amount of space. The associated retrieval efficiency is not very high, and it is not readable for 32-bit hexadecimal systems, although the primary key is meaningless to the user, it is inconvenient to design or debug communication.
In the long run, to ensure data portability, GUID is usually used as the primary key.
Example (MsSQL ):
The Code is as follows:
-- Create a test table
Create table guid (
Id uniqueidentifier not null primary key, -- you can also use a string to save
Number VARCHAR (20) unique not null,
Name VARCHAR (20) not null,
Password VARCHAR (20) defaults (123)
)
-- Insert record
Insert into guid (Id, Number, Name) VALUES (NewID (), '001', '1st ')
Insert into guid (Id, Number, Name) VALUES (NewID (), '002', '2nd ')
Insert into guid (Id, Number, Name) VALUES (NewID (), '003 ', '3rd ')
-- Retrieve records and view results
SELECT * FROM GUID
Result:
Id Number Name Password
8E194F55-B4D3-4C85-8667-33BC6CD33BBC 001 1st 123
7141F202-7D0E-4992-9164-5043EC9FC6F6 002 2nd 123
E0E365A0-8748-4656-AF24-5D0B216D2095 003 3rd 123
The third method is development and creation. Its convenience lies in controllability. This controllability refers to its composition form, which can be an integer or a regression type, you can give a variety of components and production forms based on the actual situation. Some friends may come up with a ticket number, such as 20120716001 or PI-201207-0001. That's right, self-creation also applies to these similar applications.
When it comes to self-creation, most of the first thing that comes to mind is Max (Id) + 1, but in fact, it is not very good for customization (the primary key is unnecessary when there is a certain sense of information such as the production order number) and concurrent processing. For example, the maximum number in the current table is 1000. When users C1 and C2 take this Id for processing at the same time, 1001 is returned, resulting in storage failure. The general practice is to lock values, but when multiple users operate frequently, performance is a big problem. One of the main reasons is to directly operate business data tables.
In this case, the solution is to use a key-value table to save the table name, current or next Id, and other information. If multiple table IDs in the system use this method, then, the key-value table will have multiple corresponding rule records. Of course, the IDs of all the tables in the database can be generated from one source according to the same rules, you only need one rule record in the key-value table.
Let's take a look at the evolution of such an example of using a key-value table (MsSQL ):
The Code is as follows:
-- Create a key-value table
Create table KeyTable (
Id int identity (1, 1) primary key not null,
TCode VARCHAR (20) unique not null,
TName VARCHAR (50) not null,
TKey int not null,
)
GO
-- Insert test records
Insert into KeyTable (TCode, TName, TKey)
VALUES ('t001', 'test', 0)
GO
-- Create a stored procedure for obtaining the specified table ID. You can also change it to a function.
Create procedure UP_NewTableID
@ TCode VARCHAR (20), @ NextID INT OUTPUT
AS
DECLARE @ CurTKey INT, @ NextTKey INT
Begin tran TransID
SELECT @ CurTKey = TKey
FROM KeyTable
WHERE TCode = @ TCode
IF @ ROWCOUNT = 0
BEGIN
Rollback tran TransID
RAISERROR ('Warning: No such row is exists)
RETURN
END
SET @ NextTKey = @ CurTKey + 1
-- Waitfor delay '00: 00: 05'
UPDATE KeyTable
SET TKey = @ NextTKey
WHERE TCode = @ TCode
IF @ ROWCOUNT = 0
BEGIN
Rollback tran TransID
RAISERROR ('Warning: No such row is updated)
RETURN
END
Commit tran TransID
SET @ NextID = @ NextTKey
GO
Run the Stored Procedure UP_NewTableID:
The Code is as follows:
DECLARE @ NextID INT
EXEC UP_NewTableID 't001', @ NextID OUTPUT
PRINT @ NextID
When running, the system will find that it is normal and the obtained results are correct. However, in the case of high concurrency, multiple users may obtain the same ID. If the obtained ID is used to save the records in the corresponding table, only one user can save the data successfully.
The following is a simulation of the concurrency. In the above stored procedure, remove the comment of the statement waitfor delay '00: 00: 05 'in UP_NewTableID, open the forms of the Three query analyzers, and execute the preceding statement in sequence.
It is expected to obtain 1, 2, 3 respectively, but you may find that the running results of multiple forms are: 1. This means that before the update statement is executed, the ID obtained by everyone is 0, so the next value is 1. (The actual value depends on the DELAY parameter size and running time)
From this perspective, some may think about whether the original ID can be determined when the update statement is executed? Modification Process:
The Code is as follows:
Alter procedure UP_NewTableID
@ TCode VARCHAR (20), @ NextID INT OUTPUT
AS
DECLARE @ CurTKey INT, @ NextTKey INT
Begin tran TransID
SELECT @ CurTKey = TKey
FROM KeyTable
WHERE TCode = @ TCode
IF @ ROWCOUNT = 0 BEGIN
Rollback tran TransID
RAISERROR ('Warning: No such row is exists)
RETURN
END
SET @ NextTKey = @ CurTKey + 1
Waitfor delay '00: 00: 05'
UPDATE KeyTable
SET TKey = @ NextTKey
WHERE TCode = @ TCode AND TKey = @ CurTKey -- TKey verification is added here.
IF @ ROWCOUNT = 0 BEGIN
Rollback tran TransID
RAISERROR ('Warning: No such row is updated)
RETURN
END
Commit tran TransID
SET @ NextID = @ NextTKey
GO
If you open three execution processes to simulate concurrency, there will be two forms:
Message 50000, level 16, status 1, process UP_NewTableID, 28th rows
Warning: No such row is updated
As a result, the user operation will still fail due to concurrency, but it is earlier than the last time that at least the error occurred.
Is there a better way, from the query to the update to the end of the entire transaction process, there will be no other way to insert it into it to stir up the problem, the answer is very clear, yes, use the lock! Select the appropriate lock, otherwise the effect will be the same as above.
The Code is as follows:
Alter procedure UP_NewTableID
@ TCode VARCHAR (20), @ NextID INT OUTPUT
AS
DECLARE @ CurTKey INT, @ NextTKey INT
Begin tran TransID
SELECT @ CurTKey = TKey
FROM KeyTable WITH (UPDLOCK) -- use the update lock and keep it until the transaction is completed.
WHERE TCode = @ TCode
IF @ ROWCOUNT = 0 BEGIN
Rollback tran TransID
RAISERROR ('Warning: No such row is exists)
RETURN
END
SET @ NextTKey = @ CurTKey + 1
Waitfor delay '00: 00: 05'
UPDATE KeyTable
SET TKey = @ NextTKey
WHERE TCode = @ TCode -- you do not need to verify whether the TKey is the same as the SELECT
Commit tran TransID
SET @ NextID = @ NextTKey
GO
You can open N (N> = 2) forms for testing. All operations are serialized and the result is what we want. So comment or remove the statement waitfor delay '00: 00: 05 'that imitates concurrency.
As mentioned above, this is also suitable for the generation of document numbers similar to codes. You only need to slightly modify the previous Code and key-value table. If you are interested, you can try it. If this number is obtained from the front end and applied to each record, there may be a possibility of a hop. To ensure that there is no checksum, you can use the checksum table to periodically scan and apply the checksum record to other records. Another solution is to place the record storage operations into the serial number generation process to form a serialized transaction.
As the saying goes, radish and cabbage have their own love. What kind of truth do you use.
