涉及IMAGE或TEXT列的死锁期间的怪异现象 [英] weird phenomena during deadlocks involving IMAGE or TEXT columns

问题描述

这是我在使用Sybase ASE 15.7对应用程序进行压力测试时偶然发现的事情.

This is something very disturbing I stumbled upon while stress-testing an application using Sybase ASE 15.7.

我们有下表:

CREATE TABLE foo
(
    i INT NOT NULL,
    blob  IMAGE    
);
ALTER TABLE foo ADD PRIMARY KEY (i);

即使在开始测试之前，该表也具有单个行，其中IMAGE列中包含一些数据.在测试期间，否行将被删除或插入.因此，该表始终只包含一行.列 blob 仅(在下面的事务 T1 中)更新为某个值(不是NULL).

The table has, even before starting the test, a single row with some data in the IMAGE column. No rows are either deleted or inserted during the test. So the table always contains a single row. Column blob is only updated (in transaction T1 below) to some value (not NULL).

然后，我们进行以下两项交易:

Then, we have the following two transactions:

T1: UPDATE foo SET blob=<some not null value> WHERE i=1
T2: SELECT * FROM foo WHERE i=1

由于某种原因，上述事务可能会在负载下死锁(大约10个线程在一个循环中执行 T1 20次，另外10个线程在循环中执行 T2 20次) ).

For some reason, the above transactions may deadlock under load (approx. 10 threads doing T1 20 times in a loop and another 10 threads doing T2 20 times in loop).

这已经很奇怪了，但是还有更多.总是选择 T1 作为死锁的受害者.因此，在发生死锁(错误代码1205)的情况下，应用程序逻辑只需重试 T1 .这应该起作用，并且通常应该是故事的结尾.但是…

This is already weird enough, but there's more to come. T1 is always chosen as the deadlock victim. So, the application logic, on the event of a deadlock (error code 1205) simply retries T1. This should work and should normally be the end of the story. However …

…有时候 T2 会检索到其中 blob 列的值为NULL的行！即使表已经从一行开始，并且更新仅将先前的值(非 NULL )重置为其他值(非 NULL )也是如此.每次测试都可以100％重现.

… it happens that sometimes T2 will retrieve a row in which the value of the blob column is NULL! This is even though the table already starts with a row and the updates simply reset the previous (non-NULL) value to some other (non-NULL) value. This is 100% reproducible in every test run.

在READ COMMITTED序列化级别中可以观察到此情况.

This is observed with the READ COMMITTED serialization level.

我验证了 TEXT 列类型也发生了上述现象，但 VARCHAR 却没有.

I verified that the above behavior also occurs with the TEXT column type but not with VARCHAR.

我还验证了在事务 T1 中获得对表 foo 的排他锁可以使问题消除.

I've also verified that obtaining an exlusive lock on table foo in transaction T1 makes the issue go away.

所以我想了解从根本上打破事务隔离的事物怎么可能?实际上，我认为这比事务隔离更糟糕，因为 T1 从未将 blob 列的值设置为 NULL .

So I'd like to understand how can something that so fundamentally breaks transaction isolation be even possible? In fact, I think this is worse than transaction isolation as T1 never sets the value of the blob column to NULL.

<罢工> 测试代码是使用 jconn4.jar 驱动程序(类 com.sybase.jdbc4.jdbc.SybDriver )以Java编写的，因此我不排除可能是JDBC驱动程序错误.

The test code is written in Java using the jconn4.jar driver (class com.sybase.jdbc4.jdbc.SybDriver) so I don't rule out that this may be a JDBC driver bug.

只需使用 isql 并并行生成多个shell并连续执行 T1 即可，这是可重现的.因此，我删除了 Java 和 JDBC 标记，因为这肯定与服务器相关.

This is reproducible simply using isql and spawning several shells in parallel that continuously execute T1 in a loop. So I am removing the Java and JDBC tags as this is definitely server-related.

推荐答案

我们已经看到了隔离级别为1的各种奇怪的东西.我的印象是，当T2进行时，T1可以更改数据，而T2可能会返回T1的中间结果.

We've seen all kinds of weird stuff with isolation level 1. I'm under the impression that when T2 is in progress, T1 can change data and T2 might return intermediate result of T1.

尝试隔离级别2，看看是否有帮助(对我们有用).

Try isolation level 2 and see if it helps (does for us).

这篇关于涉及IMAGE或TEXT列的死锁期间的怪异现象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！