为了账号安全,请及时绑定邮箱和手机立即绑定

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

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

青春有我 2018-12-07 03:06:59
高并发的情况下,我觉得这个存储过程会出问题 这个存储过程是一个评论系统,评论系统数据量比较大   根据 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 回答

?
守候你守候我

TA贡献1802条经验 获得超10个赞

可以放在事务中处理

查看完整回答
反对 回复 2019-01-07
?
手掌心

TA贡献1942条经验 获得超3个赞

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

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

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

还是我没理解?

查看完整回答
反对 回复 2019-01-07
?
杨魅力

TA贡献1811条经验 获得超6个赞

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

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

查看完整回答
反对 回复 2019-01-07
?
肥皂起泡泡

TA贡献1829条经验 获得超6个赞

有什么问题?报错?

查看完整回答
反对 回复 2019-01-07
  • 4 回答
  • 0 关注
  • 678 浏览
慕课专栏
更多

添加回答

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信