Skip to main content

MSS deadlock mitigation/identification

  • August 17, 2021
  • 8 replies
  • 0 views
Iain Sharp
Forum|alt.badge.img+5

We are encountering a slew of issues with one customer, whose system tends towards large transactions featuring a small range of SKU records, where the transaction is encountering a record deadlock, and being rolled back by sql server. These deadlocks are (to my mind) legitimate (in that one transaction is updating SKU A then SKU B, and the other one is trying to do B then A). 

According to the documentation 

Normally, Uniface sets $status to -10 if a user tries to modify and store a record which has been locked by another user, but because Microsoft SQL Server does not return a status in this situation, Uniface is unable to do this.

Although the userver log file does show a message from sql server about the transaction being killed. 

If I can spot that this has happened before the commit, I can execute a rollback and either automatically or manually redo the transaction. (It's a client/server situation, the server encounters the error). 

Does anyone have a trick or workaround which would enable Uniface proc to spot this very dangerous effect (my business logic integrity is severely compromised by it (for example) updating a delivery note without removing the stock....)


I have thought that I might be able to query the sql server process id at the beginning of the transaction, and the point of commit, and if they are different, then rollback instead. Would this be right? Would it get a different sql server process id at that point? 

If our users can't trust the stock figures from our system, they can't trust the system and won't be our users very long, so it is vital I can come up with something to spot these things as they happen.  

Iain Sharp : Head of Technical Services, Jonas Metals Software

    8 replies

    Gianni Sandigliano
    Forum|alt.badge.img

    We are encountering a slew of issues with one customer, whose system tends towards large transactions featuring a small range of SKU records, where the transaction is encountering a record deadlock, and being rolled back by sql server. These deadlocks are (to my mind) legitimate (in that one transaction is updating SKU A then SKU B, and the other one is trying to do B then A). 

    According to the documentation 

    Normally, Uniface sets $status to -10 if a user tries to modify and store a record which has been locked by another user, but because Microsoft SQL Server does not return a status in this situation, Uniface is unable to do this.

    Although the userver log file does show a message from sql server about the transaction being killed. 

    If I can spot that this has happened before the commit, I can execute a rollback and either automatically or manually redo the transaction. (It's a client/server situation, the server encounters the error). 

    Does anyone have a trick or workaround which would enable Uniface proc to spot this very dangerous effect (my business logic integrity is severely compromised by it (for example) updating a delivery note without removing the stock....)


    I have thought that I might be able to query the sql server process id at the beginning of the transaction, and the point of commit, and if they are different, then rollback instead. Would this be right? Would it get a different sql server process id at that point? 

    If our users can't trust the stock figures from our system, they can't trust the system and won't be our users very long, so it is vital I can come up with something to spot these things as they happen.  

    Hi Iain,

    I feel some more details are needed to let the discussion flow because locking applied on the field from a Uniface application is defined from both application choices as well as (R)DBMS configuration choices:

    1) which locking model is your application aiming for?

    Uniface is proposing as default CAUTIOUS locking on all entities defined into Application Model, but developer could define entities as OPTIMISTIC or go for mixed choices like most tables on CAUTIOUS but few on OPTIMISTIC.

    2) which is your current MSS driver/connection configuration?

    On every application model table locking scenarios possible defined at#1, the applied MSS driver configuration using USYS$MSS_PARAMS could change answers received from (R)DBMS; parameters which address the application real lock scenario are:
    - lock isolation : [value] with possible values: rc=readCommitted, ru=readUncommitted, rr=repeatableRead, sl=serializable, ss=statementSnapshot, ts=transactionSnapshot. Default is rc.
    - lock granularity : [value] with possible values: page, table, row. Default should be row.
    - lock timeout : [value] with possible values: 0=infinite or N=seconds, default 10.
    - maxlockwait : [value] with possiblle values infinite or X=seconds,

    3) Is your application using stored procedures or only SQL?

    Again into USYS$MSS_PARAMS: procedures : on | off. default being on.

    4) Is your application working with a single (R)DBMS connection or with multiple (R)DBMS connections?

    When multiple connections: all connection are read/write mode same time meaning transaction(s) over multiple (R)DBMS or a single connection is "de facto" where all transactions are managed being others read only?

    Regards,
    Gianni

    P.S. My gotfeel is there are very few possibilities to forecast a deadlock coming; with some combinations of configuration is really impossible with other some tricks could probably be applied analyzing tables reporting current locks, but I do not think with success 100%...
    A good reading on the subject could be THIS.

    PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!
    Iain Sharp
    Forum|alt.badge.img+5
    • Iain Sharp
    • Author
    • Inspiring
    • August 18, 2021

    Hi Iain,

    I feel some more details are needed to let the discussion flow because locking applied on the field from a Uniface application is defined from both application choices as well as (R)DBMS configuration choices:

    1) which locking model is your application aiming for?

    Uniface is proposing as default CAUTIOUS locking on all entities defined into Application Model, but developer could define entities as OPTIMISTIC or go for mixed choices like most tables on CAUTIOUS but few on OPTIMISTIC.

    2) which is your current MSS driver/connection configuration?

    On every application model table locking scenarios possible defined at#1, the applied MSS driver configuration using USYS$MSS_PARAMS could change answers received from (R)DBMS; parameters which address the application real lock scenario are:
    - lock isolation : [value] with possible values: rc=readCommitted, ru=readUncommitted, rr=repeatableRead, sl=serializable, ss=statementSnapshot, ts=transactionSnapshot. Default is rc.
    - lock granularity : [value] with possible values: page, table, row. Default should be row.
    - lock timeout : [value] with possible values: 0=infinite or N=seconds, default 10.
    - maxlockwait : [value] with possiblle values infinite or X=seconds,

    3) Is your application using stored procedures or only SQL?

    Again into USYS$MSS_PARAMS: procedures : on | off. default being on.

    4) Is your application working with a single (R)DBMS connection or with multiple (R)DBMS connections?

    When multiple connections: all connection are read/write mode same time meaning transaction(s) over multiple (R)DBMS or a single connection is "de facto" where all transactions are managed being others read only?

    Regards,
    Gianni

    P.S. My gotfeel is there are very few possibilities to forecast a deadlock coming; with some combinations of configuration is really impossible with other some tricks could probably be applied analyzing tables reporting current locks, but I do not think with success 100%...
    A good reading on the subject could be THIS.

    So, it's set for cautious locking in the main. (Some of the entities in the model are views, and are set to No Updates.)


    Mss driver config is currently

    USYS$MSS_PARAMS ri: uniface, procs: off, ids:quoted, hold statements:on,mlw:300, locktime:300,gran:row, stmtcache:off,iso:ru,os:96, sql92npw=on, mars:on, step:0

    BUt we have been using. 

    USYS$MSS_PARAMS ri: uniface, procs: off, ids:quoted, hold statements:on,mlw:infinite, locktime:infinite,gran:row, stmtcache:off,iso:ru,os:96, sql92npw=on, mars:on, step:0


    The system is entirely client server, so (almost) all updates are done by the userver processes, and therefore lock timeouts cannot be universally fed back to the user without a lot of coding, We'd prefer not to have locks time out, but just to wait and get on with the job when they can. 

    The data is all in one database, using one path ($DEF), some specific services are started "TRANSACTION=TRUE", but they are small, single record updates, so they can't cause deadlocks. 

    I have started a SQL server deadlock trace event log I don't entirely understand, but it shows me one from today, which (it appears) is the update of an indexed view in the database. We use indexed views because uniface's handling of (most) views appears to be slow ( the filter criteria are applied after the view is implemented, so SQL works out 1000's of answers to return one. 

    However, this is not the only place in the system the deadlocks occur, so it's probable other things also cause it. 

    If I can spot that it's occurred somehow, I can build that into my important transactions to ensure uniface rolls back and (potentially) retries the transaction when not all of it has been properly updated, but How do I spot them? 

    Iain Sharp : Head of Technical Services, Jonas Metals Software
    Gianni Sandigliano
    Forum|alt.badge.img

    Hi Iain,

    I feel some more details are needed to let the discussion flow because locking applied on the field from a Uniface application is defined from both application choices as well as (R)DBMS configuration choices:

    1) which locking model is your application aiming for?

    Uniface is proposing as default CAUTIOUS locking on all entities defined into Application Model, but developer could define entities as OPTIMISTIC or go for mixed choices like most tables on CAUTIOUS but few on OPTIMISTIC.

    2) which is your current MSS driver/connection configuration?

    On every application model table locking scenarios possible defined at#1, the applied MSS driver configuration using USYS$MSS_PARAMS could change answers received from (R)DBMS; parameters which address the application real lock scenario are:
    - lock isolation : [value] with possible values: rc=readCommitted, ru=readUncommitted, rr=repeatableRead, sl=serializable, ss=statementSnapshot, ts=transactionSnapshot. Default is rc.
    - lock granularity : [value] with possible values: page, table, row. Default should be row.
    - lock timeout : [value] with possible values: 0=infinite or N=seconds, default 10.
    - maxlockwait : [value] with possiblle values infinite or X=seconds,

    3) Is your application using stored procedures or only SQL?

    Again into USYS$MSS_PARAMS: procedures : on | off. default being on.

    4) Is your application working with a single (R)DBMS connection or with multiple (R)DBMS connections?

    When multiple connections: all connection are read/write mode same time meaning transaction(s) over multiple (R)DBMS or a single connection is "de facto" where all transactions are managed being others read only?

    Regards,
    Gianni

    P.S. My gotfeel is there are very few possibilities to forecast a deadlock coming; with some combinations of configuration is really impossible with other some tricks could probably be applied analyzing tables reporting current locks, but I do not think with success 100%...
    A good reading on the subject could be THIS.

    Hi Iain,

    I see you are using iso=ru and step=0! My experience with both of them it is not so good... 😔

    Using indexed views means you could trigger an index update when the basic table is updated; the view is not anymore readonly but have some degree of update in it. This kind of model did not work in my experience... 😔

    A couple of other suggestions based on experience:
    - we got a good improvement on concurrency stability when VIEWS in SqlServer defined as Uniface entities were not only be defined "No Updates" but using "with nolocks" on ALL tables involved into sql definition of view itself. This was reducing the number of shared locks issued from SqlServer when reading those views, going in this way in the direction of "reads should NOT lock writes and writes should NOT lock reads".
    - if disk space is not an issue for you using materialized view could slow down a bit all (R)DBMS updates but improve a lot reading because is avoiding that behaviour you described as "(the filter criteria are applied after the view is implemented, so SQL works out 1000's of answers to return one)". A materialized view is "de facto" a separate table maintained automatically from (R)DBMS; it could also be indexed as you are already doing with your current dynamic views.

    Fast forward to today; on a Uniface C/S application about warehouse&logistic delivered either on Oracle or SqlServer we worked about concurrency on SqlServer literally for years; same application on Oracle was working without any hiccup. At the end of a lot of single specific configurations and tuning efforts and testing on SqlServer we moved to a snapshot configuration using iso=ts and step=100 to be as much as possible consistent with "reads should NOT lock writes and writes should NOT lock reads". Since then concurrency was anymore NOT a problem; we are at same stability level on concurrency we have on Oracle but with some mandatory tricks. We now need to:
    1) carefully design & tune any new transaction
    2) have a properly sized and monitored tempDB in place because snapshots configuration are maintaining their "Version Store" into tempDB putting more pressure on it. We hope in the next future the "Version Store" will be maintained in memory releasing tempDB from the pressure the current implementation is putting on it.

    Hope it helps.

    Regards,
    Gianni

    PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!
    Iain Sharp
    Forum|alt.badge.img+5
    • Iain Sharp
    • Author
    • Inspiring
    • August 19, 2021

    Hi Iain,

    I feel some more details are needed to let the discussion flow because locking applied on the field from a Uniface application is defined from both application choices as well as (R)DBMS configuration choices:

    1) which locking model is your application aiming for?

    Uniface is proposing as default CAUTIOUS locking on all entities defined into Application Model, but developer could define entities as OPTIMISTIC or go for mixed choices like most tables on CAUTIOUS but few on OPTIMISTIC.

    2) which is your current MSS driver/connection configuration?

    On every application model table locking scenarios possible defined at#1, the applied MSS driver configuration using USYS$MSS_PARAMS could change answers received from (R)DBMS; parameters which address the application real lock scenario are:
    - lock isolation : [value] with possible values: rc=readCommitted, ru=readUncommitted, rr=repeatableRead, sl=serializable, ss=statementSnapshot, ts=transactionSnapshot. Default is rc.
    - lock granularity : [value] with possible values: page, table, row. Default should be row.
    - lock timeout : [value] with possible values: 0=infinite or N=seconds, default 10.
    - maxlockwait : [value] with possiblle values infinite or X=seconds,

    3) Is your application using stored procedures or only SQL?

    Again into USYS$MSS_PARAMS: procedures : on | off. default being on.

    4) Is your application working with a single (R)DBMS connection or with multiple (R)DBMS connections?

    When multiple connections: all connection are read/write mode same time meaning transaction(s) over multiple (R)DBMS or a single connection is "de facto" where all transactions are managed being others read only?

    Regards,
    Gianni

    P.S. My gotfeel is there are very few possibilities to forecast a deadlock coming; with some combinations of configuration is really impossible with other some tricks could probably be applied analyzing tables reporting current locks, but I do not think with success 100%...
    A good reading on the subject could be THIS.

    Hmm, according to Wikipedia, the SQL server version of a materialised view is an indexed view. 

    I have managed to get some feedback from my deadlock trace, and in every case at least one of the deadlocked resources has been one of our indexed views. This could, however, be an artefact of the fact that they are, aggregates of values, meaning updates to separate records in the (e.g.) stock table could be trying to update the same indexed view. 

    The only other way I can think of to make a materialised view would be to build triggers to update a table when the constituent parts of the view change. I don't see how that would materially differ from what it appears sql server is doing already. 

    I will research the iso=ts and step size locally and then see if we can implement on a customer site, as we have neither the data nor the staff to load test internally. 

    I'll be out of contact on this for a week or so due to holidays, but thanks for the hints. 

    Iain

    Iain Sharp : Head of Technical Services, Jonas Metals Software
    Gianni Sandigliano
    Forum|alt.badge.img

    Hi Iain,

    I feel some more details are needed to let the discussion flow because locking applied on the field from a Uniface application is defined from both application choices as well as (R)DBMS configuration choices:

    1) which locking model is your application aiming for?

    Uniface is proposing as default CAUTIOUS locking on all entities defined into Application Model, but developer could define entities as OPTIMISTIC or go for mixed choices like most tables on CAUTIOUS but few on OPTIMISTIC.

    2) which is your current MSS driver/connection configuration?

    On every application model table locking scenarios possible defined at#1, the applied MSS driver configuration using USYS$MSS_PARAMS could change answers received from (R)DBMS; parameters which address the application real lock scenario are:
    - lock isolation : [value] with possible values: rc=readCommitted, ru=readUncommitted, rr=repeatableRead, sl=serializable, ss=statementSnapshot, ts=transactionSnapshot. Default is rc.
    - lock granularity : [value] with possible values: page, table, row. Default should be row.
    - lock timeout : [value] with possible values: 0=infinite or N=seconds, default 10.
    - maxlockwait : [value] with possiblle values infinite or X=seconds,

    3) Is your application using stored procedures or only SQL?

    Again into USYS$MSS_PARAMS: procedures : on | off. default being on.

    4) Is your application working with a single (R)DBMS connection or with multiple (R)DBMS connections?

    When multiple connections: all connection are read/write mode same time meaning transaction(s) over multiple (R)DBMS or a single connection is "de facto" where all transactions are managed being others read only?

    Regards,
    Gianni

    P.S. My gotfeel is there are very few possibilities to forecast a deadlock coming; with some combinations of configuration is really impossible with other some tricks could probably be applied analyzing tables reporting current locks, but I do not think with success 100%...
    A good reading on the subject could be THIS.

    Hi Iain,

    still a comment on your last discoveries:
    I suppose you have very few index on your views (calling either materialized or indexed), probably just one.
    Is that index a basic index or a clustered one?
    If you can try to define it clustered because AFAIK it can reduce locking troubles having data and index into same (R)DBMS page.

    I wish you relaxed holidays! 🙂

    Gianni

    PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!
    Iain Sharp
    Forum|alt.badge.img+5
    • Iain Sharp
    • Author
    • Inspiring
    • August 20, 2021

    Hi Iain,

    I feel some more details are needed to let the discussion flow because locking applied on the field from a Uniface application is defined from both application choices as well as (R)DBMS configuration choices:

    1) which locking model is your application aiming for?

    Uniface is proposing as default CAUTIOUS locking on all entities defined into Application Model, but developer could define entities as OPTIMISTIC or go for mixed choices like most tables on CAUTIOUS but few on OPTIMISTIC.

    2) which is your current MSS driver/connection configuration?

    On every application model table locking scenarios possible defined at#1, the applied MSS driver configuration using USYS$MSS_PARAMS could change answers received from (R)DBMS; parameters which address the application real lock scenario are:
    - lock isolation : [value] with possible values: rc=readCommitted, ru=readUncommitted, rr=repeatableRead, sl=serializable, ss=statementSnapshot, ts=transactionSnapshot. Default is rc.
    - lock granularity : [value] with possible values: page, table, row. Default should be row.
    - lock timeout : [value] with possible values: 0=infinite or N=seconds, default 10.
    - maxlockwait : [value] with possiblle values infinite or X=seconds,

    3) Is your application using stored procedures or only SQL?

    Again into USYS$MSS_PARAMS: procedures : on | off. default being on.

    4) Is your application working with a single (R)DBMS connection or with multiple (R)DBMS connections?

    When multiple connections: all connection are read/write mode same time meaning transaction(s) over multiple (R)DBMS or a single connection is "de facto" where all transactions are managed being others read only?

    Regards,
    Gianni

    P.S. My gotfeel is there are very few possibilities to forecast a deadlock coming; with some combinations of configuration is really impossible with other some tricks could probably be applied analyzing tables reporting current locks, but I do not think with success 100%...
    A good reading on the subject could be THIS.

    They're all clustered, it's a requirement of the whole "indexed view" thing in SQL server, The views are generally to organise the data against a particular point, so the clustered index is the important one, but I think there are a couple with secondary indexes as well. 

    I'd relax better if I didn't think it was going to be failing updates and messing with the data all my week off. I am trying to train another member of staff in the tidying up I have to do daily to catch those problems I can identify after the fact. 


    Iain

    Iain Sharp : Head of Technical Services, Jonas Metals Software
    Iain Sharp
    Forum|alt.badge.img+5
    • Iain Sharp
    • Author
    • Inspiring
    • August 20, 2021

    Hi Iain,

    I feel some more details are needed to let the discussion flow because locking applied on the field from a Uniface application is defined from both application choices as well as (R)DBMS configuration choices:

    1) which locking model is your application aiming for?

    Uniface is proposing as default CAUTIOUS locking on all entities defined into Application Model, but developer could define entities as OPTIMISTIC or go for mixed choices like most tables on CAUTIOUS but few on OPTIMISTIC.

    2) which is your current MSS driver/connection configuration?

    On every application model table locking scenarios possible defined at#1, the applied MSS driver configuration using USYS$MSS_PARAMS could change answers received from (R)DBMS; parameters which address the application real lock scenario are:
    - lock isolation : [value] with possible values: rc=readCommitted, ru=readUncommitted, rr=repeatableRead, sl=serializable, ss=statementSnapshot, ts=transactionSnapshot. Default is rc.
    - lock granularity : [value] with possible values: page, table, row. Default should be row.
    - lock timeout : [value] with possible values: 0=infinite or N=seconds, default 10.
    - maxlockwait : [value] with possiblle values infinite or X=seconds,

    3) Is your application using stored procedures or only SQL?

    Again into USYS$MSS_PARAMS: procedures : on | off. default being on.

    4) Is your application working with a single (R)DBMS connection or with multiple (R)DBMS connections?

    When multiple connections: all connection are read/write mode same time meaning transaction(s) over multiple (R)DBMS or a single connection is "de facto" where all transactions are managed being others read only?

    Regards,
    Gianni

    P.S. My gotfeel is there are very few possibilities to forecast a deadlock coming; with some combinations of configuration is really impossible with other some tricks could probably be applied analyzing tables reporting current locks, but I do not think with success 100%...
    A good reading on the subject could be THIS.

    I have tried to make some of the views in the database use "with (nolock)" on the definition of the tables (indexed views), so the view definition reads (approximately)

    create view myview as 
