Skip to Content
0
Feb 16, 2017 at 03:42 PM

blocking of rows of others processes

122 Views

Hi!

Periodic I have problems with locks on the user tables, by multi-user operation.

Test case following:

ASE 15.7 Sp 135 HF1

I have a table: (An output from sp_help)

test dbo user table -- none -- SPID DSSPID 8 15 0 0 ZERO [NULL] [NULL] [NULL] 0 AuditID DSIDENTIFIER 8 15 0 0 ZERO [NULL] [NULL] [NULL] 0 Date DSOPERDAY 4 NULL NULL 0 ZERO_DATE [NULL] [NULL] [NULL] 0 AutoID DSIDENTIFIER 8 15 0 0 ZERO [NULL] [NULL] [NULL] 1 Object has the following indexes In_test SPID, AutoID nonclustered, unique 0 0 0 Dec 25 2016 10:22AM Global Index Lock scheme Datarows The 'ascinserts' attribute is not applicable to tables with datarow or datapage lock schemes.

Description of the user data types:

Type_name Storage_type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity

1 DSSPID numeric 8 15 0 0 ZERO [NULL] [NULL] 0

Type_name Storage_type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity

1 DSIDENTIFIER numeric 8 15 0 0 ZERO [NULL] [NULL] 0

Type_name Storage_type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity

1 DSOPERDAY smalldatetime 4 NULL NULL 0 ZERO_DATE [NULL] [NULL] 0

There are many processes which use this table. Because of the operation execution frequency where this table is used.

Sometimes there is a process which in transaction executed many operations insert, update, delete over the table «test».

On a large number of data (100 000 insertions).

After he already performed operations on operation with the specified table, but didn't finish operation and didn't close transaction, we receive locks of operation with this table on other users.

At the same time, each process works with the field value SPID (system spid), and considering that we have an In_test index, everyone works with the row.

There are no scannings of an index, there are also no escalation of locks to table.

Locks exactly at the level of row. (it is visible on system to tables of locks).

It isn't clear how, the process working on unique index and with Lock scheme Datarows blocked data row from other processes.( another spid)

As for example insert in the table approximately such:

insert pADM_SM_CurrentState

(

SPID ,

AuditID,

Date,

AutoID

)

select @@spid,

o.ID,

o.InstrumentID,

p.Date

from test2 ao

where ao.SPID = @@spid

and ao.ObjectID between @CurrentMinID and @CurrentMaxID

Can blocked operation of process of update with another system spid:

update test

set AuditID = 1

from test ao (INDEX In_test)

where ao.SPID = @@spid

and ao.AutoID between @PrevMaxAutoID and @CurrMaxAutoID

For resolve, we tried the following options:

1.

alter table test partition 16

go

2.

alter table test

add not_ins_lock_col char(6000) DEFAULT '' NOT NULL

go

sp_chgattribute 'test, 'max_rows_per_page', 1

go

But helps not always.

Perhaps someone faced a similar problem and knows as to solve it?