I read the article "abandon database auto-increment ID, expose the solution I used" and display it on the homepage. I think that if a newbie does not distinguish between true and false and blindly follows suit, it will lead to the fact that he is mistaken .. Net generate the serial number
First, from the author's purpose of writing this article, what he wants to achieve is nothing more than the following:
1. You do not need to increase your ID because it is inconvenient to transplant your ID.
2. This stored procedure can efficiently produce unique auto-increment IDs.
From my understanding, I replied:
1. For the author's first point, guid can be used to replace auto-growth, or the attributes of auto-growth can be removed first during transplantation.
Some people say that the guid performance is inferior to the self-growth ID. We will not discuss this point here. I personally think that the efficiency problem is mainly reflected in the index technique.
2. The key is the author's second point. It is totally incorrect and is also the primary purpose of my article. Because this stored procedure is not implemented in the case of multiple concurrency (multiple users)
To generate a unique primary key ID.
Let's look at the original author's code:
1 create procedure [DBO]. [up_get_table_key]
2 (
3 @ table_name varchar (50 ),
4 @ key_value int output
5)
6As
7 begin
8 begin tran
9 declare @ key int
10
11 -- initialize the key with 1
12 set @ key = 1
13 -- whether the specified table is exist
14 if not exists (select table_name from table_key where table_name = @ table_name)
15 begin
16 insert into table_key values (@ table_name, @ key) -- default key vlaue: 1
17 end
18 -- step increase
19 else
20 begin
21 select @ key = key_value from table_key with (nolock) Where table_name = @ table_name
22 set @ key = @ key + 1
23 -- Update the key value by table name
24 update table_key set key_value = @ key where table_name = @ table_name
25 end
26 -- set ouput Value
27 Set @ key_value = @ key
28
29 -- commit tran
30 commit tran
31 if @ error> 0
32 rollback tran
33end
Please refer to my test code and concurrent result Diagram
Protected void page_load (Object sender, eventargs E)
{
If (! Ispostback)
{
For (INT I = 0; I <100; I ++)
{
System. Threading. Thread temp3 = new system. Threading. Thread (new system. Threading. threadstart (run3 ));
Temp3.start ();
}
}
}
Private void run3 ()
{
System. Data. sqlclient. sqlparameter [] P = {
New system. Data. sqlclient. sqlparameter ("@ table_name", "test "),
New system. Data. sqlclient. sqlparameter ("@ key_value", system. Data. sqldbtype. INT )};
P [1]. Direction = system. Data. parameterdirection. output;
Sqlhelper. executestoredprocedure ("up_get_table_key", P );
Response. Write (P [1]. value. tostring () + "<br/> ");
}
Result chart1
In terms of the results of the multi-threaded test above, do not follow the original method.
It was so late that I didn't want to write it, but I didn't want others to say that I was not so kind and said that I only did not do it. So I plan to write another practical example, for your reference, just as a reference.
However, I have passed the multi-thread test, at least in my test, there will be no errors.
1, Table structure and, this table is used to store the basic factor, you need to expand the field, such as ascending, descending, Starting sequence number, and so on.
Create Table [DBO]. [serialno] (
[Scode] [varchar] (50) not null, -- the primary key is also the classification of multiple sequential numbers
[Sname] [varchar] (100) null, -- name, remarks form
[Sqz] [varchar] (50) null, -- prefix
[Svalue] [varchar] (80) null, -- factor Field
Constraint [pk_serialno] primary key clustered
(
[Scode] ASC
) With (pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on,
Allow_page_locks = on) on [primary]
) On [primary]
2Stored Procedure Code
1 create procedure [DBO]. [getserialno]
2 (
3 @ scode varchar (50)
4)
5
6
7
8 -- exec getserialno
9
10 begin
11
12 declare @ svalue varchar (16 ),
13
14 @ dtoday datetime,
15
16 @ sqz varchar (50) -- this represents the prefix
17
18 begin tran
19
20 begin try
21
22 -- lock the record. Many people use lock to lock the record. You only need to execute an update statement at the beginning.
23 -- in the same thing, the lock is started after the update statement is executed.
24 update serialno set svalue = svalue where scode = @ scode
25
26 select @ svalue = svalue from serialno where scode = @ scode
27
28 select @ sqz = sqz from serialno where scode = @ scode
29
30 -- no records exist in the factor table, and the initial value is inserted.
31
32 If @ svalue is null
33
34 begin
35
36 select @ svalue = convert (bigint, convert (varchar (6), getdate (), 12) + '123 ')
37
38 update serialno set svalue = @ svalue where scode = @ scode
39
40 end else
41
42 begin -- no records in the factor table
43
44 select @ dtoday = substring (@ svalue, 1, 6)
45
46 -- if the date is equal, add 1
47
48 if @ dtoday = convert (varchar (6), getdate (), 12)
49
50 select @ svalue = convert (varchar (16), (convert (bigint, @ svalue) + 1 ))
51
52 else -- if the date is not equal, the date is assigned first, and the sequential number starts from 1.
53
54 select @ svalue = convert (bigint, convert (varchar (6), getdate (), 12) + '123 ')
55
56
57
58 update serialno set svalue = @ svalue where scode = @ scode
59
60 end
61
62 select result = @ sqz + @ svalue
63
64 commit tran
65
66 end try
67
68 begin catch
69
70 rollback tran
71
72 select result = 'error'
73
74 end catch
75
76end
77
78
Let's talk about the test code and
The first figure (left) is a separate multi-process loop for the import ticket
The second figure (in) is a separate multi-process loop for the invoice
The third figure (right) is to execute loop multi-process at the same time for the invoice
That is, the above three threads can be annotated and tested by yourself.
Test concurrent code
1 protected void page_load (Object sender, eventargs E)
2 {
3 if (! Ispostback)
4 {
5 For (INT I = 0; I <100; I ++)
6 {
7 system. Threading. Thread temp = new system. Threading. Thread (new system. Threading. threadstart (run ));
8system. Threading. Thread temp2 = new system. Threading. Thread (new system. Threading. threadstart (run2 ));
9 system. Threading. Thread temp3 = new system. Threading. Thread (new system. Threading. threadstart (run3 ));
10 temp. Start ();
11 temp2.start ();
12 temp3.start ();
13}
14}
15}
16
17 private void run ()
18 {
19system. Data. sqlclient. sqlparameter [] P = {
20 new system. Data. sqlclient. sqlparameter ("@ scode", "jhd ")};
21 response. Write (sqlhelper. executestoredprocedure ("getserialno", P). Rows [0] [0]. tostring () + "<br/> ");
22}
23 private void run2 ()
24 {
25 system. Data. sqlclient. sqlparameter [] P = {
26 new system. Data. sqlclient. sqlparameter ("@ scode", "XSD ")};
27 response. Write (sqlhelper. executestoredprocedure ("getserialno", P). Rows [0] [0]. tostring () + "<br/> ");
28}
29 private void run3 ()
30 {
31 system. Data. sqlclient. sqlparameter [] P = {
32 new system. Data. sqlclient. sqlparameter ("@ table_name", "test "),
33 new system. Data. sqlclient. sqlparameter ("@ key_value", system. Data. sqldbtype. INT )};
34 p [1]. Direction = system. Data. parameterdirection. output;
35 sqlhelper. executestoredprocedure ("up_get_table_key", P );
36 response. Write (P [1]. value. tostring () + "<br/> ");
37}
38
Summary: The entire method and stored procedure I wrote can still be used to implement the serial number. In the current test process, data synchronization errors caused by concurrency can be avoided.
Please indicate the source [Tiger original]: http://www.52rs.net/ArticleView.aspx? Gid = 71bd9b1d-ad30-4f6e-896d-fed7dfbc1b3d
