1. <small id='kgMFq'></small><noframes id='kgMFq'>

    <i id='kgMFq'><tr id='kgMFq'><dt id='kgMFq'><q id='kgMFq'><span id='kgMFq'><b id='kgMFq'><form id='kgMFq'><ins id='kgMFq'></ins><ul id='kgMFq'></ul><sub id='kgMFq'></sub></form><legend id='kgMFq'></legend><bdo id='kgMFq'><pre id='kgMFq'><center id='kgMFq'></center></pre></bdo></b><th id='kgMFq'></th></span></q></dt></tr></i><div id='kgMFq'><tfoot id='kgMFq'></tfoot><dl id='kgMFq'><fieldset id='kgMFq'></fieldset></dl></div>

    1. <tfoot id='kgMFq'></tfoot>
        <bdo id='kgMFq'></bdo><ul id='kgMFq'></ul>
      <legend id='kgMFq'><style id='kgMFq'><dir id='kgMFq'><q id='kgMFq'></q></dir></style></legend>

      仅在不存在的情况下插入一行

      Only inserting a row if it#39;s not already there(仅在不存在的情况下插入一行)
      <legend id='wGVG4'><style id='wGVG4'><dir id='wGVG4'><q id='wGVG4'></q></dir></style></legend>
      • <bdo id='wGVG4'></bdo><ul id='wGVG4'></ul>

          <i id='wGVG4'><tr id='wGVG4'><dt id='wGVG4'><q id='wGVG4'><span id='wGVG4'><b id='wGVG4'><form id='wGVG4'><ins id='wGVG4'></ins><ul id='wGVG4'></ul><sub id='wGVG4'></sub></form><legend id='wGVG4'></legend><bdo id='wGVG4'><pre id='wGVG4'><center id='wGVG4'></center></pre></bdo></b><th id='wGVG4'></th></span></q></dt></tr></i><div id='wGVG4'><tfoot id='wGVG4'></tfoot><dl id='wGVG4'><fieldset id='wGVG4'></fieldset></dl></div>

              <tfoot id='wGVG4'></tfoot>

              <small id='wGVG4'></small><noframes id='wGVG4'>

                  <tbody id='wGVG4'></tbody>

                本文介绍了仅在不存在的情况下插入一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                我一直使用类似于以下的东西来实现它:

                I had always used something similar to the following to achieve it:

                INSERT INTO TheTable
                SELECT
                    @primaryKey,
                    @value1,
                    @value2
                WHERE
                    NOT EXISTS
                    (SELECT
                        NULL
                    FROM
                        TheTable
                    WHERE
                        PrimaryKey = @primaryKey)
                

                ...但是一旦在负载下,就会发生主键冲突.这是唯一插入到该表中的语句.那么这是否意味着上面的语句不是原子的?

                ...but once under load, a primary key violation occurred. This is the only statement which inserts into this table at all. So does this mean that the above statement is not atomic?

                问题是这几乎不可能随意重新创建.

                The problem is that this is almost impossible to recreate at will.

                也许我可以将其更改为以下内容:

                Perhaps I could change it to the something like the following:

                INSERT INTO TheTable
                WITH
                    (HOLDLOCK,
                    UPDLOCK,
                    ROWLOCK)
                SELECT
                    @primaryKey,
                    @value1,
                    @value2
                WHERE
                    NOT EXISTS
                    (SELECT
                        NULL
                    FROM
                        TheTable
                    WITH
                        (HOLDLOCK,
                        UPDLOCK,
                        ROWLOCK)
                    WHERE
                        PrimaryKey = @primaryKey)
                

                虽然,也许我使用了错误的锁或使用了过多的锁或其他什么.

                Although, maybe I'm using the wrong locks or using too much locking or something.

                我在 stackoverflow.com 上看到了其他问题,其中的答案暗示了IF (SELECT COUNT(*) ... INSERT"等),但我总是假设(可能不正确)假设单个 SQL 语句将是原子的.

                I have seen other questions on stackoverflow.com where answers are suggesting a "IF (SELECT COUNT(*) ... INSERT" etc., but I was always under the (perhaps incorrect) assumption that a single SQL statement would be atomic.

                有人有什么想法吗?

                推荐答案

                "JFDI"模式?

                BEGIN TRY
                   INSERT etc
                END TRY
                BEGIN CATCH
                    IF ERROR_NUMBER() <> 2627
                      RAISERROR etc
                END CATCH
                

                说真的,这是最快和最并发的,没有锁,尤其是在高容量的情况下.如果UPDLOCK被上报,整个表都被锁了怎么办?

                Seriously, this is quickest and the most concurrent without locks, especially at high volumes. What if the UPDLOCK is escalated and the whole table is locked?

                阅读第 4 课:

                第 4 课:在调整索引之前开发 upsert 过程时,我首先相信 If Exists(Select...) 行会为任何项目触发,并且禁止重复.纳达.在短时间内有数千个重复项,因为同一个项目会在同一毫秒内命中 upsert,并且两个事务都会看到不存在并执行插入.经过多次测试,解决方案是使用唯一索引,捕获错误,然后重试允许事务查看该行并执行更新而不是插入.

                Lesson 4: When developing the upsert proc prior to tuning the indexes, I first trusted that the If Exists(Select…) line would fire for any item and would prohibit duplicates. Nada. In a short time there were thousands of duplicates because the same item would hit the upsert at the same millisecond and both transactions would see a not exists and perform the insert. After much testing the solution was to use the unique index, catch the error, and retry allowing the transaction to see the row and perform an update instead an insert.

                这篇关于仅在不存在的情况下插入一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                Select n random rows from SQL Server table(从 SQL Server 表中随机选择 n 行)
                SQL query to select dates between two dates(用于选择两个日期之间的日期的 SQL 查询)
                How can I delete using INNER JOIN with SQL Server?(如何在 SQL Server 中使用 INNER JOIN 进行删除?)
                Table Naming Dilemma: Singular vs. Plural Names(表命名困境:单数与复数名称)
                INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server(INSERT 语句与 FOREIGN KEY 约束冲突 - SQL Server)
                How ROWNUM works in pagination query?(ROWNUM 如何在分页查询中工作?)
              • <tfoot id='frjdU'></tfoot>
              • <i id='frjdU'><tr id='frjdU'><dt id='frjdU'><q id='frjdU'><span id='frjdU'><b id='frjdU'><form id='frjdU'><ins id='frjdU'></ins><ul id='frjdU'></ul><sub id='frjdU'></sub></form><legend id='frjdU'></legend><bdo id='frjdU'><pre id='frjdU'><center id='frjdU'></center></pre></bdo></b><th id='frjdU'></th></span></q></dt></tr></i><div id='frjdU'><tfoot id='frjdU'></tfoot><dl id='frjdU'><fieldset id='frjdU'></fieldset></dl></div>
                  <legend id='frjdU'><style id='frjdU'><dir id='frjdU'><q id='frjdU'></q></dir></style></legend>
                1. <small id='frjdU'></small><noframes id='frjdU'>

                    <bdo id='frjdU'></bdo><ul id='frjdU'></ul>

                        <tbody id='frjdU'></tbody>