Any reason to use AUTOCOMMIT=TRUE?
Breck Carter
Is there any good reason to use AUTOCOMMIT=TRUE via ODBC (e.g., from
PowerBuilder)?
I have heard it said that AUTOCOMMIT=TRUE should be used, together
with explicit BEGIN TRANSACTION and COMMIT/ROLLBACK TRANSACTION
commands (via execute immediate in PB), "to avoid log-fill-ups / locks
/ contention / retrieve-commits-coding" (not my words).
I am trying to understand what might be going on. I thought the
AUTOCOMMIT=TRUE thing was what you did to stop the SQL Server (ASE,
etc) problem with long-running transactions and the log filling up.
Is there also a problem with SQL Anywhere?
Breck
Paul Horan
Breck,
We use this approach for SQLA, and it works fine. The application starts
with AUTOCOMMIT=TRUE. It's set that way until the application needs to do
an update to the database. We call the BeginTran function in our
dbConnection object (descendent of Transaction), which sets AUTOCOMMIT to
false. Then issue the updates, whether those are embedded SQL or dw/ds
Update()s, then either EndTran( TRUE ), or EndTran (FALSE)
(commit/rollback respectively), and flip back to Autocommit=TRUE. This
makes sure there are no open transactions during simple Select statements,
and that each update, even single statement updates, are wrapped within
transaction boundaries.
Paul Horan
VCI
Springfield, MA
Breck Carter <NOSPAM_...@bcarter.com> wrote in message
news:377f8358...@forums.sybase.com...
Leo Tohill
I am not a fan of autocommit. It seems to me that only reason to use it would be if you want the
same code to work with both ASA and ASE. ASE holds (optionally, I guess) locks on SELECTs, and ASA
does not, except at isolev 3. The whole begin/end tran business that goes along with autocommit is,
in my mind, a complication that is unecessary with ASA.
Compare the description that Paul gave, using autocommit, to this:
Issue SELECTs whenever you like. No tran management required.
Issue Updates, Inserts, Deletes, followed by COMMIT when the transaction is complete.
Leo Tohill - Team Sybase
>> Please post in newsgroup, not via email <<
Breck Carter
wrote:
>I am not a fan of autocommit. It seems to me that only reason to use it would be if you want the
>same code to work with both ASA and ASE. ASE holds (optionally, I guess) locks on SELECTs, and ASA
>does not, except at isolev 3. The whole begin/end tran business that goes along with autocommit is,
>in my mind, a complication that is unecessary with ASA.
>
>Compare the description that Paul gave, using autocommit, to this:
>
>Issue SELECTs whenever you like. No tran management required.
>Issue Updates, Inserts, Deletes, followed by COMMIT when the transaction is complete.
So you haven't heard of any problems caused by AUTOCOMMIT = FALSE?
Neither have I. And since you can change isolation level on the fly,
even within a transaction, not even that should be an issue.
Breck
Leo Tohill
>Neither have I. And since you can change isolation level on the fly,
>even within a transaction, not even that should be an issue.
right.
John Smirnios
performance complaints. Virtually every commit causes at least one disk IO. I have heard many users
complain about slow performance when inserting, updating or deleting lots of rows just to find out that
they have autocommit enabled.
-john.
Leo Tohill wrote:
> I am not a fan of autocommit. It seems to me that only reason to use it would be if you want the
> same code to work with both ASA and ASE. ASE holds (optionally, I guess) locks on SELECTs, and ASA
> does not, except at isolev 3. The whole begin/end tran business that goes along with autocommit is,
> in my mind, a complication that is unecessary with ASA.
>
> Compare the description that Paul gave, using autocommit, to this:
>
> Issue SELECTs whenever you like. No tran management required.
> Issue Updates, Inserts, Deletes, followed by COMMIT when the transaction is complete.
>
> Leo Tohill - Team Sybase
> ?? Please post in newsgroup, not via email ??
Brian Crooke
I believe the reason that we have recommended using the AUTOCOMMIT = TRUE
in the past was the impact of open transactions on Replication Server. We
have seen PowerBuilder and other applications open a transaction
immediately when they connect to the database and Replication Server,
maintaining transactional consistency, could not replicate anything
beyond this transaction until it was committed, even though the user
hadn't done anything. This is the scenario that would lead to filled
transaction logs because it could only be dumped up to the earliest open
transaction. We would have users login in the morning and tie thing up
all day long.
Now ASE, since 11.0.3 I believe, maintains a separate transaction log
cache for each user, so the filled transaction log problem has been
minimized.
Brian Crooke, SPS
Leo Tohill wrote:
> >So you haven't heard of any problems caused by AUTOCOMMIT = FALSE?
> >Neither have I. And since you can change isolation level on the fly,
> >even within a transaction, not even that should be an issue.
>
> right.
>
> Leo Tohill - Team Sybase
Mike Packard
John,
What are you saying here? Are you saying that your users have applications that allow them to make changes
to multiple rows (same table?) for some unspecified period of time and then post them all at once? In a
multi-user environment? Sorry, but I can't imagine this working without people stepping on each other's
updates at some point. Please explain.
John Smirnios wrote:
> > Leo Tohill - Team Sybase
> > ?? Please post in newsgroup, not via email ??
Breck Carter
That's good news about ASE log caches. And the Replication Server
wrinkle is a new one to me.
*However*, the original question was about SQL Anywhere (ASA)... with
this DBMS I cannot remember ANY scenario where AUTOCOMMIT=TRUE was
even suggested as a good thing. Can you?
ASE/SQL Server, that's a whole different bedtime story :)
Breck
On Tue, 06 Jul 1999 09:23:38 -0400, Brian Crooke <bcr...@sybase.com>
wrote:
>> Leo Tohill - Team Sybase
Breck Carter
<mpac...@mail.sandi.net> wrote:
>John,
>
>What are you saying here? Are you saying that your users have applications that allow them to make changes
>to multiple rows (same table?) for some unspecified period of time and then post them all at once? In a
>multi-user environment? Sorry, but I can't imagine this working without people stepping on each other's
>updates at some point. Please explain.
I'll give it a shot... between the beginning of a transaction
(usually, the first UPDATE/DELETE/INSERT) and the end of a transaction
(COMMIT/ROLLBACK) the end user must never ever have control of the
keyboard or mouse in a multi-user environment. To do otherwise is to
ask for concurrency-crippling locks, deadlocks and deadly embraces.
So, if a user is making changes to many rows as part of one
transaction, the actual SQL commands *must* wait until the user says
"I'm done!" and then get executed all at once while the user stares at
an hourglass. E.g., all the updates and the commit/rollback appear
together in cb_save.clicked (back in the days when life was simple;
i.e, before PFC :)
This is the essence of optimistic concurrency control, by far the most
popular technique in use today, and IMHO the only one to bother with.
Collisions are prevented by giant WHERE clauses (automatically created
in the case of PB) on the SQL updates that say "make this change only
if the row has not already been changed by someone else since I
retrieved it". If A and B both retrieve row X, and both change it, and
then A issues an UPDATE before B, user B loses with the dreaded (in
PowerBuilder) "row changed between retrieve and update" message.
That message is our friend. It has prevented database inconsistency.
It should be extremely rare (what business is set up where two users
frequently try to update the same row at the same time?)
In reality it IS extremely rare, especially after the first time,
where user B takes a 2-by-4 to the side of user A's head, and they
work out a division of labor in the office.
Yes, people do step on each other's updates from time to time. The
alternatives are VERY difficult to code (application-level locking) or
to recover from (inconsistencies caused by AUTOCOMMIT=TRUE).
Breck
Brian Crooke
Same as with Leo, I am not aware of any.
Brian
matt...@proam.com
Another thing we discovered using SQLA 5.5 -- if we want to handle all
transaction processing ourselves, we had to turn off the "ODBC autocommit"
attribute with the ODBC function SQLSetConnectOption(). Otherwise, SQLA
would commit and not allow a rollback.
JSB
and I do a dw.Retrieve(). The select statement is automatically committed, and everything is fine.
If I make several changes to multiple rows of the datawindow and call dw.Update(), am I actually
sending several SQL statements each of which is being committed separately?
To be more specific, If three rows are to be updated in a datawindow and the first 2 rows update
correctly, but the last row does not, does Autocommit=TRUE cause commits to be executed for
the first two rows? Or are they all rolled back when I check the results of Update()?
John Brown
jsb...@niia.net
Breck Carter
On Tue, 06 Jul 1999 11:34:14 -0500, JSB <jsb...@niia.net> wrote:
>Ok, I'm confused. If autocommit is set to TRUE in the connection parameters of a PB application,
>and I do a dw.Retrieve(). The select statement is automatically committed, and everything is fine.
>If I make several changes to multiple rows of the datawindow and call dw.Update(), am I actually
>sending several SQL statements each of which is being committed separately?
>
>To be more specific, If three rows are to be updated in a datawindow and the first 2 rows update
>correctly, but the last row does not, does Autocommit=TRUE cause commits to be executed for
>the first two rows? Or are they all rolled back when I check the results of Update()?
>
>John Brown
>jsb...@niia.net
That's what it means... ODBC and the database server (SQL Anywhere)
just see a series of UPDATE, DELETE and INSERT commands, not a single
Update() call. And AutoCommit=TRUE tells them to commit after each and
every one. Rollbacks are pointless.
Hard to say which is worse, AUTOCOMMIT=TRUE or SetTrans :)
Breck
Breck Carter
I think if you set the ODBC autocommit to false, you are undoing the
PowerBuilder AUTOCOMMIT=TRUE option (I am assuming you're talking
about PB here).
And yes, you cannot control transaction processing while the database
is automatically committing ever single individual command.
So, please tell me, what is the reason for using AUTOCOMMIT=TRUE with
SQL Anywhere?
Breck
Jay Fienberg
Leo Tohill wrote in message <377faf4...@199.93.177.77>...
>Issue SELECTs whenever you like. No tran management required.
>Issue Updates, Inserts, Deletes, followed by COMMIT when the transaction
is complete.
I have been having some locking issues from PowerDynamo to ASA, in
particular with regards to some stored procedures I have. So, this thread
has been helpful, and I wanted to ask this to get really clear:
Using autocommit= false:
If the isolation level is 0, a SELECT statement against a row in table
should in no way set a lock that prevents another user from updating that
same row, right? As long as the isolation level is 0, and autocommit is
false, a SELECT starts no transaction or locks, right?
TIA
Jay Fienberg
The Laughing Man Library of Adidam
http://library.LaughingMan.org
Breck Carter
wrote:
>Leo Tohill wrote in message <377faf4...@199.93.177.77>...
>>Issue SELECTs whenever you like. No tran management required.
>>Issue Updates, Inserts, Deletes, followed by COMMIT when the transaction
>is complete.
>
>
>I have been having some locking issues from PowerDynamo to ASA, in
>particular with regards to some stored procedures I have. So, this thread
>has been helpful, and I wanted to ask this to get really clear:
>
>Using autocommit= false:
>
>If the isolation level is 0, a SELECT statement against a row in table
>should in no way set a lock that prevents another user from updating that
>same row, right? As long as the isolation level is 0, and autocommit is
>false, a SELECT starts no transaction or locks, right?
Correct.
You might be able to find the cause with the technique suggested in
http://www.bcarter.com/sap27.htm, or use dbconsol (dbwatch in SQL
Anywhere 5.x).
Breck
Jay Fienberg
Breck Carter wrote in message <37824297....@forums.powersoft.com>...
>You might be able to find the cause with the technique suggested in
>http://www.bcarter.com/sap27.htm, or use dbconsol (dbwatch in SQL
>Anywhere 5.x).
Thanks for the tips. At this point, it is a little difficult for me to
figure out who is blocking whom because the connections are through
PowerDynamo, and all use the same pooled connections (with the same user id)
to the database. In the future, I will see if using the new ASA 6.02 SETUSER
statement helps with this kind of thing.
Thanks again.
Mike Packard
From what Leo has stated here, ASA does not do Select locking. Some
other dbs do. So yes, there won't be a lock for the Select.
However, please be aware that isolation level 0 will allow "dirty
reads". Which means that you can "see" uncommitted data. I'd prefer
level 1 where the select will only return committed data.
Uncommitted data is, essentially, fertilizer. Once it's committed then
it becomes "edible".
Of course, this depends on your purpose, too.
Mike Packard
I'm not sure you understood my question. I was questioning the viability
of a user editing multiple rows in the same table as a single
transaction.
I do not have any apps nor have I worked on any apps where the user
edits multiple rows in the same table and then commits them all at once.
I can't imagine a scenario (except in replication, or maybe remote
dial-up batching - but that's better handled by replication) where not
commiting each row after it's edited makes sense.
What a mess if the transaction fails on one or more rows! How can you
tell the user which things failed in each row? How can the user deal
with making corrections and reposting those rows? Do they have to repost
even the rows that didn't fail? Sorry, call me "pendantic" but my users
edit only one row, then save it (update, commit), then go on to the next
one.
Please explain where multi-row editing is necessary (I'd like to know).
JSB
Breck,
Are you differentiating betweeen the PB autocommit part of a transaction object
and the ASA autocommit option?
Aren't they the same thing?
Breck Carter
JSB <jsb...@niia.net> wrote:
>Breck,
>
>Are you differentiating betweeen the PB autocommit part of a transaction object
>and the ASA autocommit option?
>Aren't they the same thing?
No, ASA doesn't actually *have* an autocommit option per se.
There is an ODBC option, as in...
SQLSetConnectOption( dbc, SQL_AUTOCOMMIT, FALSE );
There is a JDBC option, as in...
conn.setAutoCommit( false );
There is an implicit "autocommit action" carried out by Data
Definition Language statements such as CREATE TABLE but that's not
optional, and it is not affected by any other options. There is also a
complex discussion of "autocommit" and the FORWARD TO command but
that's a whole other alternate universe;
There is the notion of "autocommit mode" achieved by setting the
CHAINED database option to OFF, but chained/unchained is actually a
different topic. The word "autocommit" in this context just serves to
confuse. In particular, ODBC AUTOCOMMIT is independent of SQL Anywhere
CHAINED. Nevertheless, "autocommit" and "unchained" are often treated
as synonymous, even in Sybase documentation; in particular see
"Setting autocommit or manual commit mode" in the ASA 6 Help.
There is an AUTO_COMMIT option but it only applies to ISQL and DBISQL.
In other words, AUTOCOMMIT is very much a client/interface option
rather than a server or database option.
Personally, I am not sure WHAT PowerBuilder's AUTOCOMMIT does,
exactly, with SQL Anywhere. I think the PB Help is actually wrong; in
any event, it is hopelessly confusing because it sometimes talks in
general terms whereas ODBC AUTOCOMMIT processing depends very much on
the DBMS and interface in use.
In particular, I have been told that you cannot successfully do an
EXECUTE IMMEDIATE BEGIN TRANSACTION - update - EXECUTE IMMEDIATE
ROLLBACK TRANSACTION sequence with SQLCA.AutoCommit = TRUE. This is a
common technique with SQL Server, and the PB Help implies it works
with ODBC, but as I said, I've been told it doesn't work with SQL
Anywhere.
That doesn't matter, though... it's beginning to look like
SQLCA.AutoCommit should never ever by TRUE for SQL Anywhere. Ever.
Breck
Breck Carter
Mike Packard <mpac...@mail.sandi.net> wrote:
>Breck,
>
>I'm not sure you understood my question. I was questioning the viability
>of a user editing multiple rows in the same table as a single
>transaction.
Sure, happens all the time... a tabular DataWindow used for updating
many rows at once, then saving them.
>I do not have any apps nor have I worked on any apps where the user
>edits multiple rows in the same table and then commits them all at once.
Ok... I guess experiences differ (note the "s", I'm not being arrogant
:)
>I can't imagine a scenario (except in replication, or maybe remote
>dial-up batching - but that's better handled by replication) where not
>commiting each row after it's edited makes sense.
>
>What a mess if the transaction fails on one or more rows! How can you
>tell the user which things failed in each row? How can the user deal
>with making corrections and reposting those rows? Do they have to repost
>even the rows that didn't fail? Sorry, call me "pendantic" but my users
>edit only one row, then save it (update, commit), then go on to the next
>one.
Well, basically you don't bother. Updates don't fail, usually. Servers
go down, so do connections, but it's rare that three out of seven
updates fail in a single transaction. So just make the user do it all
over again... that works in the real world because it almost never
happens.
Actually, ALL the rows fail because of the rollback. If the user can
figger out what's wrong, then it gets fixed. If it's a "row changed
between retrieve and update", it's time to do it all over again; i.e.,
retrieve and re-edit everything.
>Please explain where multi-row editing is necessary (I'd like to know).
Entering an order and all its lines. Committing halfway through and
going home results in a half-completed business transaction. It
doesn't match reality (the customer's expectations).
Now that's MY interpretation of a "business transaction". Others may
differ. But it IS common.
Breck
Jim Egan
I work with very few, if any, applications where a user only edits one
row from a table at a time. The Logical Unit of Work handles
the transaction for one or more DataWindows, DataStores or whatever (and
as such, multiple tables are involved in a single transaction). In PB
you can indeed determine which row failed and notify the user of it,
though it is a tad bit more work. But not much.
This subject sounds like a good one to cover in the class that I'm
putting together. Thanks for bringing it up.
--
Jim Egan [TeamSybase]
Dynamic Data Solutions, Inc. http://www.dyn-data.com
Houston, TX
Jim Egan
I'm not too sure that I agree with your 'fertilizer' position.
If a user is allowed to select data from an uncommitted update then he is
getting the data that another user is in the process of committing or
rolling back. In a sense the user will be looking at data that is
"expected" to be there. It may or may not end up being there when the
user attempts to update it themselves but there is the "expectation" that
it will be that way.
Now, some would argue that it SHOULD be difficult to read data that is
uncommitted. I agree with this for sure. Transactions are supposed to
be tiny things that happen in the blink of an eye. An open transaction
is an invitation to contention and pissed off users.
Anyhow, back to the main argument. Let's say user A does read
uncommitted data. Then user B, that had the open transaction, decides to
roll it back. Now, when user A tries the update it should fail if the
DataWindow update properties are set properly. If user B had committed
then user A's update would have proceeded just fine.
What happens if user B never committed and the transaction is still open?
User A is either going to sit there and wait for B to commit/rollback.
Or, depending on the db settings, the update will fail right away with a
blocking error. (Once again, this scenario should almost never happen.)
Mike Packard
>> Sure, happens all the time... a tabular DataWindow used for updating
>> many rows at once, then saving them.
Since I don't like grid-based editing it never "happens" to me.
>> Updates don't fail, usually.
>> that works in the real world because it almost never happens.
Okay...
>> If the user can figger out what's wrong, then it gets fixed.
>> If it's a "row changed between retrieve and update",
>> it's time to do it all over again;
Hell hath no fury compared to an angry user. My experiences have led me
to designs that minimize user-revolts.
>> Entering an order and all its lines.
I see what you're saying. It's fast, at the expense of good interface
design.
Mike Packard
Interesting points you bring up. So the difference between Iso 0 and Iso
1 is just the commit statement? The data doesn't really change?
When use Iso 1 then?
I'm assuming it's impossible to "see" a row while it's being written,
therefore there is no danger of reading half-written rows, right?
Jim Egan
I think that the Isolation Level has to be zero or one for what I
described. And no, you should not be able to read a row that is being
written to.
Paul Horan
'Good interface design' is in the eye of the beholder... Do you mean to
tell me that even your code table maintenance apps are free-form single-row
dw sheets? That application just screams for grid-style maintenance, and I
can't imagine deploying it in any other fashion.
Paul
Mike Packard <mpac...@mail.sandi.net> wrote in message
news:378371EF...@mail.sandi.net...
Breck Carter
On Wed, 7 Jul 1999 14:58:57 -0400, "Paul Horan"
<pa...@NOSPAMmindspring.com> wrote:
>'Good interface design' is in the eye of the beholder... Do you mean to
>tell me that even your code table maintenance apps are free-form single-row
>dw sheets? That application just screams for grid-style maintenance, and I
>can't imagine deploying it in any other fashion.
I don't think the "good design" value judgement applied to the
single-row-commit debate, I think he was talking about the nastiness
that is possible with optimistic concurrency failures.
On the OTHER HAND, if I'm wrong, I'll be buying Mike TWO beers at the
conference, one for him to drink and one for me to pour over his head
<g>
Breck I Can Dish It Out But I Can't Take It So Watch Out
matt...@proam.com
To answer your question "Any reason to use AUTOCOMMIT=TRUE for SQL
Anywhere?", I ran the following test:
PowerBuilder trace turned on (sqlca.dbms = 'trace odbc')
ODBC trace turned on (via ODBCAD32.EXE)
Test 1 of 2: set sqlca.autocommit = true
Test 2 of 2: set sqlca.autocommit = false
The Test:
using PB 6.5.1 Powerscript
connect to SQLA 5.5.04
update table set column = 'good value'
commit
update table set column = 'bad value'
rollback
select column from table
Results:
I then compared the output from the Powerbuilder trace and the ODBC trace.
With sqlca.autocommit = false, the ODBC trace shows that Powerbuilder
issues the SQLSetConnectOption to turn SQL_AUTOCOMMIT off after connecting
to the database. The PowerBuilder trace indicates this with the "BEGIN
TRANSACTION" log entry. On disconnect, the ODBC trace shows that
Powerbuilder issues a SQLTransact ( <SQL_ROLLBACK> ) and issues the
SQLSetConnectOption to turn SQL_AUTOCOMMIT on.
Therefore, if you set AUTOCOMMIT=TRUE, you better use SQLSetConnectOption
to set SQL_AUTOCOMMIT off.
Other than that, there is no reason to use AUTOCOMMIT=TRUE with SQL
Anywhere.
Mike Packard
>> Do you mean to tell me that even your code table maintenance
>> apps are free-form single-row dw sheets?
>> That application just screams for grid-style maintenance,
>> and I can't imagine deploying it in any other fashion.
You don't have to imagine it after seeing my app.
In fact, I can't imagine deploying it in any other fashion.
For example:
When a user highlights a row in a code table that has "usage", depending
on the app, I have to (a) warn them they are changing code in use, or
(b) don't allow changing
In the case of deletion (code in use), I definitely either (a) don't
allow deletion, or (b) force replacing code with another.
Using uneditable listing on one sheet with "Add, Edit, Delete" buttons
and single sheet for actual editing, this interface is easy to program
and easy for user to grasp.
How is any control maintained on edited-grid-style? This must either
(a) require more code for control or (b) have non-existant control -
(let-the-user-shoot-himself-in-the-foot type control).
I don't even like to use the grid-editing myself, because I constantly
have to be looking for where the cursor is if I get distracted, and it's
too easy to make errors.
Mike Packard
Guess I'll be bringing a waterproof (beer-proof?) hat to the conference.
Breck Carter
This is terminal thread drift <g>... but it's a great topic for the
Dolphin lobby bar in August.
Listen, I just don't happen to agree on *this* point, but I see your
point, and can appreciate your position. After all, I use (and swear
by) Wordpad as a web authoring tool.
Yes, I finally upgraded from Notepad :)
Breck
Mike Packard
Thank you. And see you in the Dolphin lobby bar.
Nina Lio
But why did you start out with AUTOCOMMIT=TRUE when
you do an explicit BeginTran, which in any case forces autocommit to
be disabled??
Nina.
Jim Egan
So you are actually going to make it to the conference finally?
Paul Horan
Transaction object.
All it really does (for SQLA 5.5.x) is flip autocommit from TRUE to FALSE,
which starts a transaction. The corresponding EndTran() function does a
hard COMMIT or ROLLBACK, and then flips autocommit back to TRUE.
So, in summary, we NEVER issue a database UPDATE with autocommit set to
TRUE. That setting is reserved for SELECT statements only.
Paul
It does not do an 'Execute Immediate Begin Transaction'.
Nina Lio <h...@controlit.no> wrote in message
news:01bec990$375ba8a0$ab5a4382@DESKPRO_5120...
Mike Packard
No, I was just dreaming. I guess that beer I owe you is getting pretty
stale. If the conference ever comes to San Diego I'll definitely be
there.
Jim Egan
Yeah, sure. It was in L.A. last year and you came up with another lame
excuse then.
JSB
--
John Brown [CPDA]
Munique Vision Corp.
Merrillville, IN
--
Mike Packard
Yeah... that's it...
;-)
Jim Egan
Mike owes me at LEAST a six pack from back in the CompuServe forum days.
My first conference was AWESOME! I soaked up so much knowledge that at
the end of each day I was drained. It is a total geek-fest. The
training is very good. In four days you can get exposure to many
different technologies at different levels. Plus you get to meet
TeamSybase! <g>
Breck Carter
THIS year's conference will be especially interesting for several
reasons (besides all the usual PowerBuilder reasons)...
(1) The enterprise database folks will be there (ASE, formerly SQL
Server)... and talking about ASE 12!
(2) There will be a lot of ASA stuff, whereas it was almost completely
absent from the Los Angeles conference last year, and
(3) There will be fewer idiot vendors because the really dumb ones are
off chasing garbage products like SilverStream (culling the herd, an
audience participation sport! :)
Oh, man, I love thread drift! (and I *started* this one :)
Breck
Leo Tohill
>If the isolation level is 0, a SELECT statement against a row in table
>should in no way set a lock that prevents another user from updating that
>same row, right? As long as the isolation level is 0, and autocommit is
>false, a SELECT starts no transaction or locks, right?
Sorry, I've been away.
Your statement above is correct but too limiting. At isolev 1 you also have NO select locks.
Isolev 2 and 3 introduce certain index locks, and 3 can also introduce the equivalent of a table
lock.
Leo Tohill - Team Sybase
>> Please post in newsgroup, not via email <<
Leo Tohill
The difference between isolev 0 and 1 is that 0 allows you to read uncommited changes, and isolev 1
does not. That's it.
Notice that there's a change in perspective: changing between 0 and 1 in no way affects the kinds of
locks that are placed. However, changing from 1 to 2 or 3 does affect what kind of locks are
placed.
Jay Fienberg
Thanks for your responses Leo. I am now autocommit=false as we speak!
Jay
Michael F. Nicewarner [TeamSybase]
And don't forget that PowerDesigner 7.0 will be announced and demonstrated.
The first complete re-write of the tool ever, and they've added an OOM
module so you can reverse and forward engineer PowerBuilder (or Java) code
and link it to the database design.
--
Michael F. Nicewarner [TeamSybase]
mailto:mike.ni...@ibpinc.com
http://www.datamodel.org
Breck Carter wrote in message <3786d538...@forums.sybase.com>...
Mike Packard
>> Mike owes me at LEAST a six pack from back in the CompuServe forum
days.
I can't deny that. Man, you have a good memory.
One of these days I'll pay up.
Maybe we can get Breck to be "designated driver".
Mike Packard
The confusing thing about this is for those of us who work with *other*
db's. Because the rules can and do change.
I think that's one of the pitfalls of capitalism, until the marketplace
shakes out a standard we are faced with multi-pleuralistic-databasism.
Jim Egan
Have you ever met Breck??? No way would I let him drive. He's the guy
that had a Road Rage heart attack. And I am quite thankful that he has
recovered from it!!