FreeTDS Development Group

["Pickett, David" <David.Pickett AT phlx.com>]

Apology for being off-forum-topic is appropriate and reiterated, but in this
world, help is where you find it.  Locks, big churn and interactive users
present classic problems.

It sounds like there is a problem with the RDBMS (not likely) or your
completeness of (count of) commit if you have to disconnect to get the
locked select running.  Maybe you should send something like a "while (
trancount > 0 ) commit".  You might be in a fatal embrace with a really long
deadlock timer, where you want to update what they locked, they want to read
what you locked.  That depends, as was noted, on the transaction controls of
both sessions.

Assuming a table lock, and that mssql and Sybase have remained essentially
the same in this area, initially table locks depend on whether your target
table is a row or page locking type, and how many rows or pages you lock,
and maybe some settings on the server side as to that threshold, controlling
when your churn escalates from page/row locks to table locks.

If you must do a big churn in one trans, and cannot avoid a table lock, you
can minimize the time by: a) doing it table to table (tempdb staging tables
are good for this), b) all at once in one batch, so networked interactions
are minimized, c) with good index support on one table or the other (but
sometimes it's faster if you add a supporting index on the staging table
than adding another index on a churn target table).  Sometimes, you can
improve the index situation by enhancing your staging table ahead of the
churn from the target table.  Oracle Row IDs are great for this, but I am
not sure mssql has the equivalent.  Of course, this assumes local
quiescence: nobody else will changes the key values while you are staging.

Sometimes you can cut your churn into batches yet preserve integrity by
dividing the data along logical data lines, doing accounts beginning each of
(make a list of prefixes) and committing them, so the partially updated data
set has integrity on every customer.  If course, with partial updates,
planning for restarts becomes a new challenge.  Working in batches can speed
things up, in total, by locality of reference (running at cache / RAM speed
not disk speed).

An insert-only data set (history where you find status using key and
timestamp or sequence) means all churn is inserts, old rows are not locked,
and churn often goes faster (no where clause and space release with
insert-only).  It also means no data is lost due to errors by programmers
and users, especially when the product is new.  Space control by deletes can
be in one careful, archiver process.  Similarly, inserting leaf to root
means you need no transactions, as aborts just mean there are orphans left
behind, which a well designed archiver will clean up.

Best regards,

David

-----Original Message-----
From: Robert Klemme [mailto:shortcutter AT googlemail.com]
Sent: Tuesday, January 24, 2006 1:46 PM
To: FreeTDS Development Group
Subject: Re: [freetds] handling multiple users on the database


2006/1/24, Lawrence Chu <lrychu AT yahoo.com>:
> Hi All,
>
> I am a newbie when it comes to TDS and handling sql
> databases, so bear with me if this seems obvious ...
>
> I have a mssql database and I am using freeTDS vers.
> 8.0.  I have heard that mssql can handle multiple
> users on the database as long as there isn't conflict
> on the same cell at the same time.  I am also using
> transactions.
>
> My problem is that when experimenting with deleting
> and loading massive amounts of data (within
> transaction), and another user is trying to retrieve
> data at the same time, both ends seem to freeze up,
> until one or the other gets off the database.
>
> 1) how do I check where the conflict is happening

You can use Enterprize Manager to check for locks and lock
dependencies. Before you do anything else you should pinpoint the
actual cause of the behavior you're perceiving.

> 2) what can I do to ensure that it does not freeze up
> when there are multiple users (configurations,
> settings, etc).

One thing you can do is to change the transaction isolation level of
one or both transactions so a tx can see changes done by another tx. 
However, you should know what you are doing.

Another thing you can do is to explicitely set locks (dunno how that
is exactly done with sql server, but BOL will help youl). That way you
might be able to prevent deadlock situations. (Note though, that sql
server will kill one of two tx that participate in a deadlock in the
db; in that case your apps would not freeze but one of them would bail
out. However, you can also produce dead locks that sql server cannot
detect via application code.)

But the main thing to do is to properly design the application to
prevent these situations. This is development of concurrent
applications which is almost never trivial.

Before I forget it: there's always the option of a bug in freetds that
somehow causes the behavior you're seeing. But given the facts we have
I rather not bet on this option - I guess it's one of the other
causes.

> If this is not the right forum to raise this question,
> or if my questions are just too vague or general,
> please let me know.  I'd appreciate any hints as to
> where to begin.

It's ok - as long as you can live with rather unspecific answers due
to the fact of the limited detail we have. :-)

Kind regards

robert
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds