插入和更新一次交易时，SQL Server僵局

Question

我正在使用Azure SQL Java Spring Boot2。当前我正在尝试了解发生僵局的原因。在交易中，我要进行插入，然后在同一表上更新，但行不同。据我了解，默认情况下，SQL Server使用rowlock和read_committed隔离。

这是僵局的详细信息：

<deadlock>
    <victim-list>
        <victimProcess id="process2b8618644e8"/>
    </victim-list>
    <process-list>
        <process id="process2b8618644e8" taskpriority="0" logused="352"
                 waitresource="KEY: 16:72057594043760640 (5f601e0257f1)" waittime="1321" ownerId="9246067"
                 transactionname="implicit_transaction" lasttranstarted="2019-02-17T13:03:49.683" XDES="0x2b868778458"
                 lockMode="U" schedulerid="2" kpid="288932" status="suspended" spid="118" sbid="0" ecid="0" priority="0"
                 trancount="2" lastbatchstarted="2019-02-17T13:03:49.793" lastbatchcompleted="2019-02-17T13:03:49.760"
                 lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft JDBC Driver for SQL Server"
                 hostname="TZ-MacBook-Pro.local" hostpid="0" loginname="master" isolationlevel="read committed (2)"
                 xactid="9246067" currentdb="16" currentdbname="test" lockTimeout="4294967295" clientoption1="671088672"
                 clientoption2="128058">
            <executionStack>
                <frame procname="unknown" queryhash="0x431f2517c2d3feb8" queryplanhash="0x3a793ad664472011" line="1"
                       stmtstart="110" stmtend="238"
                       sqlhandle="0x02000000060c530e608cef3b3e4e4712f48c40d2efd9b04d0000000000000000000000000000000000000000">
                    unknown
                </frame>
                <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1"
                       sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 int)update bug set lock=@P0, status=@P1 where
                bug_id=@P2 and lock=@P3
            </inputbuf>
        </process>
        <process id="process2b84f702108" taskpriority="0" logused="700"
                 waitresource="KEY: 16:72057594043760640 (185e9bfc7320)" waittime="1287" ownerId="9246090"
                 transactionname="implicit_transaction" lasttranstarted="2019-02-17T13:03:49.727" XDES="0x2b868764458"
                 lockMode="U" schedulerid="1" kpid="334812" status="suspended" spid="115" sbid="0" ecid="0" priority="0"
                 trancount="2" lastbatchstarted="2019-02-17T13:03:49.830" lastbatchcompleted="2019-02-17T13:03:49.790"
                 lastattention="1900-01-01T00:00:00.790" clientapp="Microsoft JDBC Driver for SQL Server"
                 hostname="TZ-MacBook-Pro.local" hostpid="0" loginname="master" isolationlevel="read committed (2)"
                 xactid="9246090" currentdb="16" currentdbname="test" lockTimeout="4294967295" clientoption1="671088672"
                 clientoption2="128058">
            <executionStack>
                <frame procname="unknown" queryhash="0x431f2517c2d3feb8" queryplanhash="0x3a793ad664472011" line="1"
                       stmtstart="110" stmtend="238"
                       sqlhandle="0x02000000060c530e608cef3b3e4e4712f48c40d2efd9b04d0000000000000000000000000000000000000000">
                    unknown
                </frame>
                <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1"
                       sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 int)update bug set lock=@P0, status=@P1 where
                bug_id=@P2 and lock=@P3
            </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594043760640" dbid="16" objectname="b51ce3c4-02f0-4dfa-89dd-3ab2f45beac7.dbo.bug"
                 indexname="bug_pk" id="lock2b8658c6f00" mode="X" associatedObjectId="72057594043760640">
            <owner-list>
                <owner id="process2b84f702108" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="process2b8618644e8" mode="U" requestType="wait"/>
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594043760640" dbid="16" objectname="b51ce3c4-02f0-4dfa-89dd-3ab2f45beac7.dbo.bug"
                 indexname="bug_pk" id="lock2b856aa7f80" mode="X" associatedObjectId="72057594043760640">
            <owner-list>
                <owner id="process2b8618644e8" mode="X"/>
            </owner-list>
            <waiter-list>
                <waiter id="process2b84f702108" mode="U" requestType="wait"/>
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

另外，我在我的ID列上使用了群集索引，并且随机操作顺序没有问题。此外，当我只插入交易中时，一切都可以正常工作，而无需僵局。如果我切换到PostgreSQL，则没有僵局问题。

所以为什么我在Azure SQL中陷入僵局？

这是Java源代码：

    @Transactional
    public void bug() {
        BugEntity bugEntity = bugRepo.findById("1234")
                .orElseGet(() -> createBugEntity());
        if (bugEntity.getStatus().equals("SomeStatus")) {
            bugEntity.setStatus("NewStatus");
            bugRepo.save(bugEntity);
        }
    }
    private BugEntity createBugEntity() {
        BugEntity bugEntity = BugEntity.builder()
                .bugId(UUID.randomUUID().toString())
                .status("SomeStatus")
                .data("SomeData")
                .build();
        return bugRepo.save(bugEntity);
    }

实体类：

@AllArgsConstructor
@NoArgsConstructor
@Data
@Builder
@Entity(name = "bug")
@DynamicUpdate
public class BugEntity {
    @Id
    String bugId;
    String status;
    String data;
    LocalDateTime dateTime;
    @Version
    int lock;

}

要重现僵局，我正在使用一些并行请求使用加特林模拟。

Answer 1

僵局的常见原因比需要查询和索引调整的查询触及了更多的数据。

死锁跟踪显示参数正在传递为nvarchar(4000)数据类型。这可能会防止varchar列上的索引有效地使用，因为nvarchar具有比varchar更高的数据类型优先级。当参数数据类型从应用程序数据类型推断时，此问题尤其常见，因为字符串通常是Unicode（例如Java和.net语言），无论基础列数据类型如何，都会导致nvarchar参数。

。

除非基础列为 nvarchar，否则解决方案是将VARCHAR参数用于字符串而不是NVarchar。如果您在数据库中不使用nvarchar，请在本答案中详细介绍JDBC连接字符串参数sendStringParametersAsUnicode。另外，将应用程序代码或框架配置更改为使用varchar参数类型的varchar列。

最好的做法是确保参数匹配基础数据库列的类型。这将提高性能和并发性，并具有其他好处。