首页 新闻 会员 周边

TSQL 高并发情况下, 存储过程创建表结构,创建索引 会出的问题,以及存储过程返回值?

0
[已解决问题] 解决于 2012-04-28 09:48

高并发的情况下,我觉得这个存储过程会出问题

这个存储过程是一个评论系统,评论系统数据量比较大

 

根据 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 
fun5的主页 fun5 | 初学一级 | 园豆:4
提问于:2012-04-26 09:48
< >
分享
最佳答案
0

可以放在事务中处理

奖励园豆:5
dudu | 高人七级 |园豆:31048 | 2012-04-26 11:58
其他回答(2)
0

我觉得是思路的问题,有两点疑问

1、这样创建表是为了什么,为什么不用表分区?

2、一个表1000条记录,是不是不用创建索引?

还是我没理解?

克己复礼 | 园豆:163 (初学一级) | 2012-04-26 10:55

1、分区表不够灵活,只能根据某一列进行分区

2、不是一个表1000条记录,可能是1000 *10000

支持(0) 反对(0) fun5 | 园豆:4 (初学一级) | 2012-04-26 20:56
0

有什么问题?报错?

小材小用 | 园豆:639 (小虾三级) | 2012-04-27 13:06
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册