原文出处:http://www.52rs.net/ArticleView.aspx?gID=71bd9b1d-ad30-4f6e-896d-fed7dfbc1b3d 截选
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 ,
ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
[ 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 ,
ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
2、存储过程代码
1
Create
procedure
[
dbo
]
.
[
GetSerialNo
]
2 (
3 @sCode varchar ( 50 )
4 )
5
6 as
7
8 -- exec GetSerialNo
9
10 begin
11
12 Declare @sValue varchar ( 16 ),
13
14 @dToday datetime ,
15
16 @sQZ varchar ( 50 ) -- 这个代表前缀
17
18 Begin Tran
19
20 Begin Try
21
22 -- 锁定该条记录,好多人用lock去锁,起始这里只要执行一句update就可以了
23 -- 在同一个事物中,执行了update语句之后就会启动锁
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 -- 因子表中没有记录,插入初始值
31
32 If @sValue is null
33
34 Begin
35
36 Select @sValue = convert ( bigint , convert ( varchar ( 6 ), getdate (), 12 ) + ' 000001 ' )
37
38 Update SerialNo set sValue = @sValue where sCode = @sCode
39
40 end else
41
42 Begin -- 因子表中没有记录
43
44 Select @dToday = substring ( @sValue , 1 , 6 )
45
46 -- 如果日期相等,则加1
47
48 If @dToday = convert ( varchar ( 6 ), getdate (), 12 )
49
50 Select @sValue = convert ( varchar ( 16 ), ( convert ( bigint , @sValue ) + 1 ))
51
52 else -- 如果日期不相等,则先赋值日期,流水号从1开始
53
54 Select @sValue = convert ( bigint , convert ( varchar ( 6 ), getdate (), 12 ) + ' 000001 ' )
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
76 end
77
78
2 (
3 @sCode varchar ( 50 )
4 )
5
6 as
7
8 -- exec GetSerialNo
9
10 begin
11
12 Declare @sValue varchar ( 16 ),
13
14 @dToday datetime ,
15
16 @sQZ varchar ( 50 ) -- 这个代表前缀
17
18 Begin Tran
19
20 Begin Try
21
22 -- 锁定该条记录,好多人用lock去锁,起始这里只要执行一句update就可以了
23 -- 在同一个事物中,执行了update语句之后就会启动锁
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 -- 因子表中没有记录,插入初始值
31
32 If @sValue is null
33
34 Begin
35
36 Select @sValue = convert ( bigint , convert ( varchar ( 6 ), getdate (), 12 ) + ' 000001 ' )
37
38 Update SerialNo set sValue = @sValue where sCode = @sCode
39
40 end else
41
42 Begin -- 因子表中没有记录
43
44 Select @dToday = substring ( @sValue , 1 , 6 )
45
46 -- 如果日期相等,则加1
47
48 If @dToday = convert ( varchar ( 6 ), getdate (), 12 )
49
50 Select @sValue = convert ( varchar ( 16 ), ( convert ( bigint , @sValue ) + 1 ))
51
52 else -- 如果日期不相等,则先赋值日期,流水号从1开始
53
54 Select @sValue = convert ( bigint , convert ( varchar ( 6 ), getdate (), 12 ) + ' 000001 ' )
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
76 end
77
78
废话不多说了,看测试代码和效果图
第一张图(左)是单独对进货单执行循环多进程
第二张图(中)是单独对发货单执行循环多进程
第三张图(右)是对进货单发货单同时执行循环多进程
也就是上面三个Thread,自己注释测试就可以了。
测试并发代码
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
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
总结:我写的整个方法和存储过程如果要实现流水号的话,还是相当可以的。在当前测试过程中是可以避免并发而导致数据的同步性出错的情况。
请注明出处[小虎原创]:http://www.52rs.net/ArticleView.aspx?gID=71bd9b1d-ad30-4f6e-896d-fed7dfbc1b3d