高并发的情况下,我觉得这个存储过程会出问题
这个存储过程是一个评论系统,评论系统数据量比较大
根据 SourceID (源ID) 比如软件ID,新闻ID等。。。进行创建表
源ID 每隔1000个创建一张表
表结构如下:
[ID] [int] IDENTITY(1,1) NOT NULL,[ParentID] [int] NOT NULL,[SourceID] [int] NOT NULL,[NickName] [nvarchar](20) NOT NULL,[Content] [nvarchar](300) NOT NULL,[Datetime] [datetime] NOT NULL,[IP] [nvarchar](30) NOT NULL,[City] [nvarchar](30) NOT NULL,[BeFiltered] [bit] NOT NULL,[Enable] [bit] NOT NULL,[Lou] [int] NOT NULL,[Ding] [int] NOT NULL,[Cai] [int] NOT NULL,
高并发的情况下,如果两个人同时执行了这个存储过程,有可能会同时创建表,索引错误,评论会丢失
1 ALTER proc [dbo].[procAddComment]
2 (
3 @ParentID int,
4 @SourceID int,
5 @NickName nvarchar(20),
6 @Content nvarchar(300),
7 @IP nvarchar(30),
8 @City nvarchar(30),
9 @BeFiltered bit,
10 @Disable bit,
11 @Key nvarchar(50),
12 @InsertedID int OUTPUT
13 )
14 as
15 begin
16 declare @tableName nvarchar(80);
17 declare @tableArea int;
18 declare @mod int;
19
20 declare @Size int;
21 set @Size = 1000;
22
23 set @mod = @SourceID % @Size;
24 if @mod > 0
25 set @tableArea = Cast((@SourceID-1) / @Size as int) + 1;
26 else
27 set @tableArea = Cast((@SourceID-1) / @Size as int);
28
29
30
31
32 set @tableName = 'comments_' + @Key + Cast(@tableArea as nvarchar(10));
33
34 if not Exists(select 'x' from [CommentsTables] where [Key]=@Key and [TableName]=@tableName)
35 begin
36
37 declare @StartID int;
38 declare @EndID int;
39
40 set @EndID = @tableArea * @Size;
41 set @StartID = @EndID - (@Size-1);
42
43
44 --创建表
45 declare @CreateSQL nvarchar(MAX);
46 set @CreateSQL =
47 'Create table [dbo].['+@tableName+'](
48 [ID] [int] IDENTITY(1,1) NOT NULL,
49 [ParentID] [int] NOT NULL,
50 [SourceID] [int] NOT NULL,
51 [NickName] [nvarchar](20) NOT NULL,
52 [Content] [nvarchar](300) NOT NULL,
53 [Datetime] [datetime] NOT NULL,
54 [IP] [nvarchar](30) NOT NULL,
55 [City] [nvarchar](30) NOT NULL,
56 [BeFiltered] [bit] NOT NULL,
57 [Disable] [bit] NOT NULL,
58 [Lou] [int] NOT NULL,
59 [Ding] [int] NOT NULL,
60 [Cai] [int] NOT NULL,
61 CONSTRAINT [PK_'+@tableName+'] PRIMARY KEY CLUSTERED
62 (
63 [ID] ASC
64 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
65 ) ON [PRIMARY]'
66
67 EXEC(@CreateSQL);
68
69
70 --创建索引 ID DESC
71 EXEC('
72 CREATE UNIQUE NONCLUSTERED INDEX [IX_'+@tableName+'_ID_DESC] ON [dbo].['+@tableName+']
73 (
74 [ID] DESC
75 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]');
76
77 --创建索引 Ding DESC
78 EXEC('
79 CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Ding_DESC] ON [dbo].['+@tableName+']
80 (
81 [Ding] DESC
82 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]');
83
84 --创建索引 SourceID DESC
85 EXEC('
86 CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_SourceID_DESC] ON [dbo].['+@tableName+']
87 (
88 [SourceID] DESC
89 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]');
90
91
92 --创建默认值
93 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_ParentID] DEFAULT ((0)) FOR [ParentID]');
94
95 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Datetime] DEFAULT (getdate()) FOR [Datetime]');
96
97 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_BeFiltered] DEFAULT ((0)) FOR [BeFiltered]');
98
99 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Disable] DEFAULT ((0)) FOR [Disable]');
100
101 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Lou] DEFAULT ((1)) FOR [Lou]');
102
103 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Ding] DEFAULT ((0)) FOR [Ding]');
104
105 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Cai] DEFAULT ((0)) FOR [Cai]');
106
107
108 --
109 Insert Into [CommentsTables]([Key],[TableName],[StartID],[EndID]) values(@Key,@tableName,@StartID,@EndID);
110 end
111
112
113 set @NickName = Replace(@NickName,'''','''''');
114 set @Content = Replace(@Content,'''','''''');
115 set @IP = Replace(@IP,'''','''''');
116 set @City = Replace(@City,'''','''''');
117
118 Exec('Insert Into dbo.['+@tableName+'](ParentID,SourceID,NickName,Content,IP,City,BeFiltered,[Disable])
119 values ('+@ParentID+','+@SourceID+','''+@NickName+''','''+@Content+''','''+@IP+''','''+@City+''','+@BeFiltered+','+@Disable+');');
120
121 set @InsertedID = SCOPE_IDENTITY();--这一句有问题
122
123 end
- 4 回答
- 0 关注
- 678 浏览
添加回答
举报
0/150
提交
取消