select  
from iview1 with (nolock,noexpand)
left outer join iview2 with (nolock,noexpand) on 
left outer join iview3 with (nolock,noexpand) on ]] >


    However, when doing a read on this view, which is set to "No Updates" in the model AND the component, and I have removed the write/delete triggers (using undeclare in the component). 

    SQL returns an error of mismatched locking hints and an $ioprint of 32 shows. 

    select 
from [myview] with(rowlock)
 where (([key_1]=? and [key_2]=?))


I/O function: F, mode: 0, on file/table: myview index: 1 =
42000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conflicting locking hints are specified for table "myview". This may be caused by a conflicting hint specified for a view.
"


    I've set iso=rs and step=100 - Still issues a rowlock

    I've set the database to allow transaction snapshot and set it to iso=ts no joy. I've had to take the view back to with (noexpand) to get the read to work. 

    I can't set the definition of the indexed views to read the tables "with (nolock)" as that prevents SQL server from creating the indexes (an error about not using hints in indexed views.)

    Iain Sharp : Head of Technical Services, Jonas Metals Software
    Gianni Sandigliano
    Forum|alt.badge.img

    Hi Iain,

    I feel some more details are needed to let the discussion flow because locking applied on the field from a Uniface application is defined from both application choices as well as (R)DBMS configuration choices:

    1) which locking model is your application aiming for?

    Uniface is proposing as default CAUTIOUS locking on all entities defined into Application Model, but developer could define entities as OPTIMISTIC or go for mixed choices like most tables on CAUTIOUS but few on OPTIMISTIC.

    2) which is your current MSS driver/connection configuration?

    On every application model table locking scenarios possible defined at#1, the applied MSS driver configuration using USYS$MSS_PARAMS could change answers received from (R)DBMS; parameters which address the application real lock scenario are:
    - lock isolation : [value] with possible values: rc=readCommitted, ru=readUncommitted, rr=repeatableRead, sl=serializable, ss=statementSnapshot, ts=transactionSnapshot. Default is rc.
    - lock granularity : [value] with possible values: page, table, row. Default should be row.
    - lock timeout : [value] with possible values: 0=infinite or N=seconds, default 10.
    - maxlockwait : [value] with possiblle values infinite or X=seconds,

    3) Is your application using stored procedures or only SQL?

    Again into USYS$MSS_PARAMS: procedures : on | off. default being on.

    4) Is your application working with a single (R)DBMS connection or with multiple (R)DBMS connections?

    When multiple connections: all connection are read/write mode same time meaning transaction(s) over multiple (R)DBMS or a single connection is "de facto" where all transactions are managed being others read only?

    Regards,
    Gianni

    P.S. My gotfeel is there are very few possibilities to forecast a deadlock coming; with some combinations of configuration is really impossible with other some tricks could probably be applied analyzing tables reporting current locks, but I do not think with success 100%...
    A good reading on the subject could be THIS.

    Hi Iain,

    on MSS defining iso equal ts or ss has pre-requisites at database level configuration. Without those pre-requisites the connection does not activate the snapshot and it is acting as iso=rc.

    More information HERE.

    Gianni

    PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement