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

Transact SQL :: Get Total Request Duration From Multiple Task Duration?

Jun 4, 2015

I have the following SQL query

SELECT
[Req_ID]
,[Service_Name]
,[Req_Started_Date]
,[Task_Name]
,[Task_Status]
,[Performer_Full_Name]

[code]....

Which works fine, but what I need to calculate the total duration of a request based on the duration of the tasks completed in the request based on Req_ID. I would like to use the CASE statement I have to determine the SLA_Mins for each task and add them together to get total request SLA_Mins.

Below is the create table schema and data

GO
/****** Object: Table [dbo].[MidrangeOtherSourceControl] Script Date: 06/03/2015 18:13:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MidrangeOtherSourceControl](
[Req_ID] [float] NULL,
[Service_Name] [nvarchar](255) NULL,

[code]....

View 9 Replies View Related

Transaction Locking

Jun 7, 2006

I'm using Sql Server 2005...
I'm creating a transaction and enlisting the commands inside vb.net code as well as surrounding the t-sql in an "Begin Trans --- Commit Trans" block. I also have the Isolation level set to the highest (Serializable) in the vb.net code and the sprocs.
I'm running 4 instances of the app on 1 server and 4 instances of the app on another server. I am handling the lockouts just fine and writing them to an error table within the db. The app keeps spinning and producing data just fine.
There are 3 places where the locking may occur within the app. Two of them are just fine (which is a select and and insert). The app will eventually cycle around and pick up the records taht may have been locked out. My concern is the Update portion which updates stats based off the Insert done previously. If the records never get updated, the only way I would know if they were processed would be to check in our Error table to see if the record exists.
I would like to know if there is any way possible to cut down on the number of lockouts (which may be perfectly normal) and to get a way to update that table I just talked about. Should I be using different isolation levels, etc. --- anything of importance might be useful.

View 1 Replies View Related

Transaction Locking

Oct 3, 2000

I have a stored-procedure which insert's records. In it I have a Begin Tran so if it fails I can run a rollback. When I'm inserting big number of records it creates X locks and it start's blocking other users.

View 3 Replies View Related

Transaction Locking

Oct 24, 2007

Hi,

How can I know when a table is lock by a transaction?

I have found about the procedure sp_lock but it doesn��t show me the table that the transaction is locking and when I execute an update statement through an application the application crashed waiting for the transaction to finish.

Any help will be appreciated.

View 3 Replies View Related

Transaction Locking

Jun 8, 2006

I'm using Sql Server 2005...

I'm creating a transaction and enlisting the commands inside vb.net code as well as surrounding the t-sql in an "Begin Trans --- Commit Trans" block. I also have the Isolation level set to the highest (Serializable) in the vb.net code and the sprocs.

I'm running 4 instances of the app on 1 server and 4 instances of the app on another server. I am handling the lockouts just fine and writing them to an error table within the db. The app keeps spinning and producing data just fine.

There are 3 places where the locking may occur within the app. Two of them are just fine (which is a select and and insert). The app will eventually cycle around and pick up the records taht may have been locked out. My concern is the Update portion which updates stats based off the Insert done previously. If the records never get updated, the only way I would know if they were processed would be to check in our Error table to see if the record exists.

I would like to know if there is any way possible to cut down on the number of lockouts (which may be perfectly normal) and to get a way to update that table I just talked about. Should I be using different isolation levels, etc. --- anything of importance might be useful.

View 1 Replies View Related

Transaction Locking Issue

Jun 6, 2001

I have SQL server 7 machine here but this problem will occure with any
version I suppose.
We have a web application and NT server and client machines.

I have a stored procedure which has

Begin transaction
statement1
statement2 and so on... these could be any DML statements or another
proecdure call.

commit transaction
end

We cannot commit this transaction untill the process is complete, I mean it
processes all statements.

Now the problem is when one of the statement takes a ong time the page will
time out.. keeping that transaction open.

In the mean time other users are doing their changes in the database.. this
goes on for a while and nobody noticed it.

When the connection to the application was broken for some reason. The
transaction which was open was cancelled ... and everthing rollsback from
the time the 1st transaction failed ( timed out) so there is nothing in the
database that other users did..

How do you solve this problem ?

View 1 Replies View Related

Transaction/Locking Question

Nov 26, 2004

Hi,
Very new to locking/transactions, so any help appreciated with the following:

I want to create a stored procedure with statements that read an Integer value in a single row table, return this as output from the procedure to my web form, and increment it incremented by 1 (for the next call of the procedure).

I need to be absolutely sure that while the value is being read and updated, another call to the procedure cannot read/update this value until the current is call completed, i.e some sort of locking on the value.

Question - If I wrap my SELECT and UPDATE statements in a BEGIN TRAN and COMMIT TRAN, will this be enough? e.g Is this sufficient:

CREATE PROCEDURE sp_test
@retval int output
AS

BEGIN TRAN

SELECT @retval = POID FROM OMS_SYSDATA
UPDATE OMS_SYSDATA SET POID=POID+1

COMMIT TRAN
GO

Or do I need to start using things like HOLDLOCK/UPDLOCK within statements?

Cheers
Greg

View 6 Replies View Related

Transaction Locking Tables

Apr 16, 2004

Hi all,
I am writing an sp which includes insert and update statements.
sp is working fine.
But when I tried to make it as a single Transaction its not working(waiting indefinetly at second insert statement).tables are getting locked.
what could be the possible reason for tables getting locked indefinetly.
I Tried with
set transaction isolationlevel serializable
option.
There are several insert into statements.some of them on the same tables again and again.
any help would be greatly appreciated.
Thanks.

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

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

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