Multi threads

I have an app that is critical to our business. It handles and syncronises several SQL Servers, checks integrety etc. I need to make the app so it can run a few things at once. Does anyone have any experience with this? Currently we use Delphi and ADO. I have been fiddling with DMO to get more performance - I am not sure ADO is very quick for some I tasks I need to do.

I suppose my main question *really* is does ADO/DMO multi-thread and has anyone tried it. If not how do people do it?Yes, ADO emphatically multi-threads. I regularly run one app that for a few moments has 30+ threads running with no problems at all.

Then again, I'm well known for doing extremely perverse things with multi-threading (including a couple of multi-threaded batch files!).

-PatP|||That's interesting, is there anything you would recommend that I do/not do?

Also, do you have to use client cursors? I tend to find using more than one and I ger problems generally.|||Use connection pooling!!!
Use a separate connection object for every active recordset.
Close every connection as soon as you are done using it and free any memory it is/was using.
Keep your recordsets as small as practical (more threads, more network traffic, more contention).
When referencing database objects, do so strategically to avoid the app blocking itself

-PatPsql

Multi Controller

For large databases backup the databases in to multiple backup devices
spanned across multiple disk controllers.
This will create multiple threads during backup right...
what is the command like let's assume we have three controller C: D: E:
the backup command we can write like
backup database DBName to disk = c:\DBDirectory
How can I take the backup into the multiple backup devices on the spot.
Thanks
Hi,
1. Create seperate Backup devices pointing to each controllers using
sp_addumpdevice system stored procedure.
2. Backup the database using Backup database command mentioning all the
devices
Backup database <dbname> to db1_control1,db1_control2 with Init
with init option will overwrite the backup files
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:19D4728B-E399-4E14-AE78-213371431378@.microsoft.com...
> For large databases backup the databases in to multiple backup devices
> spanned across multiple disk controllers.
> This will create multiple threads during backup right...
> what is the command like let's assume we have three controller C: D: E:
> the backup command we can write like
> backup database DBName to disk = c:\DBDirectory
> How can I take the backup into the multiple backup devices on the spot.
> Thanks
|||Just a note that you don't need dump devices to backup to multiple files.
You can specify the filename directly as well.
Andrew J. Kelly SQL MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OR1YS4qOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi,
> 1. Create seperate Backup devices pointing to each controllers using
> sp_addumpdevice system stored procedure.
> 2. Backup the database using Backup database command mentioning all the
> devices
> Backup database <dbname> to db1_control1,db1_control2 with Init
> with init option will overwrite the backup files
> Thanks
> Hari
> SQL Server MVP
>
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:19D4728B-E399-4E14-AE78-213371431378@.microsoft.com...
>
|||Backup database <dbname> to db1_control1,db1_control2 with Init
Is that means that the same backup will take place into two cotroller or
partially one and partially into another.
"Hari Prasad" wrote:

> Hi,
> 1. Create seperate Backup devices pointing to each controllers using
> sp_addumpdevice system stored procedure.
> 2. Backup the database using Backup database command mentioning all the
> devices
> Backup database <dbname> to db1_control1,db1_control2 with Init
> with init option will overwrite the backup files
> Thanks
> Hari
> SQL Server MVP
>
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:19D4728B-E399-4E14-AE78-213371431378@.microsoft.com...
>
>
|||Hi,
Partiall backup files will be send to each drives specified in Backup
database command
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:F17B751D-4C3D-4D1B-8294-508C4286E333@.microsoft.com...[vbcol=seagreen]
> Backup database <dbname> to db1_control1,db1_control2 with Init
> Is that means that the same backup will take place into two cotroller or
> partially one and partially into another.
>
> "Hari Prasad" wrote:
|||Thanks HARI.
"Hari Prasad" wrote:

> Hi,
> Partiall backup files will be send to each drives specified in Backup
> database command
> Thanks
> Hari
> SQL Server MVP
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:F17B751D-4C3D-4D1B-8294-508C4286E333@.microsoft.com...
>
>

MTS Threads

I am wondering how to determine the the number of threads an MTS object can handle, and where you can view this information in the mts explorer.

