Insert Table Lock - Various connections inserting into one table

I have three connections from oracle DBs which all insert into 1 SQL
Server 2000 table. About 1 million rows in all. The problem is only one
connection is inserting at one time... then the second... then
third.... The first connection obtains a table lock causing other
connections to wait until it completes...should'nt they run
simultaneously'...My DBA recently changed the from FULL to Simple
recovery model... does that has anything to do with how insert table
locks are being handled' If I was on simple recovery model....
would I be able to insert into one table from three different
connections or SQL statements?Hi Zomer
The recovery model should not affect the locking.
How are you determining that a table lock is being acquired?
Are the connections doing more than inserts? Are they starting an explicit
transaction? (Look for BEGIN TRAN)
What isolation level are you in? Have you enabled implicit transactions?
(Run DBCC USEROPTIONS on the connection)
Have you disallowed page and row locks (check indexproperty function)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"zomer" <noneee@.gmail.com> wrote in message
news:1146586395.557732.192510@.u72g2000cwu.googlegroups.com...
>I have three connections from oracle DBs which all insert into 1 SQL
> Server 2000 table. About 1 million rows in all. The problem is only one
> connection is inserting at one time... then the second... then
> third.... The first connection obtains a table lock causing other
> connections to wait until it completes...should'nt they run
> simultaneously'...My DBA recently changed the from FULL to Simple
> recovery model... does that has anything to do with how insert table
> locks are being handled' If I was on simple recovery model....
> would I be able to insert into one table from three different
> connections or SQL statements?
>|||I run sp_lock to find out if a table lock was acquired or not. I am
doing it in DTS and I can see one connections getting data while others
are waiting for it to get done (due to table lock)
How do I check for Transaction isolation level? When I run DBCC
USEROPTIONS I dont see any informaton about transactions...
No I have not disallowed page and row locks...
A few days ago this DB was changed to simple from full recovery model
by DBA|||If DBCC USEROPTIONS doesn't mention transaction isolation, it means you
haven't changed it from the default, but you have to be running it from the
connection doing the insert.
Did you run sp_indexoption to verify that page and row locks are allowed?
Can you post the specific information from sp_lock that is showing the table
lock?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"zomer" <noneee@.gmail.com> wrote in message
news:1146590297.747832.125830@.i39g2000cwa.googlegroups.com...
>I run sp_lock to find out if a table lock was acquired or not. I am
> doing it in DTS and I can see one connections getting data while others
> are waiting for it to get done (due to table lock)
> How do I check for Transaction isolation level? When I run DBCC
> USEROPTIONS I dont see any informaton about transactions...
> No I have not disallowed page and row locks...
> A few days ago this DB was changed to simple from full recovery model
> by DBA
>