MS SQL Server question

[vc]

Recently, a developer brought to my attention a problem that I once saw during my Sybase days.

Assuming the default isolation READ COMMITTED:

Code: Select all

create table t1(id int, val int)
insert into t1(1,10)
insert into t1(2,20)
insert into t1(3,30)

The table 't1' contains three accounts (id) with some money(val).

Now in Session 1

Code: Select all

isql -U vc -P vc
1> SET IMPLICIT_TRANSACTIONS ON

1> select * from t1
2> go
 id          val
 ----------- -----------
           1          10
           2          20
           3          30

(3 rows affected)

Now take money from Account 3:
1> update t1 set val=val-30 where id=3
2> go
(1 row affected)

...and in Session 2:

Code: Select all

1> SET IMPLICIT_TRANSACTIONS ON 

1> select sum(val) from t1 where id <=3
2> go
The session is blocked by Session 1's update

... and in Session 1:

Code: Select all

...transfer money to Account 1:
1> update t1 set val=val+30 where id=1
2> go
(1 row affected)
1> commit
2> go
1>

... and in Session 2

Code: Select all

Session is unblocked

 -----------
          30

(1 row affected)
1>

and gives an incorrect answer (should be 60)

I understand what's going on but the question is whether it's possible to fix the problem under SQL Server 2K without changing the code, or the locking works pretty much in the same way as it did under Sybase (version 10) ? Changing to SERIALIZABLE is not an option either.

The original task is about moving network devices between hubs but it can be expressed in terms of money transfer between accounts.

Thanks.

Rgds.

[tengiz]

The locking protocol used here (likewise in any reasonably implemented locking scheduler) is precisely what ANSI READ COMMITTED assumes, so there is no 'fix' per se. It's the standard behavior. ANSI's READ COMMITTED is in fact weaker isolation level than the one of ORACLE's.

To correct the problem without changing the code one should use an appropriate isolation level, which is REPEATABLE READ in this particular case. SERIALIZABLE would be an overkill indeed. You could either issue SET TRANSACTION ISOLATION LEVEL REPEATABLE READ before running the query in the session which reads data or change the default database transaction isolation level to REPEATABLE READ. The latter, obviosuly, would affect some other sessions connected to the database, a namely those which don't explicitly set the isolation level. As of the code change, even though it's not an option, it's rather trivial - the query which reads the summary could use a locking hint.

[vc]

... As of the code change, even though it's not an option, it's rather trivial - the query which reads the summary could use a locking hint.

tengiz,


Thank you.

I understand that the locking hint means locking the entire table before running the aggregate query ?

Rgds.

[vc]

....

To correct the problem without changing the code one should use an appropriate isolation level, which is REPEATABLE READ in this particular case. SERIALIZABLE would be an overkill indeed. You could either issue SET TRANSACTION ISOLATION LEVEL REPEATABLE READ before running the query in the session which reads data or change the default database transaction isolation level to REPEATABLE READ. The latter, obviosuly, would affect some other sessions connected to the database, a namely those which don't explicitly set the isolation level. As of the code change, even though it's not an option, it's rather trivial - the query which reads the summary could use a locking hint.

tengiz,

As I suspected, under the RR level, I get a classical dead-lock situation since the sum query leaves behind shared read locks on each row (I used the very same sequence as above):

1> select sum(val) from t1 where id <=3
2> go
Msg 1205, Level 13, State 50, Server VJC, Line 1
Transaction (Process ID 51) was deadlocked on {lock} resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
1>

I suppose the only choice is to lock the entire table. Is it the usual approach (locking the entire table) with aggregates or I am missing something ?

Thank you.

Rgds.

[tengiz]

In this particular case ideally you should first try to change the order in which the transactions access the data: say, in the transaction which moves funds you always touch first the account which number comes first. Or you can specify an explicit index hint for the query with aggregation thus ensuring a desired order of access to the accounts. With no particular access pattern in the application there's always a fat change to encounter a deadlock.

As of the hint I was talking about in the previous message - it's either HOLDLOCK or a combination of HODLOCK and UPDLOCK. A TABLOCK hint is usually the last resort, and typically one only needs it in some peculiar or exotic cases.

[vc]

tengiz,

Thank you.

In this particular case ideally you should first try to change the order in which the transactions access the data: say, in the transaction which moves funds you always touch first the account which number comes first. Or you can specify an explicit index hint for the query with aggregation thus ensuring a desired order of access to the accounts. With no particular access pattern in the application there's always a fat change to encounter a deadlock.

As of the hint I was talking about in the previous message - it's either HOLDLOCK or a combination of HODLOCK and UPDLOCK. A TABLOCK hint is usually the last resort, and typically one only needs it in some peculiar or exotic cases.

Since the topology update order is dictated by some other considerations and the moment when the customer decides to issue an aggregate query is unpredictable, the hints seem to be the way to go...

Rgds.