看了文章《弃用数据库自增ID,曝光一下我自己用到的解决方法 》,居然还显示到首页上去。我却觉得如果新手不辨真假,盲目顺从,那么会造成误人子弟的事实。
首先从作者的写这篇文章的目的上讲他想实现的无非是下面目的:
1、不用自增长ID,因为自增长移植的时候不方便。
2、这个存储过程可以很高效的产生唯一性的自增长ID
从我小虎的认知上来回答:
1、对于作者的第一点,完全可以用Guid来替代自增长,或者在移植的时候,可以先去掉自增长的属性。
有的人说Guid性能比不上自增长ID,这里我们先不讨论这一点,个人认为效率问题主要体现在索引技巧上。
2、关键是作者的第二点,完全是不正确的,也是我写这篇文章的首要目的。因为这个存储过程根本就没有实现在多并发(多用户)的情况
下能真正产生唯一性的主键ID。
我们看原作者的代码:
1
create
procedure
[
dbo
]
.
[
up_get_table_key
]
2
(
3
@table_name
varchar
(
50
),
4
@key_value
int
output
5
)
6
as
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
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
29
--
commit tran
30
commit
tran
31
if
@@error
>
0
32
rollback
tran
33
end
请看我的测试代码以及并发结果图
protected
void
Page_Load(
object
sender, EventArgs e)
![ExpandedBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedBlockStart.gif)
...
{
if (!IsPostBack)
![ExpandedSubBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
...{
for (int i = 0; i < 100; i++)
![ExpandedSubBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
...{
System.Threading.Thread temp3 = new System.Threading.Thread(new System.Threading.ThreadStart(Run3));
![InBlock.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/InBlock.gif)
temp3.Start();
![InBlock.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/InBlock.gif)
}
}
}
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
private
void
Run3()
![ExpandedBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedBlockStart.gif)
...
{
![ExpandedSubBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
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/>");
}
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
结果图1
![r_id.jpg](https://i-blog.csdnimg.cn/blog_migrate/2d41d589bbdb913b51d1227dfce01f95.jpeg)
从上面多线程的测试效果上来说,绝对不要去按照原作者的方法去做。
本来这么晚了,我不想在写了,但是不想让别人说我不厚道,说我只说不做,所以,我打算就再写一个切实可行的例子,供大家参考,仅仅作为抛砖引玉。
但是本人是经过多线程测试的,至少在我测试情况下不会出现并发出差错的情况。
1、表结构和效果图,这个表是用来存储基础因子的,需要的可以拓展字段,比如,升序,降序,起始序号等。
CREATE
TABLE
[
dbo
]
.
[
SerialNo
]
(
[
sCode
]
[
varchar
]
(
50
)
NOT
NULL
,
--
主键也是多个流水号的类别区分
[
sName
]
[
varchar
]
(
100
)
NULL
,
--
名称,备注形式
[
sQZ
]
[
varchar
]
(
50
)
NULL
,
--
前缀
[
sValue
]
[
varchar
]
(
80
)
NULL
,
--
因子字段
CONSTRAINT
[
PK_SerialNo
]
PRIMARY
KEY
CLUSTERED
(
[
sCode
]
ASC
)
WITH
(PAD_INDEX
=
OFF
, STATISTICS_NORECOMPUTE
=
OFF
, IGNORE_DUP_KEY
=
OFF
, ALLOW_ROW_LOCKS
=
ON
,
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
ALLOW_PAGE_LOCKS
=
ON
)
ON
[
PRIMARY
]
)
ON
[
PRIMARY
]
![r_bjg2.jpg](https://i-blog.csdnimg.cn/blog_migrate/52ab9d5e8d517194b1142d0c3a5c1b9a.jpeg)
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
2、存储过程代码
1
Create
procedure
[
dbo
]
.
[
GetSerialNo
]
2
(
3
@sCode
varchar
(
50
)
4
)
5
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
6
as
7
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
8
--
exec GetSerialNo
9
10
begin
11
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
12
Declare
@sValue
varchar
(
16
),
13
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
14
@dToday
datetime
,
15
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
16
@sQZ
varchar
(
50
)
--
这个代表前缀
17
18
Begin
Tran
19
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
20
Begin
Try
21
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
22
--
锁定该条记录,好多人用lock去锁,起始这里只要执行一句update就可以了
23
--
在同一个事物中,执行了update语句之后就会启动锁
24
Update
SerialNo
set
sValue
=
sValue
where
sCode
=
@sCode
25
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
26
Select
@sValue
=
sValue
From
SerialNo
where
sCode
=
@sCode
27
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
28
Select
@sQZ
=
sQZ
From
SerialNo
where
sCode
=
@sCode
29
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
30
--
因子表中没有记录,插入初始值
31
32
If
@sValue
is
null
33
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
34
Begin
35
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
36
Select
@sValue
=
convert
(
bigint
,
convert
(
varchar
(
6
),
getdate
(),
12
)
+
'
000001
'
)
37
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
38
Update
SerialNo
set
sValue
=
@sValue
where
sCode
=
@sCode
39
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
40
end
else
41
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
42
Begin
--
因子表中没有记录
43
44
Select
@dToday
=
substring
(
@sValue
,
1
,
6
)
45
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
46
--
如果日期相等,则加1
47
48
If
@dToday
=
convert
(
varchar
(
6
),
getdate
(),
12
)
49
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
50
Select
@sValue
=
convert
(
varchar
(
16
), (
convert
(
bigint
,
@sValue
)
+
1
))
51
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
52
else
--
如果日期不相等,则先赋值日期,流水号从1开始
53
54
Select
@sValue
=
convert
(
bigint
,
convert
(
varchar
(
6
),
getdate
(),
12
)
+
'
000001
'
)
55
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
56
57
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
58
Update
SerialNo
set
sValue
=
@sValue
where
sCode
=
@sCode
59
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
60
End
61
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
62
Select
result
=
@sQZ
+
@sValue
63
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
64
Commit
Tran
65
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
66
End
Try
67
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
68
Begin
Catch
69
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
70
Rollback
Tran
71
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
72
Select
result
=
'
Error
'
73
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
74
End
Catch
75
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
76
end
77
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
78
废话不多说了,看测试代码和效果图
第一张图(左)是单独对进货单执行循环多进程
第二张图(中)是单独对发货单执行循环多进程
第三张图(右)是对进货单发货单同时执行循环多进程
也就是上面三个Thread,自己注释测试就可以了。
测试并发代码
1
protected
void
Page_Load(
object
sender, EventArgs e)
2
![ExpandedBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedBlockStart.gif)
...
{
3
if (!IsPostBack)
4![ExpandedSubBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
...{
5
for (int i = 0; i < 100; i++)
6![ExpandedSubBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
...{
7
System.Threading.Thread temp = new System.Threading.Thread(new System.Threading.ThreadStart(Run));
8
System.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
![None.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/None.gif)
17
private
void
Run()
18
![ExpandedBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedBlockStart.gif)
...
{
19![ExpandedSubBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
System.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
![ExpandedBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedBlockStart.gif)
...
{
25![ExpandedSubBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
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
![ExpandedBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedBlockStart.gif)
...
{
31![ExpandedSubBlockStart.gif](http://52rs.net/SysAdmin/fckeditor/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
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
总结:我写的整个方法和存储过程如果要实现流水号的话,还是相当可以的。在当前测试过程中是可以避免并发而导致数据的同步性出错的情况。
请注明出处[小虎原创]:http://www.52rs.net/ArticleView.aspx?gID=71bd9b1d-ad30-4f6e-896d-fed7dfbc1b3d