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


ADVERTISEMENT

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

Can We Specify Datarow Locking In Create Table Statement

Sep 24, 2007

Hi guys,

I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.

View 2 Replies View Related

Locking Column (cannot Drop Columns In The Table)

May 27, 2015

How can I pervert dropping the column in the table (probably some process doing that and I want to find it) ? I need to be able to modify but not alter / drop column .

View 4 Replies View Related

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

Data Flow OLE DB Command And Table Locking

Jan 7, 2008



Hi all,

I'm just testing an SSIS package and am having issues with dealing with locked records.

my situation is as follows:

my source table is oracle, my destination table is in SQL server. my data flow is a very simple update with a lookup transformation and then two OLEDB commands for update and insert.

On each of the OLEDB commands I have set the "command timeout" to 5 seconds (just for testing purposes). also each OLE DB command has a failure path that outputs to a flat file. I'm expecting that if the destination table/records is/are locked then after 5 seconds the record will be output to the flat file.

so to test this I begin a transaction on the destination table and don't commit it. then I start the SSIS job. it doesn't appear to even get to the OLE DB commands. it appears to stop at the beginning of the data flow task. the output window shows this:

"Information: 0x40043007 at Import from Phoenix, DTS.Pipeline: Pre-Execute phase is beginning."

but it just hangs there indefinately. the progress tab tells me that it get's from the validating stage and past the prepare for execute stage but hangs on pre-execute - 0 percent.

I've put the command timeout = 5 on everything that I can find. I've mucked arround with all the possible "validateExternalMetadata" properties even though I only guessed that it may be the cause. is there anything that I'm missing? where should I look next?

(yes it does work perfectly when there is no transaction locking the target table)

Cheers,
Andrew

View 21 Replies View Related

Data Access :: Transactions And Table Locking

Sep 3, 2015

Multi user ASP.Net website, SQL Server backend.When using transactions to insert multiple rows into a table and then commiting them once you're happy that everything looks good, the table(s) gets locked before commiting.Basically, I want to perform updates and inserts under a transaction but still allow other users to carry on using the site and potentially the tables that are being used with the transaction.

Question: Is there a way to stop the table lock occurring and for SQL to just do a record lock?, thus avoiding blocking other users?I know that a possibility is to use Snapshot replication, but am a little worried about turning it on due to the small team we have for testing.

View 10 Replies View Related

Locking A SQL Table For Specific Amount Of Time.

Feb 28, 2008

I am wondering if it€™s possible to lock a SQL table for specific amount of time, say 5 min.



There is a particular 'Phone' table on the database that should never get locked. Yet, during the development stages we have noticed that the table gets locked at time. The issue since has been resolved to the best of our ability, but, there is still a vague chance that the table can get locked due to the multiple jobs that query the table, when we go live.



If such a situation occurs, we just want to be able flip the switch that will send the server to the mirror mode and the previously mirrored database to become the principal.



So, I just want to recreate a situation by voluntarily locking the table.

Any help in this regard will be appreciated.

View 6 Replies View Related

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 View Related

Row Level Locking For Queue Table To Get Rid Of Concurrency Problem

Apr 7, 2004

Hi all

We are writing a web-based multi-user call centre application application.

we are getting concurrency problems as you would expect with a multiuser application.

the application is made for callers who will bring up a different contact to call based on some predefined priority. now because the algorithm that prioritises the contacts takes a good 2 seconds to run, if 2 different caller request for the next prioritised contact, they will retrieve the same contact.

The only way that we think can resolve this problem is by building a queue. The queue would be implemented as a table, the particular implementation of this queue would be, when ever someone retrieves an entry from the queue, a background process will go on and generate a new queued item, i.e. in a FIFO manner. So that's how we think we should implement the queue.

Now come the question how to implement it. My idea is to have row level locking and a trigger to remove queue items from the queue. so that once one caller have looked at one of the item in the queue, another user can't look at the same item.

Any suggestions as to how i might be able to avoid concurrency problems?

What do you all think of my idea of implementing the FIFO queue?/
Is it possible to do row level locking in such a way that other users won't even be able to read the locked entry??



James :)

View 2 Replies View Related

SQL Server 2014 :: Table Locking While Insert Statement

Sep 18, 2015

I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.

View 5 Replies View Related







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