ASE 11.5 vs ASE 12 : exclusive locks at page / table

ASE 11.5 vs ASE 12 : exclusive locks at page / table

Post by Ashi » Wed, 27 Feb 2002 15:00:03



We are in the process of upgrading from ASE 11.5 to ASE 12.0

In ASE 11.5,
Our developers use the following to get a table level exclusive lock :

  begin tran
  update T1 set C1 = xxx where 1 = 3
  ...
  ...

This grants a table level exclusive lock.

In ASE 12.0,
The same stuff grants a level exclusive lock on a page and a Exclusive
Intent lock on the table.
So, the developers are planning to start using the lock table command.

I do not know the exact details regarding their programming ... why
they are doing this,  what isolation level they need etc. etc.

But I am surprised at the behaviour of ASE 12.

I created a test table T1 with one column C1.
There were no indexes.
I inserted one row in the table with C1 = 1.
The table had Allpages locking scheme.
When I gave the following :

  begin tran
  update T1 set C1 = 2 where C1 = 1 /* this results in 1 row updated
*/

I got a page level exclusive lock and a table level exclusive intent
lock.
The Performance and Tuning Manual says on page 26-8 (bottom of page) :

"If no index exists on any of the search arguments, Adaptive Server
must acquire a table-level lock."

Either the manual is incorrect or I am missing something.
Please help.

 
 
 

ASE 11.5 vs ASE 12 : exclusive locks at page / table

Post by Ilya Zvyagin 214748364 » Wed, 27 Feb 2002 16:11:05


Hello, Ashish!
You wrote  on 25 Feb 2002 22:00:03 -0800:

 A> We are in the process of upgrading from ASE 11.5 to ASE 12.0
 A> In ASE 11.5,
 A> Our developers use the following to get a table level exclusive lock
 A> :
 A>   begin tran   update T1 set C1 = xxx where 1 = 3   ...
 A>   ...
 A> This grants a table level exclusive lock.

Use LOCK TABLE.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port

ICQ UID: 29427861(MasterZIV)

 
 
 

ASE 11.5 vs ASE 12 : exclusive locks at page / table

Post by Anthony Mandi » Wed, 27 Feb 2002 20:40:40



> In ASE 11.5,
> Our developers use the following to get a table level exclusive lock :

>   begin tran
>   update T1 set C1 = xxx where 1 = 3
>   ...
>   ...

> This grants a table level exclusive lock.

> In ASE 12.0,
> The same stuff grants a level exclusive lock on a page and a Exclusive
> Intent lock on the table.
> So, the developers are planning to start using the lock table command.

> I do not know the exact details regarding their programming ... why
> they are doing this,  what isolation level they need etc. etc.

        It might be better to dig into that first. It sounds to me
        like they are doing something ridiculous that will come back
        to haunt someone (usually the DBA).

...

Quote:> The Performance and Tuning Manual says on page 26-8 (bottom of page) :

> "If no index exists on any of the search arguments, Adaptive Server
> must acquire a table-level lock."

> Either the manual is incorrect or I am missing something.

        The manual might be wrong or you could be missing an EBF.
        Check your exact version of ASE 12.0.

-am     ? 2002

 
 
 

1. Migrating dbs from ASE 11.0.3.3 (desupported) to ASE 12.x

Hi Folks

I am a new user to Sybase and have been entrusted the task to
investigate the upgrade from  our currently desupported ASE-11.0.3.3
to version 12.

Has anybody out there done this, and if not what should I be paying
particular attention to when doing the upgrade

Any help, advice would be greatly appreciated


2. Praveen

3. Locking on ASE 11.5 PLEASE HELP!!

4. runtime error 3067 "provider cannot be found"

5. ASE 12 filesystem vs. raw partitions

6. SQL Jobs

7. bcp in Ase 11.5.1 syslogins to ASE 12.0 not working

8. pgsql/src/include/storage (bufpage.h)

9. loading data dump from ASE 11.9 to ASE 12.0 sets it to offline after load

10. ASE 11.5 Alter table error

11. Migration from Sybase ASE 11.5 to SQL Server 2000

12. DTS problem: insertion of BLOB values on Sybase ASE 11.5 destination

13. VB 5.0 and Sybase ASE 11.5