Locking Issues When Updating Remote Table

May 21, 2008

Hi everyone

I have a Stored Procedure mySP that updates a table in a linked server.
The same table is also being updated by other processes in the remote server

Usually mySP updates 10-20 records at a time, and that works fine.

BEGIN TRANSACTION

......

......


UPDATE [REMOTE_SERVER].[DB].dbo.[REMOTE_TABLE] SET VALUE = 2

WHERE ID IN (SELECT ID FROM LOCAL_TABLE)

COMMIT

The problem begins when mySP tries to update 100+ records, it gets a time out probably due to locking issues
(indexes are properly set in both remote and local tables )

I read some articles where they suggest to break the transaction in several batches of a smaller size to avoid locking issues.

Something like:

BEGIN TRANSACTION

.....

.....


DECLARE @Rows int

DECLARE @Batchsize int

SET @Batchsize = 10

SET @Rows = 1

WHILE (@Rows > 0 )

BEGIN

UPDATE [REMOTE_SERVER].[DB].dbo.[REMOTE_TABLE] SET VALUE = 2

WHERE ID IN (SELECT TOP (@Batchsize) ID FROM LOCAL_TABLE)

DELETE TOP (@Batchsize) FROM LOCAL_TABLE

SET @Rows = @@ROWCOUNT

END

COMMIT


But it seems to make no difference.

Any ideas/suggestions?

Thank you

View 2 Replies


ADVERTISEMENT

Deleting, Updating And Inserting---is It LOCKING The Table?

Aug 17, 2006

I have created a single Data Flow Task that reads a set of records from a source table and then makes determinations whether to insert, update or delete from a destination table. The data is basically being copied from one database to another with a small amount of data manipulation and lookups. The problem seems to be that when running the task, even a small amount of records read from the source table seem to take a long time for the task to finish. I feed the records into a Script Component (the brains) that sorts the records to three separate outputs. I use OLE DB Commands to perform the DELETE and UPDATE and an OLE DB Destination for INSERT. I thought that by using three separate database connections would help, but it just appears to be locked while trying to perform these commands against the same table.

Is there a way to control or route these three record sets in such a way as to perform them sequentially?

I know it's a bit of a simple question for some of you, but I'm just learning SSIS (but I like it!).

Thanks!

Jeff Tolman
E&M Electric

View 3 Replies View Related

Updating Remote Table With OPENDATASOURCE

May 25, 2008

Greetings all

Have another issue with OPENDATASOURCE

The following SQL appears to run successfully:
Update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=******').TRANSFERSTN.TSADMIN.SALESDAY
set STARTDTTM = L.STARTDTTM,ENDDTTM = L.ENDDTTM,CLOSED = L.CLOSED,
CASHEDUP = L.CASHEDUP,CASHINTILL = L.CASHINTILL,CHEQUESINTILL = L.CHEQUESINTILL,
EPSALES = L.EPSALES,EPCUTORETURN = L.EPCUTORETURN,REPFLAG = R.REPFLAG
from OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=******;Password=*****').TRANSFERSTN.TSADMIN.SALESDAY AS R inner join
TSADMIN.SALESDAY as L on R.SALESDAYID = L.SALESDAYID where L.REPFLAG = 1


However, all records in the remote table get updated if there are any records satisfying the REPFLAG clause, if more than 1 then I guess the last record in the source set.

Running the same SQL against 2 tables in the local DB works perfectly.

These are both MSDE V8.00.2039 SP4 connecting via RAS dial up.

Any ideas where to focus my attention will be gratefully received


Thanks

<EDIT>
Haven't solved the issue, but staggered around it. I have simply reversed the tables in the SQL and executed at the remote server instead, so it is updating a local table while getting data from a remote table. This functions as it should. Any clues to the above would still be good tho' - cheers
<END EDIT>

View 2 Replies View Related

Remote Data Updating

Apr 25, 2008

Hello all, quick question and then a little background.

Could someone point me in the right direction when it comes to updating remotely deployed instances of a SQL CE database? I don't expect someone to code my project for me, but I'm getting lost in the sea of information and haven't found anything that looks like what we need yet.

