统计分页一些sql,避免死锁

USE [QPTreasureDB]
GO
/****** Object: StoredProcedure [dbo].[GameStatistics] Script Date: 2018/8/16 10:33:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[GameStatistics]
@Type INT,
@GameType INT,
@Year INT,
@Month INT,
@Day INT,
@PageIndex INT,
@PageSize INT
AS -- 属性设置
SET NOCOUNT ON;

-- 执行逻辑
BEGIN
IF (@Type = 0)
BEGIN
IF (@GameType = 2)
BEGIN
SELECT SUM(-lwinscore) AS TotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS PlayIn, --玩入
SUM(- (lwinscore   lalladdscore)) AS PlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = CASE @Year
WHEN -1 THEN YEAR(insertTime)
ELSE @Year END
AND MONTH(insertTime) = CASE @Month
WHEN -1 THEN MONTH(insertTime)
ELSE @Month END
AND DAY(insertTime) = CASE @Day
WHEN -1 THEN DAY(insertTime)
ELSE @Day END;
END;
IF (@GameType = 3)
BEGIN
SELECT YEAR(insertTime) Year,
SUM(-lwinscore) AS RichTotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS RichPlayIn, --玩入
SUM(- (lwinscore   lalladdscore)) AS RichPlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
GROUP BY YEAR(insertTime);
END;
END;
ELSE IF (@Type = 1) --月统计
BEGIN
SELECT @Year Year,
MONTH(insertTime) Month,
SUM(-lwinscore) AS RichTotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS RichPlayIn, --玩入
SUM(- (lwinscore   lalladdscore)) AS RichPlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
GROUP BY MONTH(insertTime);
END;
ELSE IF (@Type = 2) --日统计
BEGIN
SELECT @Year Year,
@Month Month,
DAY(insertTime) Day,
SUM(-lwinscore) AS RichTotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS RichPlayIn, --玩入
SUM(- (lwinscore   lalladdscore)) AS RichPlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
GROUP BY DAY(insertTime);
END;
ELSE IF (@Type = 3) --日统计
BEGIN
DECLARE @RecordCount INT;
SELECT @RecordCount = COUNT(*)
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
AND DAY(insertTime) = @Day
GROUP BY dwUserID,
lwinscore,
lalladdscore,
lAreaScore1,
lAreaScore2,
lAreaScore3,
lAreaScore4,
lAreaScore5,
insertTime;
DECLARE @TotalProfitAndLoss VARCHAR(255)
DECLARE @TotalPlayIn VARCHAR(255)
DECLARE @TotalPlayOut VARCHAR(255)
DECLARE @PageCount INT

SELECT 
@PageCount=COUNT(dwUserID),
@TotalProfitAndLoss=SUM(-lwinscore), --总盈亏
@TotalPlayIn=SUM(lalladdscore), --玩入
@TotalPlayOut=SUM(- (lwinscore   lalladdscore)) --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
AND DAY(insertTime) = @Day;
DECLARE @StartIndex INT ,
@EndIndex INT
SELECT @StartIndex = ( @PageIndex - 1 ) * @PageSize   1 ,
@EndIndex = @PageIndex * @PageSize;
WITH Pager
AS ( SELECT ROW_NUMBER() OVER (ORDER BY dwUserID ASC) AS Id,
dwUserID,
@PageCount PageCount,
@TotalProfitAndLoss AS TotalProfitAndLoss, --总盈亏
@TotalPlayIn AS TotalPlayIn, --玩入
@TotalPlayOut AS TotalPlayOut, --玩出
-lwinscore AS RichProfitAndLoss, --总盈亏
lalladdscore AS RichPlayIn, --玩入
- (lwinscore   lalladdscore) AS RichPlayOut, --玩出
lAreaScore1,
lAreaScore2,
lAreaScore3,
lAreaScore4,
lAreaScore5,
insertTime
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
AND DAY(insertTime) = @Day
GROUP BY dwUserID,
lwinscore,
lalladdscore,
lAreaScore1,
lAreaScore2,
lAreaScore3,
lAreaScore4,
lAreaScore5,
insertTime)
SELECT *
FROM Pager
WHERE Id BETWEEN @StartIndex AND @EndIndex

END;
END;

一.概述  

  说到B-tree组织,就是指索引,它可以提供了对数据的快速访问。索引使数据以一种特定的方式组织起来,使查询操作具有最佳性能。当数据表量变得越来越大,索引就变得十分明显,可以利用索引查找快速满足条件的数据行。某些情况还可以利用索引帮助对数据进行排序,组合,分组,筛选。

   一个B-tree,根是唯一的遍历的起点。中间页 层次数是根据表的行数以及索引行的大小而变化。索引中的底层节点称为叶节点。叶节点它容纳了一行或多行具有指定键值的记录,对于聚集或非聚集,叶节点都是按照键值的顺序组成,对于复合索引就是若干键值的组合。

  1.聚集索引

  在聚集索引的叶节点里不仅包含了索引键,还包含了数据页。也就是说数据本身也是聚集索引的一部分。聚集索引基于键值联系使表中的数据有序。决定哪个键值作为聚集键是重要因素,当遍历到叶级别时,可以获取数据本身,而不是简单地得到一个指向数据的指针(非聚集索引数据未覆盖)。聚集索引在 sys.partitions区中有一行,其中,索引使用每个分区的 index_id = 1,默认情况下,聚集索引是单个分区。如果聚集索引有四个分区,就有四个 B-tree 结构,每个分区中有一个 B-tree结构,关于分区在sql server 分区(上)中有讲到。由于数据页链只能按一种方式排序,因此表只有一个聚集索引,一般情况查询优化器非常倾向于采用聚集索引,因为可以直接在叶级别找到数据。  查询优化器也只需要在某一段范围的数据页,进行扫描。聚集索引结构按物理顺序存储不是磁盘上的顺序,聚集索引的排序顺序仅是表数据链在逻辑上有序的。

图片 1

  2.非聚集索引

  非聚集索引与聚集索引有一个相似的 B -tree索引结构。不同的是,非聚集索引不影响数据行的顺序。什么意思呢,就是说非聚集索引,叶级别不包含全部的数据,只包含了键值以及,在每个叶节点中的索引行包含了一个书签(bookmark),书签在聚集索引里就是相应的数据行的聚集索引键,在堆里就是行标识符RID,该书签告诉sql server可以在哪里找到与索引键相应的数据行。
理解了非聚集索引叶节点不包含全部数据时,就知道非聚集索引的存在并不影响数据分页的组织,因此每张表上最多249个非聚集索引。
非聚集索引在 sys.partitions 区中有一行, 非聚集索引标识 index_id >1。默认情况下,一个非聚集索引一个分区。

图片 2

一. 概述

  通常来说,死锁都是应用设计问题,通过调整业务流程,数据库对象设计,事务大小,以及访问数据库的sql语句,绝大部分死锁都可以避免,下面介绍几种避免死锁的常用 方法.
  1. 在应用中,如果不同的程序并发操作多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。按顺序对表进行操作,是很常用的一种避免死锁的操作。 比如:有二个不一样的存储过程,同时在对一个表进行复杂的删改操作。这种情况可以考虑先让一个执行完成,再让另一个在执行。
  2. 在程序中以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。比如常见的就是多线程下在程序中lock锁住,在进程下保持串行处理。
  3. 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排它锁,而不是先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其它事务可能又已经获得了相同记录的共享锁,从而造成锁冲突。 我理解是在事务中首先将要更新的记录,以select .. for update方式获得排它锁, 在事务里处理完逻辑后就可以直接更新而不用考虑锁冲突。 代码如下:

SET autocommit=0
-- 将要更新的数据先获得排它锁
SELECT * FROM city WHERE city_id=103 FOR UPDATE;
-- 逻辑处理  ....
-- 最后更新可以避免锁冲突
UPDATE city SET cityname='杭州' WHERE city_id=103;
COMMIT;

  4. 在默认级别Repeatable read下, 如果两个线程同时对相同条件记录用 select .. for update 加排它锁,在没有符合该条件记录情况下,两个线程都会加锁成功。当一个程序发现记录不存在,就试图插入一条新数据,如果两个线程都这么做,就会出现死锁。这是因为在Repeatable read下产生了间隙锁。这种情况下,将隔离级别改成Read commited,就可避免问题 如下图表格 贴出了二个隔离级别下产生锁的差异。

图片 3

  5. 当在Repeatable read下,如果两个线程都先执行select .. for update。 在判断是否存在符合条件的记录,如果没有,就插入记录,此时,只有一个线程能插入成功,另一个线程会出现锁等待, 当第1个线程提交后,第2个线程如因为主键值重复,会出现异常。但却获得了一个排它锁, 需要执行rollback释放排它锁。避免影响其它事务。
  总结:尽管通过上面介绍和sql 优化等措施,可以大大减少死锁,但死锁很难完全避免。因此。 在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。在程序异常里或commit或rollback。

本文由ca88手机版登录发布于亚洲城ca88手机版官网,转载请注明出处:统计分页一些sql,避免死锁

TAG标签: ca88手机版登录
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。