How To Prevent Table-locking With Multiple Queue Readers
In a situation where messages are coming in faster than they can be processed, at what point will service broker start up another queue_reader? Also, how do you prevent table locking if part of the processing of that message involves inserting or updating data in a table? We are experiencing this problem because of the high number of messages coming through, and I'm not sure what the best solution is - does service broker have some built-in support for preventing contention on a table when multiple readers are running? Or maybe a pattern that can be used to get around it?
View Complete Forum Thread with Replies
Related Forum Messages:
Clarifications On Queue Service And Queue Readers
Hello, This is info that I am still not certain about and I just need to make sure, my gut feeling is correct: A. When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back? 1. Message is left on the Queue. 2. is the worker procedure triggered again for the same message by the queue? 3. I am hoping the Queue keeps on triggering workers until it is empty. My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages. B. For my scenario messages are independent and ordering does not matter. Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously. Thank you very much for the time, Lubomir
View Replies !
How To Prevent System Form Locking A Table In Case Of Large Insertion
Hi, I found out that executing the procedure SP_INDEXOPTION and setting 'AllowRowLocks' to false i can prevent the sql server from locking rows in a table and 'AllowPageLocks' prevents from pages being locked. I need to preform same operation in case of tables. I need to perform insertion operations concurrently and acquire required locks manually. Is there a way to stop sqlserver from acquiring locks on the table. I need to disable all the locks (row, page and table). Thank you in advance.
View Replies !
How Can Readers Block Readers In SQL Server 2005
Hello All, Greetings!!! Due to certain constraints on my Table, I am not able to place unique Key constraint on it. So I have take care about the uniqueness in my T-SQL code. I don't want to use the serializable transaction isolation level as it will result in frequent deadlocks. I want some means by which one select will block other select. Can this be done in SQL Server. I tried using TablockX along with holdlock as Table hint, but still the Selects does not block other select. Will appreciate if you can resolve this issue Thanks in Advance, Mitesh Shah MCTS- SQL Server 2005
View Replies !
Row Level Locking For &"queue&" Table To Get Rid Of Concurrency Problem
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 Replies !
SQL Server How To Prevent Locking To Do Read-Only Reports
Need some information regarding SQL Server locking.Requirement is to run SQL Statements for reports (read-only) that willnot lock-out other people trying to update (write lock). For this, wetried the following to make the db connection not deny write locks(our report code will not have a read-only lock).ADODB.ConnectionObj.Mode =adModeShareDenyNoneBut this made our connection bumped whenever someone tried to update.Tried enabling dirty reads by doing:ADODB.ConnectionObj.IsolationLevel =adXactBrowseEven this made our connection bumped whenever someone tried to update.Is there a way to achieve what is needed without having a seperatedatabase for reports?
View Replies !
Queue Processing Using Updlock, Readpast Locking Hints
This article instructed me on how to process rows from a table used as a data queue for multiple processes. http://www.mssqltips.com/tip.asp?tip=1257 I tested this against the AdventureWorks DB (SQL 2005) and multiple SQL connections inside of Sql Mgmt. Studio). Connection1: BEGIN TRANSACTION SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows --COMMIT TRANSACTION Connection2: BEGIN TRANSACTION SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows COMMIT TRANSACTION This works like I want where connection 2 skips over the locked row from connection 1 and gets the next available record from the table / queue. However, when I add ORDER BY tsql to each sql statement, connection 2 is now blocked waiting for Connection 1 to commit. (This is not what I want) Connection1: BEGIN TRANSACTION SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate --COMMIT TRANSACTION Connection2: BEGIN TRANSACTION SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate --is blocked until connection 1 commits transaction COMMIT TRANSACTION How do I prevent blocking when using these locking hints with ORDER BY? thanks
View Replies !
Locking Method... Records In A Queue - Ensuring Only 1 User Get The Next Record...
I have an Access front end that reads data from a SQL database. The data is a simple table but depending on the user, different sorting queries. Some records may show up in more than one query. The user opens the record, makes a call, and the changes the status. At the time they open the record, I stamp it with their userID and also a date and time. This removes it from the active list. When the user hits "next record", the same function grabs a record number (unique key field) and then opens that record in the form. The problem is that the function that runs when you hit "next record" does a query to return the next active record, returns the unique key, and then sets it to the form. In rare cases, the record number can be the same for more than one user. Then, when the form is opened, the first user stamps the record - as the 2nd one. Later, when they save, they receive the write conflict message. I want, when the next record is selected, to grab the next unassigned/unstamped record, stamp it, and then open it in the form. If the next user tried that record, it will not open it but will go out and re-query for a new record. I hope this made some sense. What is the best way to do this? Thanks.
View Replies !
Multiple Services On Same Queue Or Not ?
Hi There I am guessing defining multiple services on the same queue is basically for providing different services to outside sources while using 1 queue, obviously as long as the contracts and activated sp logic applies to all services defined on that queue. I am defining a queue per service, is this right or wrong or irrelevant ? It just works better for me in terms of manageability. I just want to make sure there is no "best practice" reason for doing either or ? Is it just a matter of preference? Any comments? Thanx
View Replies !
Triggers Prevent Updates To Multiple Records
I have triggers in place on a table that do various checks on data input. It is clear that because of these triggers I cannot do updates on multiple records in this table. When I do, I receive an error that "subquery returned more than one value." Is there anyway to work around this by temporarily turning off triggers or something else? Many thanks for advice.
View Replies !
Problem With Distributed Transactions - Multiple Threads Pop The Same Message From Queue
Hi, I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit(). I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit. My dequeue SP is as follows: CREATE PROC [dbo].[queue_dequeue] @entryId int OUTPUT AS BEGIN DECLARE @conversationHandle UNIQUEIDENTIFIER; DECLARE @messageTypeName SYSNAME; DECLARE @conversationGroupId UNIQUEIDENTIFIER; GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue; if (@conversationGroupId is not null) BEGIN RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId END if @messageTypeName in ( 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog', 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' ) begin end conversation @conversationHandle; end END Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?
View Replies !
Readers Not Queued?
When multiple readers are waiting on a message from the same queue, I would expect that the reader that has been waiting the longest would be the first to pick up a message. However, I'm shocked to discover that the opposite seems to be true; in my tests I'm showing that the reader that has been waiting the least time picks up a message first! This seems totally counter-intuitive, and I'd like to know why it's working this way. This implementation will cause a lot more reader timeouts to occur than a properly queued method. For instance, assume that I have two readers, each using a one minute timeout. Reader #1 starts waiting, and reader #2 is busy for another 20 seconds before it starts waiting. 39 seconds later a message comes in and reader #2 will pick it up, leaving reader #1 to time out one second later! I would much rather have reader #1 pick up the message and reader #2 continue to wait for 20 more seconds. I'm considering filing a bug on Connect about this, but I thought I'd post here first and see if I can get an answer... Following is the script I'm using to test: ---Setup / Window #1--- -------------------------------------- CREATE DATABASE SimpleSSB GO USE SimpleSSB GO --Create a database master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'onteuhoeu' GO --Create a message type CREATE MESSAGE TYPE Simple_Msg VALIDATION = EMPTY GO --Create a contract based on the message type CREATE CONTRACT Simple_Contract (Simple_Msg SENT BY INITIATOR) GO --create a queue CREATE QUEUE Simple_Queue GO --Create a service CREATE SERVICE Simple_Service ON QUEUE Simple_Queue (Simple_Contract) GO -------------------------------------- ---Go start the other windows now--- ---Readers: Windows #2-n --- -------------------------------------- USE SimpleSSB GO WAITFOR ( RECEIVE * FROM Simple_Queue ), TIMEOUT 300000 -------------------------------------- ---Start at least two readers, then do--- -------------------------------------- --send a message... DECLARE @h UNIQUEIDENTIFIER BEGIN DIALOG CONVERSATION @h FROM SERVICE Simple_Service TO SERVICE 'Simple_Service' ON CONTRACT Simple_Contract WITH ENCRYPTION=OFF; SEND ON CONVERSATION @h MESSAGE TYPE Simple_Msg GO -------------------------------------- ... the last reader you've started will pick up the message first. Note I'm testing on 9.0.3033, in case that matters. Thanks!
View Replies !
Prevent Duplicate Entries In A Table
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember. The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID. Here is a sample of some data: User1 Group1 User1 Group2 User2 Group2 User3 Group1 User3 Group3 Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example: User1 Group1 User2 Group2 User1 Group1 I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome. Is there a simpler way to prevent duplicate entries in a table using sql? Thanks a lot, Chris
View Replies !
How To Prevent Doublicate Entry To A Table
hi, If I have a table which contains customer names. I want to have some kind of process to validate any new insert record into the customer table. so if the inserted new customer already exist in the table I will get a message that say " Sorry , this customer name is already in the system " .... how can I do that, I am using sql server 2000. thanks Ali
View Replies !
Table As Queue
I have a table that I want to act as a queue. It has no indexes and no key. Just one column. Basically I want a stored procedure that will pull / return the first record off the queue (table) and delete it. I'd rather not use MSMQ for this. There will be about 10 users trying to do this at the same time and will be trying to pull of about 15 times every second. How can I do this and ensure that no two requests pull off the same row? Thanks, Kevin
View Replies !
Need A Fast Queue Using A Table
I am trying to implement a very fast queue using SQL Server.The queue table will contain tens of millions of records.The problem I have is the more records completed, the the slower itgets. I don't want to remove data from the queue because I use thesame table to store results. The queue handles concurrent requests.The status field will contain the following values:0 = Waiting1 = Started2 = FinishedAny help would be greatly appreciated.Here is a simplified script to demonstrate what has been done.CREATE TABLE [dbo].[Queue] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[JobID] [int] NOT NULL ,[Status] [tinyint] NOT NULL) ON [PRIMARY]GOCREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]GOCREATE PROCEDURE dbo.NextItem@JobID integer,@ID integer outputASSELECT TOP 1 @ID = [ID]FROM Queue WITH (READPAST, XLOCK)WHERE (Status = 0) AND (JobID = @JobID)RETURNGO
View Replies !
Performance Of A Queue Vs Table
I know we are not allowed to benchmark SQL Server but..... It would be nice to have material to present which demonstrates the performance gains using a queue compared to insert/delete in a SQL table. Logically it seems faster to use a queue due to the conversation grouping locking and the service broker itself. But there seems to be some overhead involved just to manage these queues that the service broker has to perform. I am sure we are not unique with the choice to figure out if we will get a boost in performance using SQL a queue between services rather than a table to queue data. What is available to help understand the performance gains of using a queue?
View Replies !
Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View Replies !
Storing A List In A Database Table (messaging Queue?)
hey all you database guru's hopefully someone can lend some insight as to a little table design problem I have. Basically I've got a system in place to authorize users to access a website typical username password stuff. The table contains a list of users and there passwords plus the auth level and a few other tid bits that aren't really important enough to into detail about them here. What I want to do is add a messaging system to this, I think I could probably figure out a way to do this half decent if I setup a seperate table for each user to add a row to the table for every message entry than in my asp.net code have it delete everything but the last 10 entries every time a user logs on. However I would much prefer a way that I didn't have to setup a whole new table for each user just for messaging purposes, maybe store something like a list in one of the database cell's kind of like .nets generic.list or better yet generic.queue, I would also like a way if it's possible without too much work to have the table automatically delete the oldest message every time a new message is received if there's already 10 messages existing for the user. Anyways hopefully someone has some experience in setting up a system like this, I don't really require any code samples I can code it all myself (other than the database code to automatically remove entry's, I'm not a database guy) if someone could just explain a way to accomplish what I'm trying to do, or if someone has a different more convenient way of doing this I would be up for suggestions Thanks in advance for any help offered, I do appreciate it
View Replies !
Table Locking
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 Replies !
Locking Table In A SP
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 Replies !
Table Locking
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 Replies !
Table Locking
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 Replies !
Table Locking?
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 Replies !
Locking The Whole Table
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 Replies !
Locking Table
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 Replies !
Table Locking
I have a batch process that runs on a table for several minutes. While the batch is running, an insert to that table gets hung until the batch completes. This does not occur in the full version, the insert does not have to wait. Is there something I can set in my batch to not lock the table? RLiss
View Replies !
How To? - OpenQuery Table Locking
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 Replies !
Locking A Table Before Query
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 Replies !
SQLTransaction Locking Up Table
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 Replies !
Locking Users Out Of Table
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 Replies !
Locking Scheme On A Table
Whe I do the following SELECT INDEXPROPERTY(OBJECT_ID('message_done'), 'pk_message_done', 'IsPageLockDisallowed') I get the output as ZERO and when I do the following SELECT INDEXPROPERTY(OBJECT_ID('message_done'), 'pk_message_done', 'IsRowLockDisallowed') I again get the output ZERO. Shouldn't it be ZERO in one case and ONE in other, as a table can either have ROW level locking or PAGE level locking. Thanks.
View Replies !
Locking Table While Insert
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 Replies !
Best Way To Exclusively Locking A Table
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 Replies !
Table Locking & Transactions
Greetings, I have a question regaring table locking (and releasing) after a transaction completes. Here's my scenario: I am running a (very simple) sproc that does a simple UPDATE, then INSERT in one(1) Transaction. When I kick off the sproc (all hell breaks loose)first, the table locks and I can't "see" the table growing (run a select count(*) from table in another query analyzer window). More importantly (our dB being really small)the sproc runs way to long for the amount of work its doing AND if I don't kill the process manually (inside EM) it will not release the tables. For further clarification my sproc looks something like this: CREATE PROC sp____ AS set nocount on BEGIN TRAN LOAD UPDATE b SET b.blah = c.blah WHERE ....... INSERT INTO BLAHBLAH (.....) VALUES (.....) COMMIT TRAN LOAD GO Someone please tell me where I am going wrong or if there is anything I can do to increase the performace of these simple update/insert statements within a sproc. Also, the sproc has no cursors and each update/insert statement begin with BEGIN TRAN and end with COMMIT TRAN. Please help, Thanks for your time, Christine
View Replies !
Delete Without Locking The Table?
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 Replies !
Locking Records/table
Hi! I have a problem like this: There is table JOB with attributes: JOB_ID, ORIGINATOR_ID, CAUSE, DATE and also USER (USER_ID, FNAME, LNAME, LOGIN, PASSWORD) User can log more than once simultanously. There is procedure Create_JOB @login , @pass , @cause which insert new record to the table, and has to return new inserted record's JOB_ID just after insert statement I run: select max(JOB_ID) from JOB where ORIGINATOR_ID = (select USER_ID from user where login = @login) to return the JOB_ID to the client ..., but when particular user run Create_JOB 2 x at the same time it is possible that both client receive the same (second one) JOB_ID Any suggestions? Michal
View Replies !
Table Locking In Partitioning
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 Replies !
Locking A Table Header Row
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 Replies !
About Locking A Table Of Database In C#
hi i need to develop an aplication ,in which one thread will insert a record and sends that id to another Application,that will process the record.now at the insertion point i need to lock the table.how to do that? Thanks in aDvance
View Replies !
Locking A Table For The Duration Of A Transaction.
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 Replies !
SSIS Source Table/Row Locking
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 Replies !
Read, Modify Table (locking) Question
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 Replies !
|
TRACKER