What's going on is that I was given the wonderful opportunity to learn SQL.NET programming from the other side of application development. I'm not a beginner programmer by any means, but kind of a newbie when it this level of SQL deployment. What we are trying to do is give our users applications that use a local instance of the larger SQL database we have on the server. We were thinking that deploying SQL CE databases created from the master DB would be a good solution since it is supposed to have updating capabilities, which would be wonderful since then the users wouldn't have to be burdened by having to manually download and update a huge database install file every time we updated the information in the master.

What I'm finding though (after going throught the tutorials), is that the RDA feature (this would only need a pull function) that we were hoping to use requires that you delete the entire table, and then replace. If the master DB has only a few record changes in several scattered tables, it seem that this operation would be similar in size and resource demands as just re-downloading the entire database again (~2.7 gigabytes and growing).

Is there a function that can use a feature on the field properties that can download and replace only those fields/records that have changed intead of basically replacing the entire database?

Sorry if this seems like a painfully obvious question, but I have been looking for a while and haven't been able to find it.

thanks a ton in advance!
Brian

View 4 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

Updating A Table By Both Inserting And Updating In The Data Flow

Sep 21, 2006

I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.

Any suggestions?

View 7 Replies View Related

Updating Local And Remote Databases

Jun 10, 2004

Hello everyone.

I'm currently trying to figure out a good way to keep my Local and Remote Databases in sync. Whenever I make changes to one or the other I spend a lot of time manually adjusting the other to match. I want to be able to set up an update page that accesses my Remote database and Local database and Updates the two accordingly.

I really don't know where to start with this one. I'm not sure at all as to how to simultaneously connect to two different databases, on two different servers, and Udate from one another. Does anyone know any good articles to get me started?

Thanks ahead for your replies.

-Alec

View 1 Replies View Related

Updating A Remote Database Copy

Apr 12, 2008

I have a SqlExpress DB on my server. I have a remote copy for security/backup reasons. Because the DB file(and .bak file) is about 2 Gb, it take a while to copy the whole thing to the remote machine. I believe there is a way to use the log file to simply update my remote copy ratherr than copying whole files, but I don't know what this process is called to research it. I have Brust's book on Sql Programming, but I have not found what I am looking for in there. Can anyone tell me where I should be looking and what this process is called?

thanks,
Steve

View 8 Replies View Related

Performance Problems Updating A Remote Database Using Triggers

Apr 20, 2005

I have a table that has triggers on insert update and delete. The triggers perform modifications on another table on a remote databased(linked server).
All triggers make a join between the tables inserted, deleted and the remote table.
When running a profile on the remote database it seems that the following query is done on the remote table by the triggers.

"Select * from Database.dbo.Table"

The triggers are the only entities accessing the remote table from the original server so the query must come from them. My only conclusion is that MSSQL server is doing this query for some kind of "optimization".

Has anyone seen this before?
How can I work arond this? The remote table is BIG and this query happening every few seconds in a problem for me.

Thanks

Robert

View 4 Replies View Related

Table Locking?

Feb 7, 2002

Gurus,

I am trying to execute this stored procedure when I try to change all occurences of a field in a table.

(
@Dept char(8),
@DDept char(8)

)
As
Set NoCOUNt On

Begin
Select '@DDept'
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
update phone set fo_dept = @Ddept where fo_dept = @Dept
End
GO

The table/database is being used by others, generally in a read only mode.
via a VB 5.0 FE program.

The Stored procedure, when it is invoked, just hangs like it is waiting for exclusive use of the table.

Is there a way around it, without doing major surgery on the VB code?

Thanks.

Sam

View 3 Replies View Related

Locking The Whole Table

May 24, 2002

Hello,
I am working on the implementation of a database for my company and I have
a simple (I hope) question to ask.
I have the following stored procedure that will be running when I want
(actually when I want to delete a customer).
Do not mind if you do not understand what this procedure does... ;-)
Actually the Level8View is a VIEW of a nested table
(CustomerData->CustomerData).

CREATE Procedure DeleteCustomer
@ClientID INT
AS
UPDATE Level8View
SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level1=@ClientID
UPDATE Level8View
SET Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level2=@ClientID
UPDATE Level8View
SET Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level3=@ClientID
UPDATE Level8View
SET Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level4=@ClientID
UPDATE Level8View
SET Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level5=@ClientID
UPDATE Level8View
SET Level6=Level7, Level7=Level8
WHERE Level6=@ClientID
UPDATE Level8View
SET Level7=Level8
WHERE Level7=@ClientID
DELETE FROM Customers
WHERE ClientID=@ClientID
GO

