Skip to Content
0

blocking of rows of others processes

Feb 16, 2017 at 03:42 PM

67

avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Mark A Parsons Feb 16, 2017 at 04:02 PM
0

I'd start by suggesting you post the following in a *.txt attachment for better readability:

- output from running sp_help test

- snapshot of the blocking/blocked locks (sp_lock or monLocks) to start; when you're experiencing the problem [ultimately trying to find out if blocking on data or index pages/rows]

- output from sp_who showing the blocked spids and their commands [curious to see if blocking on inserts, updates, deletes or a mix]

Show 1 Share
10 |10000 characters needed characters left characters exceeded

responded below in the text

0
Artem Maystrenko Feb 17, 2017 at 11:12 AM
0

lock.txt - it's file with rezult lock monitoring

lockmonitor.txt -it's file with script monitorin locks

test.txt - sp_help table test(sorry but i can't use xls format in Questions & Answers)

About sp_who, I know statement(because this information in my lock monitoring) and line. And I know batch.

update test

set Date = '12122017'

from test ao (INDEX In_test)

inner join test10 s (INDEX Xtest10)

on s.AuditID = ao.AuditID

where ao.SPID = @@spid

and ao.AutoID between @PrevMaxAutoID and @CurrMaxAutoID


This request and request written earlier in my post


lock.txt (6.4 kB)
lockmonitor.txt (2.7 kB)
test.txt (2.4 kB)
Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Feb 18, 2017 at 04:56 PM
0

Not sure where to start with this one ...

- your original post mentions blocking between 2 commands ... 1) INSERT to pADM_SM_CurrentState select from test2 ... 2) UPDATE test; you've provided no information on the relationship between these 2 commands or the referenced tables; I can't see how either of these commands could block the other unless either a) you've provided the wrong queries, b) you've edited the queries and made some typos or c) there are triggers and/or RI constraints involved

In your latest post ...

- you mention the blocking process is running an UPDATE, but your monitoring script shows sysprocesses.cmd = INSERT, while monProcessSQLText is showing a DELETE; which is it ... UPDATE? INSERT? DELETE? while I might be able to come up with some sort of explanation for the discrepancy in commands, I'm guessing that either a) you've got some cut-n-paste/edit mistakes or b) you need to explain the relationship between all of these commands and the referenced objects (1 proc, 3 tables) ??

- you've mentioned an issue with a table named test but the monitoring output shows objects named Audit (proc?), test (table) and test3 (table), while the monProcessSQLText references an object named test2 (table? view?); again, need a detailed explanation of the relationship between these various objects

- you've provided no (usable) info on the locks causing the blocking, in particular ... are we looking at a row-, page- or table-level (exclusive) lock?

- you've provided the text of a UPDATE statement (2-way join between test and test10), but monProcessSQLtext is showing a DELETE from test2; again, why the discrepancy in commands and objects? what's the relationship between test, test2 and (yet another object) test10?

---------------------

At this point I can't make heads or tails out of what you've provided ... what appears to be discrepancies in commands being issued ... at leasat 6 different referenced objects with no idea of the relationship between said objects... no clear picture of the process flow (eg, commands? triggers? RI constraint checks?)

In addition to more details on all of the objects, triggers, RI constraints, command/process flow, (blocking) lock details ... additional info that would likely be needed:

- SQL text being executed by all of the blocked processes; to include some idea of the numbers of rows that should be affected, and the total number of rows in the table at the time the commands are being blocked

- query plan of the blocking query (probably wouldn't hurt to grab a copy of the query plans for the blocked processes, too)

Share
10 |10000 characters needed characters left characters exceeded