SQLSERVER2012 保存阿拉伯文字乱码的问题

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/whsunbin/article/details/49889223

开发中遇到SQLSERVER2012数据库字段定义为nvarchar保存下面这段文字时,其中阿拉伯语言部分存储到数据库后变为了?号,其它语言能正常保存

测试一下 測試一下 テスト اختبار Испытаний 시험

解决办法

在SQL语句中,保存特殊字符串前面加上一个N后,能正常保存

insert into MyTable(Field01) values (N'测试一下 測試一下 テスト اختبار Испытаний 시험')



展开阅读全文
博主设置当前文章不允许评论。

SQLServer2012 死锁问题讨论

05-18

问题描述:rn系统中有一个存储过程用来插入或更新帐号手续费率数据,首先select看是否存在手续费率,如果不存在则insert否则update.同一时刻会有可能会有多个线程调用该存储过程,但是每个线程之间只会修改不同帐号下的数据,所以并发调用并不会修改同一条数据.代码如下:rnALTER procedure [dbo].[AddOrUpdateInstCommiRate]rn @p_intUserID int,rn @p_chrInstrumentID char(30),rn @p_intTradingDay int, rn @p_chrInvestorID char(12),rn @p_decOpenRatioByMoney decimal(10, 8),rn @p_smmnyOpenRatioByVolume smallmoney,rn @p_decCloseRatioByMoney decimal(10, 8),rn @p_smmnyCloseRatioByVolume smallmoney,rn @p_decCloseTodayRatioByMoney decimal(10, 8),rn @p_smmnyCloseTodayRatioByVolume smallmoney,rn @p_decStrikeRatioByMoney decimal(10,8),rn @p_smmnyStrikeRatioByVolume smallmoneyrnasrnBEGINrn declare @count intrn select @count = count(*)rn from [dbo].[CtpTrade_CommiRate] T1 with(nolock)rn where T1.[CtpInvestorID] = @p_intUserID and T1.[InstrumentID] = @p_chrInstrumentID and T1.[TradingDay] = @p_intTradingDayrnrn if @count = 0rn beginrn INSERT INTO [dbo].[CtpTrade_CommiRate]rn ([CtpInvestorID],rn [InvestorID],rn [InstrumentID],rn [TradingDay],rn [OpenRatioByMoney],rn [OpenRatioByVolume],rn [CloseRatioByMoney],rn [CloseRatioByVolume],rn [CloseTodayRatioByMoney],rn [CloseTodayRatioByVolume],rn [StrikeRatioByMoney],rn [StrikeRatioByVolume])rn VALUES(@p_intUserID,rn @p_chrInvestorID,rn @p_chrInstrumentID,rn @p_intTradingDay,rn @p_decOpenRatioByMoney,rn @p_smmnyOpenRatioByVolume,rn @p_decCloseRatioByMoney,rn @p_smmnyCloseRatioByVolume,rn @p_decCloseTodayRatioByMoney,rn @p_smmnyCloseTodayRatioByVolume,rn @p_decStrikeRatioByMoney,rn @p_smmnyStrikeRatioByVolume)rn endrn elsern beginrn UPDATE [dbo].[CtpTrade_CommiRate]rn SET [InvestorID] = @p_chrInvestorID,rn [OpenRatioByMoney] = @p_decOpenRatioByMoney,rn [OpenRatioByVolume] = @p_smmnyOpenRatioByVolume,rn [CloseRatioByMoney] = @p_decCloseRatioByMoney,rn [CloseRatioByVolume] = @p_smmnyCloseRatioByVolume,rn [CloseTodayRatioByMoney] = @p_decCloseTodayRatioByMoney,rn [CloseTodayRatioByVolume] = @p_smmnyCloseTodayRatioByVolume,rn [StrikeRatioByMoney] = @p_decStrikeRatioByMoney,rn [StrikeRatioByVolume] = @p_smmnyStrikeRatioByVolumern where [CtpInvestorID] = @p_intUserID and [InstrumentID] = @p_chrInstrumentID and [TradingDay] = @p_intTradingDayrn endrn rnENDrn系统运行一段时间后,发现有死锁情况,而且死锁发生在update频繁操作的时间段,而且确定该时间段没有insert操作.rn抓取死锁图如下:rn[img=https://img-bbs.csdn.net/upload/201805/18/1526635161_121672.png][/img]rn死锁数据:rnrn rn rn rn rn rnUPDATE [dbo].[CtpTrade_CommiRate]rn SET [InvestorID] = @p_chrInvestorID,rn [OpenRatioByMoney] = @p_decOpenRatioByMoney,rn [OpenRatioByVolume] = @p_smmnyOpenRatioByVolume,rn [CloseRatioByMoney] = @p_decCloseRatioByMoney,rn [CloseRatioByVolume] = @p_smmnyCloseRatioByVolume,rn [CloseTodayRatioByMoney] = @p_decCloseTodayRatioByMoney,rn [CloseTodayRatioByVolume] = @p_smmnyCloseTodayRatioByVolume,rn [StrikeRatioByMoney] = @p_decStrikeRatioByMoney,rn [StrikeRatioByVolume] = @p_smmnyStrikeRatioByVolumern where [CtpInvestorID] = @p_intUserID and [InstrumentID] = @p_chrInstrumentID and [TradingDay] = @p_intTradingDay rn rnexec AddOrUpdateInstCommiRate 4219,'fb1804',20180326,'8010801001',0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0 rn rn rnexec AddOrUpdateInstCommiRate 4219,'fb1804',20180326,'8010801001',0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0 rn rn rn rn rnUPDATE [dbo].[CtpTrade_CommiRate]rn SET [InvestorID] = @p_chrInvestorID,rn [OpenRatioByMoney] = @p_decOpenRatioByMoney,rn [OpenRatioByVolume] = @p_smmnyOpenRatioByVolume,rn [CloseRatioByMoney] = @p_decCloseRatioByMoney,rn [CloseRatioByVolume] = @p_smmnyCloseRatioByVolume,rn [CloseTodayRatioByMoney] = @p_decCloseTodayRatioByMoney,rn [CloseTodayRatioByVolume] = @p_smmnyCloseTodayRatioByVolume,rn [StrikeRatioByMoney] = @p_decStrikeRatioByMoney,rn [StrikeRatioByVolume] = @p_smmnyStrikeRatioByVolumern where [CtpInvestorID] = @p_intUserID and [InstrumentID] = @p_chrInstrumentID and [TradingDay] = @p_intTradingDay rn rnexec AddOrUpdateInstCommiRate 4218,'fb1804',20180326,'8010801002',0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0 rn rn rnexec AddOrUpdateInstCommiRate 4218,'fb1804',20180326,'8010801002',0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0 rn rn rn rn rnUPDATE [dbo].[CtpTrade_CommiRate]rn SET [InvestorID] = @p_chrInvestorID,rn [OpenRatioByMoney] = @p_decOpenRatioByMoney,rn [OpenRatioByVolume] = @p_smmnyOpenRatioByVolume,rn [CloseRatioByMoney] = @p_decCloseRatioByMoney,rn [CloseRatioByVolume] = @p_smmnyCloseRatioByVolume,rn [CloseTodayRatioByMoney] = @p_decCloseTodayRatioByMoney,rn [CloseTodayRatioByVolume] = @p_smmnyCloseTodayRatioByVolume,rn [StrikeRatioByMoney] = @p_decStrikeRatioByMoney,rn [StrikeRatioByVolume] = @p_smmnyStrikeRatioByVolumern where [CtpInvestorID] = @p_intUserID and [InstrumentID] = @p_chrInstrumentID and [TradingDay] = @p_intTradingDay rn rnexec AddOrUpdateInstCommiRate 4218,'fb1804',20180326,'8010801002',0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0 rn rn rnexec AddOrUpdateInstCommiRate 4218,'fb1804',20180326,'8010801002',0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0 rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rn rnrn现在结果很明确是 U锁 导致了循环等待 进而导致死锁rn查了不少资料还是不太清楚为什么会发生这种情况,按道理来说只更新一条数据不应该产生死锁,求个位大神赐教 论坛

没有更多推荐了,返回首页