I checked this procedure and works perfectly.
What I want to do is to somehow lock the WHOLE TABLE (CustomerData) or the
view (Level8View) before the Update statements and unlock it after the
delete statements.
I do not want to do a Row by Row lock, or Page by Page lock since the updates
in this table occur in the whole table and during that operation I do not
want other threads to issue SELECT, INSERT or UPDATE statements.

Can someone answer me how I can lock and unlock the whole view or table
please?

I will appreciate it for your answer. Thanks.

Yours, sincerely
Efthymios Kalyviotis
ekalyviotis@comerclub.gr

View 4 Replies View Related

Locking Table In A SP

Nov 11, 2005

I have a SP for an e-commerce site that creates an order doing the following fashion:

begin
-Fill out some variables
*Critical Section
-Create Order number
-While loop select on order table and recreate if ordernumber already exists
-Insert into order table
*End Critical Section
-Insert into order lines table, address table, etc.
commit

The problem is the Create Order number and While loop used to be at the top and occasionally I would get duplicate order numbers if two submitted at the same time. I rearranged it to the above and it happens less now.

My question is if there is a way to put an XLOCK on the orders table only during the critical section lines. Also my understanding is that once the insert into orders is done that the server will hold a lock until the commit in case of rollback.? I don't want to XLOCK the whole SP if I don't have to. It is quick though.

Thanks

View 3 Replies View Related

Table Locking

Apr 25, 2008

I need 2 unique sequential numbers to be stored in the same table. Obviously, for the first number I can use an auto-number field. For the second number, I would like to use a 2nd table with just 1 field that would only ever store 1 record. The idea being that if a user needs to generate a new unique number, they lock the 2nd table, lookup the current value, store the value in a variable for use, add 1 to the value of the number in the table, then unlock the table.

Presumably, I'd use something like this:

SELECT OrderNo
FROM tblUniqueOrderNumber (WITH TABLOCK)

Does this lock the table so no other users can read/write the table? If so, how do I unlock the table once I've updated OrderNo? What happens if another user then reads the table while it has a TABLOCK applied? I'm using SQL Server 2005 and the database is a multi-user database. I have a feeling I'm not going about this the best way possible, but I'd like to know how the table lock works eitherway.

View 12 Replies View Related

Table Locking

Apr 30, 2004

hi

can i lock my table while doing an updation so that all others in n/w can insert into the table only after my operation is completed?

if a user attempts to save/update the 'locked'table, will he get an error message or will his application (using ADO) will wait till the table gets unlocked?

View 2 Replies View Related

Locking A Table

May 11, 2004

Can any one please tell me how to lock & unlock a table a from inside a store procedure using T-SQL
thanks

View 6 Replies View Related

Locking Table

May 6, 2008

hi,
I am running a sql job that select data from one database (multiple table) and inserts into another database (1 table)

I have a problem with the way it is locking the tables I am selecting from !

my query is like :

insert into testdb.dbo.tabletest

select * from
livedb.dbo.tablex
inner join liveb.dbo.table1 on tablex.id=table1.xid


It seems to be locking the table and blocking other users from accessing the table.

suppose my question is why is the select locking the table ?

View 1 Replies View Related

Table Locking

Jul 20, 2005

I want to insert values into a table and the same time select a valuefrom the same table.I insert a record of a parent type and use a function to create asubscriberType for the record.Next i insert a record of child type and want to select thesubscriberType from the parent record.This do not work when i useINSERT TABLESELECT ,Name,"function to generate subscribertype",.............I DO NOT WANT TO USE CURSOR!!

View 1 Replies View Related

How To? - OpenQuery Table Locking

Aug 21, 2007

We have a MS SQL database with an Oracle linked server  'ALTTEST'
 We can Select, Insert, Delete and Update tables on the Oracle Db using OpenQuery, but how do I apply a table lock with a transaction?
I've tried applying the code below, but it doesn't work.
 Any help appreciated.