Thanks

Mike

You should be posting the MTS group not SQL.

http://support.microsoft.com/kb/282490/

MSSQL ODBC difference compared to MySQL

The source for this problem can be found http://www.wellytop.com/SQLProblem.zip

This test creates two threads each with a database connection and uses transactions to insert values into the same table.
The objective of this test is to check that a thread cannot read the results from a pending transaction on a different thread.
In effect this checks dirty reads do not happen and transaction locking.

The test runs correctly and displays "PASSED" with MySQL indicating the transaction and threading worked.
When running with MSSQL Express 2005 it reports a deadlock error during a transaction.
It's not really possible to re-run the transaction and I would like MS SQL to operate similar to MySQL, i.e. MySQL waits for the other transaction to finish before the next transaction can operate on those table rows. I'd like to use MSSQL but I am wondering why this error doesn't happen with MySQL and so have, for the moment, chosen to use it as my preferred database solution.
I have experimented with transaction isolation levels and this doesn't seem to solve the problem.

I've tested this with a fresh install of Windows XP SP2 and no firewall turned on.

To run this test with MSSQL Express2005 use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named MyExpressTest and attach this to an empty database that has been created with the default values. Enable the #define MSSQL in the coude otherwise it tests with MySQL.

To run this test with MySQL (to show how this test should work) use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named mySQLNewTest and attach this to an empty database that has been created with the default values. Comment out the #define MSSQL to switch to MySQL mode.

Replying to my own old post is a bad sign, it's one step away from talking to myself.

However that said I thought it would be useful to summarise my findings. The problem above is a limitation of the MSSQL ODBC interface. Basically, MSSQL ODBC connections with transactions from different processes work whereas if two or more connections come from the same process there is no transaction waiting. The workaround is to not use more than one MSSQL ODBC connection from each process. An alternative would be to use something like ADO instead, but that would break the example given in the problem where the code is meant to be cross platform and have a choice of database.

|||

Hi Martin,

After running your repro and experimenting with sqlcmd, I can see the same thing happening in sqlcmd with two separate processes running your statements in transactions. If I set the transaction isolation level to 'snapshot', the problem does not surface and each thread sees only the rows that existed before their transaction started or were inserted during the transaction, so perhaps snapshot isolation is for you.

For reference, I ran sqlcmd twice and executed:

SETUP:

create table t2 (id integer identity primary key, thing integer)

WINDOW 1:

-

begin tran

go

insert into t2(thing) values(10)

insert into t2(thing) values(14)

insert into t2(thing) values(15)

go

select * from t2

go

WINDOW 2:

begin tran

go

insert into t2(thing) values(100)

insert into t2(thing) values(110)

insert into t2(thing) values(120)

go

select * from t2

go

However, setting the transaction isolation level to 'serializable' seems as though it should also address your issue; but it does not.

From looking at the locks that get held, it is clear that thread 1 and thread 2 each obtain an exclusive lock on a subset of the rows in the table, then try to obtain a shared lock on the whole table to select. Since each holds an exclusive lock on a subset of the table, they reach deadlock. Effectively, the isolation level needs to be set such that they do not see each other's rows during the transaction so they can read without locking the table -- or what they see as the table. Snapshot isolation level clearly does this for you, but one would expect serializable to do it as well.

In order to ensure that your question is addressed by experts in this particular domain, I am transferring this question to T-SQL forums.

I hope this helps,

John

|||

Thank you very much for your extremely helpful answer. I just tried the test source here (configured for MSSQL) and found your suggestion does indeed solve the problem without any code changes needed.

After a bit more digging and searching for ALLOW_SNAPSHOT_ISOLATION this link http://msdn2.microsoft.com/en-us/library/tcbchxcb(vs.80).aspx describes the same solution.

So with the RNLobby database created (or in a new create database script) I then issue this command:

ALTER DATABASE [database name] SET READ_COMMITTED_SNAPSHOT ON

GO

Turning this option on by default for all connections means the code doesn't need changing to enable the snapshot isolation level for each connection which is perfect for this test case but may not be perfect for everyone.