|
Home > Archive > SQL Anywhere database > July 2005 > Serialization from Delete statement in ASA8
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Serialization from Delete statement in ASA8
|
|
|
| We are working in a client -server environment using VB6 as front end and
connected to ASA8 thru ODBC. Two users are logging in VB6 program and
trying to delete 2 different sets of data located in the same table in 2
different years. The size of the data is pretty big (around 100000 rows or
20 MB for each year). The table also has primary key on year and other
fields to make data unique. However, the program is kicked off for one user
with the message:"Serialization failure: user 1 has the row in table 1
locked". The database is set to the isolation level 1. How could we avoid
this error? Is there some setting that allow the deletion go through?
Thanks for help
Vanny
| |
| Stephen Rice 2005-06-27, 8:23 pm |
| Vanny wrote:
> We are working in a client -server environment using VB6 as front end and
> connected to ASA8 thru ODBC. Two users are logging in VB6 program and
> trying to delete 2 different sets of data located in the same table in 2
> different years. The size of the data is pretty big (around 100000 rows or
> 20 MB for each year). The table also has primary key on year and other
> fields to make data unique. However, the program is kicked off for one user
> with the message:"Serialization failure: user 1 has the row in table 1
> locked". The database is set to the isolation level 1. How could we avoid
> this error? Is there some setting that allow the deletion go through?
>
> Thanks for help
>
> Vanny
>
>
It sounds like you are running into locks on the index positions and you
have blocking turned off. I assume blocking is off for a good reason
otherwise I would expect the failing transaction would just sit and wait
for the other one to complete.
If you are free to experiment you can try to confirm this by dropping
all indexes from the problem table and repeating the deletes. If it is
the indexes the problem will be gone.
The concepts I am talking about are discussed in the v9 docs in the section:
ASA SQL User's Guide
Using Transactions and Isolation Levels
How locking works
Locking during deletes
HTH
/steve
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com
| |
|
| Thanks for the response. Yes, the database has the blocking option off. We
got this error before we put the primary keys on the problem table. We
thought putting primary key will help to avoid the error, but it doesn't.
Since the problem does not come from the indexes, what other issues could it
be?
Thanks,
Vanny
"Stephen Rice" <NSsrice@ianywhere.com> wrote in message
news:42c06171$1@foru
ms-2-dub...
> Vanny wrote:
> It sounds like you are running into locks on the index positions and you
> have blocking turned off. I assume blocking is off for a good reason
> otherwise I would expect the failing transaction would just sit and wait
> for the other one to complete.
>
> If you are free to experiment you can try to confirm this by dropping all
> indexes from the problem table and repeating the deletes. If it is the
> indexes the problem will be gone.
>
> The concepts I am talking about are discussed in the v9 docs in the
> section:
> ASA SQL User's Guide
> Using Transactions and Isolation Levels
> How locking works
> Locking during deletes
>
> HTH
>
> /steve
>
> --
> Stephen Rice
> Technical Services Manager
> iAnywhere Solutions
>
> --- Please Post ---
> Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
> "Report a bug" are all available on www.ianywhere.com
| |
| Stephen Rice 2005-06-28, 11:23 am |
| Vanny wrote:
> Thanks for the response. Yes, the database has the blocking option off. We
> got this error before we put the primary keys on the problem table. We
> thought putting primary key will help to avoid the error, but it doesn't.
> Since the problem does not come from the indexes, what other issues could it
> be?
>
> Thanks,
> Vanny
>
> "Stephen Rice" <NSsrice@ianywhere.com> wrote in message
> news:42c06171$1@foru
ms-2-dub...
>
>
>
>
if there are no indexes on the table then AFAIK you are actually trying
to access the same row from both connections somehow and locking is
preventing it.
So to figure out what the engine is really being asked to do I would
turn on statement tracing somewhere. Either in ODBC or turn on the
server SQL statement tracing (see -zr SQL -zo -zs). These traces should
show the exact sequence of operations and it "should be obvious" what is
going wrong. (yes that was very easy for me to say :) Seriously, I
expect you will find a couple of extra operations within your
transaction and one of these is taking a lock you aren't expecting.
Let us know what you learn
/steve
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com
| |
|
| Xref: number1.nntp.dca.giganews.com sybase.public.sqlanywhere.general:100669
I do not have a chance to try the tracing yet. We retested our system thru
VB by taking off the primary key from the table. You're right, the "delete"
statement goes thru for both users. However, the error comes from the
"insert into.... select" statement written in a stored procedure that
follows the deletion when user_1 is in insert phase and user_2 is in delete
phase. It seems this is the effect of anti-insert.
Since I'm not a database administrator, I would like to ask you a general
question:
"What is a good reason to set the blocking option off to the database?"
Thank again for your help
Vanny
"Stephen Rice" <NSsrice@ianywhere.com> wrote in message
news:42c16bb9$1@foru
ms-2-dub...
> Vanny wrote:
> if there are no indexes on the table then AFAIK you are actually trying to
> access the same row from both connections somehow and locking is
> preventing it.
>
> So to figure out what the engine is really being asked to do I would turn
> on statement tracing somewhere. Either in ODBC or turn on the server SQL
> statement tracing (see -zr SQL -zo -zs). These traces should show the
> exact sequence of operations and it "should be obvious" what is going
> wrong. (yes that was very easy for me to say :) Seriously, I expect you
> will find a couple of extra operations within your transaction and one of
> these is taking a lock you aren't expecting.
>
> Let us know what you learn
>
> /steve
> --
> Stephen Rice
> Technical Services Manager
> iAnywhere Solutions
>
> --- Please Post ---
> Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
> "Report a bug" are all available on www.ianywhere.com
| |
| Stephen Rice 2005-07-12, 11:23 am |
| Vanny wrote:
> I do not have a chance to try the tracing yet. We retested our system thru
> VB by taking off the primary key from the table. You're right, the "delete"
> statement goes thru for both users. However, the error comes from the
> "insert into.... select" statement written in a stored procedure that
> follows the deletion when user_1 is in insert phase and user_2 is in delete
> phase. It seems this is the effect of anti-insert.
>
> Since I'm not a database administrator, I would like to ask you a general
> question:
> "What is a good reason to set the blocking option off to the database?"
>
> Thank again for your help
>
> Vanny
> "Stephen Rice" <NSsrice@ianywhere.com> wrote in message
> news:42c16bb9$1@foru
ms-2-dub...
>
>
>
>
What blocking does is cause one user to automatically wait until the
user that has taken the lock completes their transaction. Most
applications I've encountered work this way.
I have seen blocking turned off for high performance applications where
it is preferable to know the transaction didn't complete as opposed to
waiting some, indefinite, length of time. Most of these application are
coded to provide semi-automatic retrys.
/steve
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com
|
|
|
|
|