BEGIN TRAN
SELECT * from openquery(ALTTEST,'select LAST_PIN_NUMBER from sys_params') WITH (TABLOCKX)
COMMIT
 

View 5 Replies View Related

How Do I Find Out Who's Locking A Table? And Than Do Something About It?

Oct 3, 2007

So randomly every 1 to 6 days queries start timing out and I'm almost positive it's from an improperly terminated transaction
 Is there a way to snoop this out the next time it happens? Like when a table's locked I can look and see yea this is the transaction it's in the middle of?
 

View 6 Replies View Related

Locking A Table Before Query

Mar 25, 2006

Hi,
im developing an application for my university final year project and i want to lock the table before the query executes to ensure that no other changes are allowed (sorry if im confusing)
So my question , really does anyone know how to do this and is it possible ?, for example in coldfusion a query is surrounded with cflock or something similiar.
Appreciate any and all help guys
Many thanks in advance
andy

View 1 Replies View Related

Best Way To Exclusively Locking A Table

Aug 5, 1999

Hi

Is a TABLOCK HOLDLOCK in a transaction the best way for locking a table for a brief period of time?

We need to get generate a unique serial number each time a certificate record is printed. We have in place a field called Field_SERIAL_NUMBER for each certificate record, which may be printed 1 time or 100 times. Each time it prints, we will insert a unique number into the Field_SERIAL_NUMBER field.

This is what I'm thinking to approach this.
- Create a new table called Table_SERIAL with one field called Field_NUMBER which has only 1 record set to 1.
- Every time a certificate is printed, we
1. Begin transaction
2. Select @number = Field_NUMBER from Table_SERIAL with a TABLOCK HOLDLOCK
3. Insert that number into the Field_SERIAL_NUMBER field in the certificate table
4. Increment @number by 1
5. Update Field_NUMBER with @number in Table_SERIAL
6. Unlock table

So, everytime the Field_NUMBER is pulled, it should be unique and incremented by 1.

Does this approach sound good?

Joyce

4. Increment

View 2 Replies View Related

Delete Without Locking The Table?

Oct 20, 2005

Hi,

is there anyway i can run delete statements on an audit table that will not lock the whole table?
As it is an audit table, when i run the delete query, the whole application freezes until the delete query is complete.

the process is taking several minutes as the table is approx 40M rows, the where clause in the delete is indexed but thats it really.

any thoughts greatly appreciated by my system users!

View 5 Replies View Related

Locking Table While Insert

Apr 4, 2007

Hi,
I want to insert a record in a table having an identity column as primary key. I want to lock the table while inserting. so that no one should be able to insert, select, update, delete from the table. and once my insert is over, then will release the lock.


Can I have the code for the same. M using SQL SERVER 2005.



Thanks,
Rahul Jha

View 8 Replies View Related

SQLTransaction Locking Up Table

Jul 23, 2005

Hi All,I am using a SQLTransaction to insert records into a table. At onetime, there are 5000 or more records to be inserted one by one. Ittakes some 20-25 mins for the entire process to run.Another application accesses the same table.As long as the insert process within the transaction isn't completed,the second application is not getting any response from the server. Ieven tried to run a SELECT on the table in SQL Query Analyzer while theinsert process was running and it also did not respond till the timethe insert process finished!Is this normal that a transaction is locking up a table? How do Iovercome it? I am using IsolationLevel.ReadUncommitted for thetransaction.If I do not run the process within a SQLTransaction, the second processor running the SELECT in Query Analyzer does not hang.Thanks,Sanjeev Mahajan

View 3 Replies View Related

Locking Users Out Of Table

Jul 20, 2005

SQL Server 2000, MSDE 2000I have a procedure in my application that I would like only one user at atime to be able to run. Is there a TSQL command I can run that willesentially lock a set of tables so others cannot access the table until theuser is done with the procedure or until the user disconnects from thatsession (in case of a hung app, I would like to lock released)TIA--Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehiclerelated website.http://www.vehiclewebstudio.com

View 3 Replies View Related

Locking A Table Header Row

Oct 12, 2007

I feel like I read about a new ability in SSRS 2005 to be able to lock the position of a table's header row (when viewing through the report viewer) so that it does not scroll out of view when scrolling down long report. Now that I need it, I have not been able to find any information about it. Does anyone have any information about this feature or know if this feature exists?

