Frequently Update And Select Table Locking Problem
Oct 10, 2006
Hi guys,
I faced problem related to Locking and Isolation Level on Table(s).
My problems is there r some tables which r frequently updated, and I also want to fire select query over those tables every 1 seconds and want to get only committed records.
In current scenario we start transactions with ReadCommitted Lock for updating records. But in this scenario I can€™t get select query result because of some of recourses r used by transactions so after some time it gives Deadlock error.
So I want solution like both operation run simultaneously and get only committed records at a time of transaction running
Please help me for solving my problem.
View 10 Replies
ADVERTISEMENT
Oct 10, 2000
I have a query that retrieves a single record from searching on two tables.
The statement goes like this...
select sum(amount) from Table1 A union Table2 B on a.id = b.id
where date < ### and date > #### and account = ###
As people are running a particular report, this statement is executed time and
time again to pull up the numbers necesarry for the report. When the report gets slow, I can speed it up by updating the statistics. My concern is that I'm having to update the statistcs every hour; otherwise, the query becomes slow. I have noticed that users are inserting data while users are running the report on one of the tables listed above. I'm sure that's making it become more fragmented and ultimately slowing down the query.
Do you have any suggestion on how I can make the union of these two tables faster? Or is there anything I could do to speed the query besides creating clusted indexes?
Any help would be appreciated....thank you
View 1 Replies
View Related
Dec 23, 2014
I'm working on databases where statistics of some indexes (tables) are changing too frequently. Once I update them manually, one minute after they get 10-20% change, and five minutes after they get over 100% change. Tables get updated very frequently (multiple times in a second).
When I run a query to read from sys.stats, sys.dm_db_stats_properties and other dynamic views, I see that they were last updated when I did it manually, but the change rate overpassed the 500+20% (tables have multiples of 10K rows). Auto create and update statistics are set to true on all databases, and I don't know why sql server does not do that automatically.
View 2 Replies
View Related
May 7, 2007
Hi,
In a project with SQL Servcer 2005 the customer is interested to save (archive) content of a table frequently. They want to have possibility to restore the table content as before. I have suggested to export the table every day to a text file and save the file in Visual source safe. If they need there will be possibility to import the text file later. Now I wonder, is there any other way to do archiving of the table content in SQL Server 2005?
Best regards
View 4 Replies
View Related
Dec 12, 2000
I would like a simple statement to select some rows and then update them. I would like to use SERIALIZABLE locking, will this do the trick. Below is my TSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
select * from patient_medication_dispersal_ where ddate =
convert(char(10),getdate(),101)
update patient_medication_dispersal_
set rec_status = 1 where ddate =
convert(char(10),getdate(),101) and rec_status =1
commit tran
View 2 Replies
View Related
Mar 9, 2005
We’re creating a program and need some advice about locking. The program will select data for the user to view; the user may or may not then perform an update on the set. There exists a possibility of another user attempting the same operation at the same time. Thus we want to have the first user lock the data set and the second to be able to select only until the first is complete. We’ve run some tests using the UPDLOCK hint. It works as expect except for the blocking. If a connection attempts to create another Update Lock after another connection has one, then the new connection is blocked. We’re looking for a way to let the program know that an update lock is in existence before it tries to create a new one. The sp_lock procedure can display all the locks, but you can not select specific values (i.e. select any update locks on ObjectID = x). Do any of you know another way or how we can use sp_lock for our purpose?
Thanks in advance.
View 2 Replies
View Related
Sep 21, 2007
I am trying to run a simple update statement that updates around 1 million records on SQL Server 2005. For example:
update customers
set CustCode='AAB'
where CustType=72
I would like to update the table WITHOUT locking. In this case, there is no need to have "all or nothing" transactions. If it does a partial update and then fails, it's ok to only have half the records updated.
The server is using up a lot of resources creating and releasing the locks. Plus users are getting locked out of the records during the update. I know this is by design, but in this case it's OK. I know I can use the "set transaction isolation level READ UNCOMMITTED" statement to fix the select statements from getting blocked, but there are way too many places that would have to be changed. Plus there are other updates to this table that need to be locked.
So here is my question: Is there a way to do a transaction-less update?
Thanks,
Stuart Fox
View 4 Replies
View Related
Nov 17, 2006
Hello Friends,
I am totally new to locking field so I need your help.
Below are my environment details:
OS = Windows 2003 + Service Pack 1
SQL SERVER= SQL Server Enterprise Edition + Service Pack 4
My Organization is using an application that is designed in PowerBuilder. The problem is so many times this application got hang due to blocking in sql server.
I have diagnosis the problem in the below steps,
Waiting spid = 192
Select * from sysprocesses
Where spid = 192
Dbcc inputbuffer (192)
Then
Select blocked,* from sysprocesses
Where spid = 192
Returns
201
I had repeated the steps till I got BLOCKED = 0, STATUS = sleeping and CMD = AWAITINGCOMMAND. By this way I found the blocking spid.
And DBCC INPUTBUFFER (blocked spid) is a SELECT statement.
In sp_lock spid it is showing that spid is holding a shared lock, where my application had fetched all the data and also transaction had been commited.
I cannot find the solution please advice me what to do. Why select statement is holding a lock. End users are really frustrated.
Thanks a billion in advance.
View 7 Replies
View Related
Jun 12, 2014
I want to update records in 1 table with the result of a select statement.
The table is called 'MPR_Portfolio_Transactions' and contains the following fields:
[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]
And the select statement is like this:
SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
the key is PTR_SEQUENCE
View 3 Replies
View Related
May 4, 2015
I have a main table called Stock for example.
One process ( a service ) inserts data into this table , as well as updates certain fields of the table periodically.
Another process ( SQL Job ) updates the table with certain defaults and rules that are unknown to the service - to deal with some calculations and removal of null values where we can estimate the values etc.
These 2 processes have started to deadlock each other horribly.
The SQL Job calls one stored procedure that has around 10 statements in it. This stored proc runs every minute. Most of them are of the form below - the idea being that once this has corrected the data - the update will not affect these rows again. I guess there are read locks on the selecting part of this query - but usually it updates 0 rows - so I am wondering if there are still locks taken ?
UPDATE s
SET equivQty = Qty * ISNULL(p.Factor,4.5) / 4.5
FROM Stock s
LEFT OUTER JOIN Pack p
on s.Product = p.ProductId
AND s.Pack = p.PackId
WHERE ISNULL(equivQty,0) <> Qty * ISNULL(p.Factor,4.5) / 4.5
The deadlocks are always between these statements from the stored procedure - and the service updating rows. I can't really see how the deadlocks occur but they do.
Another suggestion has been to try and use an exists before the update as below
IF EXISTS( SELECT based on above criteria )
( UPDATE as before )
Does this reduce the locking at all ? I don't know how to test the theory - i added this code to some of the statements, and it didn't seem to make much difference ?
Is there a way to make a process ( in my case the stored procedure ) - give up if it can't aquire the locks rather than being deadlocked - which leads to job failures and emails etc ?
We are currently trying to filter down the data that is updated to be only the last few months - to reduce the amount of rows even analyzed - as the deadlocking does seem to be impacted by the number of rows in the tables.
View 9 Replies
View Related
Oct 28, 2015
I have  created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
 ALTER PROCEDURE [dbo].[RPT_Cost_copy]
SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM
INTO T1
FROMÂ
(SELECT a.meu, a.mep2, SUM(a.mest) as excst        Â
FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> '' Â Â Â
[code]....
View 2 Replies
View Related
Sep 27, 2000
I am doing a ‘Select into’ to make a table from at another table which has as many as 130 millions rows (its well indexed). The new table will most often have about 1000 rows. (During the running of the app, the app will be making many new tables, since hopefully this will be a ‘popular’ item by the users. After the users ‘use’ them, they are dropped.)
I know that locks are held on various system tables (including sysobjects) during this ‘select into’ process. Are they held for the entire process?
What should I be concerned about doing ‘select into’?
Thanks for any help,
Judith
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
