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
>

INSERT or UPDATE syntax?

Oracle and a few other DB's contain a VERY nice "INSERT OR UPDATE" feature.
In my SQL Server code I have to have large IF blocks in order to replicate
this -- in a trigger or stored proc you don't know in advance which one
you're going to have to do.
Am I missing something here? Is there an easy way to replicate this feature
in SQL Server?
Maury
Nope, it's not there. Send a request for this feature to:
sqlwish@.microsoft.com
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DC54237D-E872-45A1-86F2-48F29665F9BD@.microsoft.com...
> Oracle and a few other DB's contain a VERY nice "INSERT OR UPDATE"
feature.
> In my SQL Server code I have to have large IF blocks in order to replicate
> this -- in a trigger or stored proc you don't know in advance which one
> you're going to have to do.
> Am I missing something here? Is there an easy way to replicate this
feature
> in SQL Server?
> Maury
|||There is no UPSERT or MERGE.
http://groups.google.com/groups?hl=e...phx.gbl#link1
http://groups.google.com/groups?hl=e...phx.gbl#link1
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DC54237D-E872-45A1-86F2-48F29665F9BD@.microsoft.com...
> Oracle and a few other DB's contain a VERY nice "INSERT OR UPDATE"
feature.
> In my SQL Server code I have to have large IF blocks in order to replicate
> this -- in a trigger or stored proc you don't know in advance which one
> you're going to have to do.
> Am I missing something here? Is there an easy way to replicate this
feature
> in SQL Server?
> Maury