View 1 Replies View Related

Table Locking In Partitioning

Aug 10, 2006

Hi,

I have the following doubt about table lockinglocking in case of partitioning:-

Say we have 5 partition on the table Employee on the key Joining_Date and when we run 5 select queries on each of the parition in parallel will there be locking on the table when the 1st query is running or all the 5 queries can run in parallel. Basically, I am trying to see if parallelism and partitioning can work in sync or there will be locking at the table level if I don't specify any query hints?

Any help in this regard is highly appreciated.

Thanks,
Ritesh





View 1 Replies View Related

Locking A Table For The Duration Of A Transaction.

Feb 20, 2004

We have a nightly script that drops and rebuilds a table. Problem is before the script has had a chance to complete the building of the primary key, it fails due to some other process gaining access to the table.

My question is this: how do I lock a table for the duration of the transaction.

Here is what I think would work:

SET ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

DROP TABLE <table>...
CREATE TABLE <table>...

INSERT INTO <table> WITH (TABLOCKX) ...

ALTER TABLE <table> ADD CONSTRAINT PRIMARY KEY ...

COMMIT

Any insight as to whether or not I am correct, or if I am not, what would be the correct way.

View 9 Replies View Related

Transact SQL :: Table Locking With TABLOCK

Oct 20, 2015

We currently have a large ETL import each night. There are lots of tables and some are quite large. If there is a problem with the nightly import, it is rerun during the day while users are running reports. The only updates to the tables is the nightly import.Currently the import does a BULK Insert and several INSERT INTOs. We are going to try to improve performance by adding a WITH (TABLOCK). But wonder if the TABLOCK will cause more locking, less locking, or be about the same. We do not have transactions batched. We insert all the records for a table in a single transaction. 

Have found these references regarding TABLOCK, but I am confused what they mean, and how they differ from doing a BULK INSERT or INSERT INTO without TABLOCK.

TABLOCK - Specifies that a shared lock is taken on the table held until the end-of-statement. [URL] ....
Using INSERT INTO…SELECT to Bulk Load Data with Minimal Logging [URL] ....
Lock Modes [URL] ....

View 7 Replies View Related

SSIS Source Table/Row Locking

Feb 8, 2008



Hi all,
We have an SSIS package which runs for around 30 min and it does a lot of things, now whenever this SSIS Package is run it is suspected that it is locing the table or records from the DB which it is reading the records, and indeed that system is having some fault that it is never able to remove that lock unless the DB restarts. In this source we only have a SQL Command and not a Table selected. Have anyone come across this problem? Any answers will be of great help.

View 2 Replies View Related

Locking And Unlocking Rows In SQL CE Table Manual

Nov 30, 2007

Hi all,

We have a scenario where we want to lock and unlock manually some rows in a table in SQL CE database .First the rows we need should be locked. By using a select query the locked rows will be read and processed in front end. Only if the process is completed in front end, manually I need to unlock rows which I have locked earlier. When rows are in locked state no body should be allowed to access that rows . ( should not allow anybody even to put a select query on that locked rows).
At the same time appending rows in the table should always be allowed.
Is it possible to achieve the above scenario?

View 1 Replies View Related

Locking And Unlocking Rows In SQL CE Table Manual

Nov 30, 2007

Hi all,

We have a scenario where we want to lock and unlock manually some rows in a table in SQL CE database .First the rows we need should be locked. By using a select query the locked rows will be read and processed in front end. Only if the process is completed in front end, manually I need to unlock rows which I have locked earlier. When rows are in locked state no body should be allowed to access that rows . ( should not allow anybody even to put a select query on that locked rows).
At the same time appending rows in the table should always be allowed.
Is it possible to achieve the above scenario?

View 1 Replies View Related

Read, Modify Table (locking) Question

Feb 3, 2006

I am quite new to MS SQL, and I want to read rows from a todo table, andwhen a row has been processed, I want to delete that row.Ages ago in MySQL I would probably have locked the table, select, processa row, delete a row, unlock the table.I have been reading through the documentation from MS SQL, but it's notclear what exactly I should do.Since I want to lock only one table in the select (the others just providedata, and are not modified), what's a good solution?--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved