MS SQL Server ODBC - Wrong locking behaviour on Service Pack 4

Author

Wrong locking behaviour on Service Pack 4

Joda

2005-08-02, 3:23 am

Hello everyone,

we see a strange behaviour after installing SP4 (under WIN2000).
On preSP4 environment everthing works well and as expected.

There is a (very simple) task:
Select a single one row, accessed by a defined 2-part-key, * with lock*.

A specific situation here is the use of ODBC-instructions, such as:

1 <SQL_ATTR_MAX_ROWS> ;only one row to access
6 < SQL_ATTR_CURSOR_TYPE
> ;cursor type dynamic
7 < SQL_ATTR_CONCURRENCY
> ;lock that row

And after preparing and binding we execute:
"SELECT MW_year,provider,FS,
GBR,name,E_origin,E_
user,
E_date,E_time,A_orig
in,A_user,A_date,A_t
ime
FROM GM_GBR_provider (nowait)
WHERE MW_year = 2004 AND provider = 354 ORDER BY MW_year,provider"

Ok so far, we are getting that row, but:
* all rows in key sequence behind that specific row get locked
such as other workstations cannot access any of these rows *.

Important remark: That situation doesn't occur if only one key is used.

Any idea, please ?

Yours sincerely

Hello everyone,

we see a strange behaviour after installing SP4 (under WIN2000).
On preSP4 environment everthing works well and as expected.

There is a (very simple) task:
Select a single one row, accessed by a defined 2-part-key, * with lock*.

A specific situation here is the use of ODBC-instructions, such as:

1 <SQL_ATTR_MAX_ROWS> ;only one row to access
6 < SQL_ATTR_CURSOR_TYPE
> ;cursor type dynamic
7 < SQL_ATTR_CONCURRENCY
> ;lock that row

And after preparing and binding we execute:
"SELECT MW_year,provider,FS,
GBR,name,E_origin,E_
user,
E_date,E_time,A_orig
in,A_user,A_date,A_t
ime
FROM GM_GBR_provider (nowait)
WHERE MW_year = 2004 AND provider = 354 ORDER BY MW_year,provider"

Ok so far, we are getting that row, but:
* all rows in key sequence behind that specific row get locked
such as other workstations cannot access any of these rows *.

Important remark: That situation doesn't occur if only one key is used.

Any idea, please ?

Yours sincerely
joda

Mike Epprecht \(SQL MVP\)

2005-08-02, 11:23 am

Hi

What indexes do you have on the table?

What does the "(nowait)" locking hint do? Is that correct?

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Joda" <joda@nospam.net> wrote in message
news:u9ZJDgzlFHA.576@TK2MSFTNGP15.phx.gbl...
>
> Hello everyone,
>
> we see a strange behaviour after installing SP4 (under WIN2000).
> On preSP4 environment everthing works well and as expected.
>
> There is a (very simple) task:
> Select a single one row, accessed by a defined 2-part-key, * with lock*.
>
> A specific situation here is the use of ODBC-instructions, such as:
>
> 1 <SQL_ATTR_MAX_ROWS> ;only one row to access
> 6 < SQL_ATTR_CURSOR_TYPE
> ;cursor type dynamic
> 7 < SQL_ATTR_CONCURRENCY
> ;lock that row
>
> And after preparing and binding we execute:
> "SELECT MW_year,provider,FS,
GBR,name,E_origin,E_
user,
> E_date,E_time,A_orig
in,A_user,A_date,A_t
ime
> FROM GM_GBR_provider (nowait)
> WHERE MW_year = 2004 AND provider = 354 ORDER BY MW_year,provider"
>
> Ok so far, we are getting that row, but:
> * all rows in key sequence behind that specific row get locked
> such as other workstations cannot access any of these rows *.
>
> Important remark: That situation doesn't occur if only one key is used.
>
> Any idea, please ?
>
> Yours sincerely
>
> Hello everyone,
>
> we see a strange behaviour after installing SP4 (under WIN2000).
> On preSP4 environment everthing works well and as expected.
>
> There is a (very simple) task:
> Select a single one row, accessed by a defined 2-part-key, * with lock*.
>
> A specific situation here is the use of ODBC-instructions, such as:
>
> 1 <SQL_ATTR_MAX_ROWS> ;only one row to access
> 6 < SQL_ATTR_CURSOR_TYPE
> ;cursor type dynamic
> 7 < SQL_ATTR_CONCURRENCY
> ;lock that row
>
> And after preparing and binding we execute:
> "SELECT MW_year,provider,FS,
GBR,name,E_origin,E_
user,
> E_date,E_time,A_orig
in,A_user,A_date,A_t
ime
> FROM GM_GBR_provider (nowait)
> WHERE MW_year = 2004 AND provider = 354 ORDER BY MW_year,provider"
>
> Ok so far, we are getting that row, but:
> * all rows in key sequence behind that specific row get locked
> such as other workstations cannot access any of these rows *.
>
> Important remark: That situation doesn't occur if only one key is used.
>
> Any idea, please ?
>
> Yours sincerely
> joda
>

Joda

2005-08-04, 3:23 am

Hi Mike,
thank you for response.

That (composite) index is:
"MW_year, provider" primary, unique numeric values, as given in ORDER BY.

The (nowait) hint, currently undocumented obviously, is important to us:
If the row to select is actually locked by another client the application
should not stop and wait until being released,
* but must return immediately with an appropriate error code *
as it behaves perfectly in preSP4-environment.

Is there another method for this functionality now, did we miss something ?

Thank you in advance.
Yours sincerely
Joda.

"Mike Epprecht \(SQL MVP\)" <mike@epprecht.net> wrote:
>Hi
>
>What indexes do you have on the table?
>
>What does the "(nowait)" locking hint do? Is that correct?
>
>Regards
>--------------------------------
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@epprecht.net
>
>MVP Program: http://www.microsoft.com/mvp
>
>Blog: http://www.msmvps.com/epprecht/
>
>"Joda" <joda@nospam.net> wrote in message
>news:u9ZJDgzlFHA.576@TK2MSFTNGP15.phx.gbl...
>
>

Sponsored Links