SQL Anywhere database - Locking and DDL Statements

Author

Locking and DDL Statements

Melody Pedzisai

2005-12-19, 8:23 pm

I have a classic problem that involves locking/concurreny and the use of DDL
statements, specifically, creating fields and/or tables. My application
allows users to create custom database fields and tables on the fly. The
problem is that as long as someone is connected to the database, let alone
using some of the tables that may be affected by the DDL, the user would not
be able to complete the DDL.

What options do I have that would allow me to be able to perform DDL
statements while other users are connected to the same database, or better
yet, using the same tables that will be affected by the DDL.

Scenarios already tried :-

Scenario 1:
1. Isolation Level 0
2. Blocking = Off
3. Blocking Timeout = 0
4. Induce a read lock by doing a select on the table
5. Before (4) is completed, try a DDL thus - ALTER table x add (f1 integer)
6. result is error code -210

Scenario 2:
1. Isolation Level 0
2. Blocking = ON
3. Blocking Timeout = 0
4. Induce a read lock by doing a select on the table
5. Before transaction (4) is completed, try a DDL thus - ALTER table x add
(f1 integer)
6. result is error - Transaction (5) Blocks

Scenario 3:
1. Isolation Level 0
2. Blocking = ON
3. Blocking Timeout = 0
4. Induce a read lock by doing a select on the table
5. Before transaction (4) is completed, try a DDL thus -ALTER table x add
(f1 integer)
6. Induce another read lock by doing a select as in (4) above ona different
connection
7. Complete transaction (4) above by doing a commit (dont know any other
way of forcing completion of transaction)
8.Check Blocking Status of transaction (5) above -- STILL BLOCKED by
transaction 6 which apparently acquired a read lock after (5) had supposedly
acquired a more stringent schema lock

What options do I have ??

Thanks

Melody

David Kerber

2005-12-19, 8:23 pm

In article <43a7126f$1@forums-1-dub>, mel_nsx_ody@nospam.com says...

....

> 8.Check Blocking Status of transaction (5) above -- STILL BLOCKED by
> transaction 6 which apparently acquired a read lock after (5) had supposedly
> acquired a more stringent schema lock

A schema lock isn't very strict; it just prevents anything from changing
the schema while the lock is set, and I don't believe it will prevent
another connection from getting a read lock on specific rows. Beyond
that, I can't help you...

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Breck Carter [TeamSybase]

2005-12-19, 8:24 pm

Pretty much every SQL operation involving tables (e.g., SELECT) will
grab a "shared table schema lock" on each table it touches. These
locks show up in the sa_locks() as lock_type = 'S' and lock_name =
(NULL). These locks do not block updates, etc., but they do block most
DDL operations. These locks go away with a COMMIT, something that a
connection doing queries may not do, so they often last a lonnnng
time... but that might be the first line of attack, make all the query
connections do frequent commits.

The powers-that-be hold the opinion that great care must be taken with
DDL, and that one "should not" do DDL things while the database is in
active use... it is "too dangerous". (shhhh, I don't share that
opinion, but don't tell anyone :)

One approach that has been used is just *let* the DDL block;
eventually it will run, when everyone else goes home... I'm betting
you won't like that.

Another approach might be to kick everyone else off, and *keep* them
off, until the DDL runs. Do this Google Groups search...

p_drop_other_connect
ions group:sybase.public.sqlanywhere.*

....then pick the Aug 2 2004 message.

Yet another approach is to use Block Sniffer's "AutoDrop" feature; you
can exclude "important" connections from being dropped. See
http://www.risingroad.com/block_sniffer.html

Breck

On 19 Dec 2005 12:05:03 -0800, "Melody Pedzisai"
<mel_nsx_ody@nospam.com> wrote:

>I have a classic problem that involves locking/concurreny and the use of DDL
>statements, specifically, creating fields and/or tables. My application
>allows users to create custom database fields and tables on the fly. The
>problem is that as long as someone is connected to the database, let alone
>using some of the tables that may be affected by the DDL, the user would not
>be able to complete the DDL.
>
>What options do I have that would allow me to be able to perform DDL
>statements while other users are connected to the same database, or better
>yet, using the same tables that will be affected by the DDL.
>
>Scenarios already tried :-
>
>Scenario 1:
>1. Isolation Level 0
>2. Blocking = Off
>3. Blocking Timeout = 0
>4. Induce a read lock by doing a select on the table
>5. Before (4) is completed, try a DDL thus - ALTER table x add (f1 integer)
>6. result is error code -210
>
>
>Scenario 2:
>1. Isolation Level 0
>2. Blocking = ON
>3. Blocking Timeout = 0
>4. Induce a read lock by doing a select on the table
>5. Before transaction (4) is completed, try a DDL thus - ALTER table x add
>(f1 integer)
>6. result is error - Transaction (5) Blocks
>
>
>Scenario 3:
>1. Isolation Level 0
>2. Blocking = ON
>3. Blocking Timeout = 0
>4. Induce a read lock by doing a select on the table
>5. Before transaction (4) is completed, try a DDL thus -ALTER table x add
>(f1 integer)
>6. Induce another read lock by doing a select as in (4) above ona different
>connection
>7. Complete transaction (4) above by doing a commit (dont know any other
>way of forcing completion of transaction)
>8.Check Blocking Status of transaction (5) above -- STILL BLOCKED by
>transaction 6 which apparently acquired a read lock after (5) had supposedly
>acquired a more stringent schema lock
>
>
>What options do I have ??
>
>Thanks
>
>Melody
>
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links