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.
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 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.
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?
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!!
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
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?
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
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.
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!
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.
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
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
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?
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?
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.
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] ....
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.
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?
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?
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
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.
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 .
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!).
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)
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.
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.
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)
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??
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.
