Hi DB Gurus..
I need to lock one table ( table-level locking) as i dont want anyone to modify till i finish my stored procedure.
I need simple example on how to Lock the table and unlock..
Something like this
Lock Table Name...
...
statements go here
...
Unlock table...
Is this possible in Sql server 2000
waiting for ur replies..
Thanks in Advance
I have a Table TEST (TCD int,NUMCD int ,TNM varchar(20))
where TCD is an identity column
1- When inserting I want the value of TCD to be inserted also in NUMCD.
2- I want to prevent two users to insert the same value of NUMCD
if executing the same insert at the same time .
For this i use the following insert :
INSERT Into TEST WITH (TABLOCK)
(NUMCD,TNM) select IsNull(Max(TCD),0)+1,'Abcd' from TEST
- Is the sql statment above resolve my probleme. If not is there someone who can help me resolving this probleme.
Think you for helping me
I've written a C# program which (1) Calls a SQL stored procedure which, among other things, updates a table with email information and then (2) sends the email via Outlook. This is ultimately going to be on a production server where the C# email program can be called by a number of processes.
If more than one instance of the email program is invoked at the same time, problems could occur. For instance, after the first instance updated the table it would then begin the process of creating and sending the email, but if a second instance is called at that time,it could be updating the SQL table which I think would screw up the email the first instance was creating. As a solution, I've been looking into locking tables. What I'd like to do is lock the table after it's been updated and unlock it after the email is sent. I know I can use the SLEEP function in C# so if it tries to access the table and it's locked, to wait 20 seconds or so and try again.
So how can you lock (and unlock?) a table in SQL? I'm reading about using WITH (TABLOCK) in the UPDATE query, but I'm not sure that will solve my problem. From what I can tell, TABLOCK automatically "unlocks" when the update is done. That still wouldn't resolve the issue of instance 2 updating the SQL table after instance 1 had updated it, but BEFORE instance 1 has completing creating the email (based off the data in the table).
Thanks
Hi,
I have a question regarding locking tables in transact sql.
The situation is the following:
a table named TableA which has two columns; ID and Name.
a table named IDTable which has two columns REF_ID, REF_PREFIX
in the IDTable we have the following entry:
REF_ID = 1, REF_PREFIX = 'TbA'
I have a stored procedure that will make a lot of inserts into the TableA. However there is another program that makes insert into TableA as well.
They both read from the IDTable to get which ID to use (ID of course needs to be unique).
A simplified version of the stored procedure is:
"SELECT all entries to move from another table"
"While not all entries moved"
"Get id from IDTable"
"Insert into TableA"
"Update IDTable"
"Loop"
I would like to have it like:
"SELECT all entries to move from another table"
"While not all entries moved"
"lock IDTable and TableA"
"Get id from IDTable"
"Insert into TableA"
"Update IDTable"
"unlock IDTable and TableA"
"Loop"
and thus giving the other program a change to "get in line" to use the tables but not using them at the same time.
So my questions:
1) Is this possible (if not, please explain why)?
2) How do I lock a table, I have tried "LOCK TABLE" but that just gives me syntax error?
Thank you in advance.
BR Mikael
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.
I have some stored procs that have temp table creations in them using "create table #tmptable...". I have noticed that when running the procs, sp_lock shows tempdb system tables being locked. I totally expect this. But, put a PowerBuilder front end on, and I get another situation. Calling the stored procs from PowerBuilder causes the same locks to occur, but they are not released after the data is displayed through the application. Furthermore, this blocks other users from creating anything in tempdb until the user logs off of the application and ends the connection to the database.
please help - is there something I am missing in my procedures, or is this a PowerBuilder issue?
Thanks!
Dean
HI There
After upgrading my publishers to 2005 i noticed that i cannot specify not to lock tables during snapshot during publication creation, also not on publication properties, and i see sp_addpublication has no such parameter, is there no longer an option not to lock publication tables during snapshot?
Thanx
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
Over the weekend, one of our out-of-house programmers ran an update to our three main tables. I know these are kind of broadstrokes, but basically he compared the data and updated certain fields when it met certain conditions (lots of rules basically). The three tables are one-to-one and contain a little over a million records. The comparison file contained around 400k records.
The scripts made it through 250k records from the comparison file before he had to stop it for the weekend.
When I came in to test the data yesterday - I was met with problems on my front end application - it would lock up on the write back to the database. I went into EP and experienced the same thing after making any changes to a record, it would just lock up. This only appears to be a problem on the 2 bigger tables of the 3. I currently have 12 gigs or so free on that box and I have already shrunk the log and data files.
I tried removing and re-adding the indexes, but I am freezing up everytime I try to either change or delete the Clustered Index on the Primary Key. I don't know why, but I thought maybe that was my issue.
I know this is pretty broad, but even if someone could give me ideas as to why SQL would lock up like that when trying to just save the data, it would be most helpful.
NOTE: There were NO structure changes in the update process and my restored data from Friday works perfect.
If you need more info, just ask. Thanks in advance for the help.
Don
elitecobra2000@yahoo.com
Hi All -
My Set up:
Server A - Oracle 10g Database
Server B - SQL2005
Client PC - Sql Express
Server A holds all data. I am using a linked server to connect server A and B. I use a set of stored procedures containing the openquery() syntax to get data from Server A to Server B. These stored procedures run every 20 minutes. I then create a publisher on Server B. I subscribe from the client PC to publisher to get data down from Server B to client(Download only subscription).
When I fire up the stored procedures and attempt to replicate, everything works fine. It appears after about 4-5 hours of the stored procedures running replication begins to hang more and and more until eventually it hangs for about 10 minutes and I recieve the following error:
Command attempted:
{call sp_MSreleasemakegenerationapplock}
Error messages:
The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)
I'm not sure if there is an error with openquery() locking? There is some locking activity going on with the TempDB and and Server B database. I've also come across some threads talking about the agent profiles. I'm very new to replication and very confused by all of the options in the agent profiles. Any help would be greatly appreciated!
We have transactional replication running with a seperate publisher/distributor/subscriber. I want to add a couple of articles to the publication, and then initialise them. I have added the articles and run sp_refreshsubscriptions
I now want to refresh the subscriptions. I have selected not to lock the tables on the snaphot tab of the publication properties, but whenever I run the snapshot agent it locks the application solid! Its odd, as soon as I run the snaphot agent, the phones start ringing within minutes. The application is Great Plains and I have set the snapshot agent to run nightly anyway.
Is there any way I can run the snapshot agent during working hours to refresh this one article? Once I have successfully done this, I have a number of articles want to add - but I can't lock the tables when refreshing the initial snaphot.
Hi everyone,
I have a question about SQL Server 2005. I have written an ASP.Net 2.0 Web Application and it is using SQL Server 2005 as Database.
In the last few days I noticed that the app is down sometimes. To analyze the problem I looked at the activity monitor in SQL Management Studio. I can see there approximately 170 processinfos.
I want to describe the column values of the process infos:
Process-ID: Unique ID and a red down-showing-arrow-icon
User: My UserDatabase: My DatabaseStatus: sleepingCommand: AWAITING COMMANDApplication: .Net SqlClient Data Provider
When I click Locks by Object, I can see the IDs of the Processinfos. Again I will show some colums:Type: DATABASERequirementtype: LOCKRequirementstate: GRANTOwnertype: SHARED_TRANSACTION_WORKSPACEDatabase: My Database
So my question is, does this mean, that i have locked the db? How are they handled? For example I have a windows service, which is doing checks in db every 10 seconds. I can see, that each check generates a new processinfo?
Is this usual, or am I doing something wrong?
Thnaks for help,Byeee
When I run a select statement : select 'X' from table1 where c1 = condition locking on indexes behaves as expected
However if I run select 1 from table1 where c1 = condition locking on indexes goes wild locking pages and rows on indexes that are not even referenced in the query. Any ideas Why?
Hello All,
I'm just migrating from oracle to SQL.Can anybody tell me that how effectively I can use Row level locking in SQL? If tow users are attemping to
Moify same record how i can deal it in Back end(SQL)?
Thanks in Advance.
Suchee
currently im using NT 4 (SP 6a), Intel Pentium III 600 (DUal processor) and 1GB RAM.
the problem that i facing now is DEADLOCK. the lock type in my database is 'DB type', how can i change to 'KEY' type or 'TAB' type???
pls help me and thanx in advance... :)
from comel
i have an application in production(sql 6.5 ) which causes locking which times out my other processes , iwant to capture time the locking takes place i have found in bol that i can get time deadlock occurs using trace flag 3605 in sql7.0 ,if i have to use trace flag is it ok with dbcc traceon or -T option in startup is recommended.
any advice would be appreciated
tia
ram
I have used DTS in the past to copy information in certain tables in production over the top of those same tables in test. However, the process is now failing. Does DTS require an exclusive lock on the source table, as well as the destination table during the export process? Will shared locks on the table I need to copy prevent DTS from completing the process?
View 4 Replies View RelatedHi
We are running out of locks while updating a particular table (table name = history, rows = 25,000,000) in SQL Server 6.5.
LE threshold maximum is set to 200.
LE threshold minimum is set to 20.
LE threshold percentage is set to 0.
Locks is set to 0.
I have also included the stored procedure, which we use to update the history table.
As you can see, from the first four lines, we ran this SP 4 times processing around 6 million rows at a time. It runs out of locks once it is around 5.5 to 6.5 million rows. Is there a way of locking the table so that this SP can be run just once which will effectively process all the 26 million rows in one go?
Any help will be greatly appreciated.
Winston
--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 5635993 and 12000000)
--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 12000001 and 19000000)
declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno > 19000000)
set nocount on
declare @sex char(1)
declare @huin integer
declare @huan integer
declare @hmailingdate char(8)
declare @mailtot integer
declare @mail12m integer
declare @lastday char(8)
open minihist
fetch next from minihist into @huin,@huan,@hmailingdate
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @mailtot = 1 select @mail12m = 0
/*** Get the gender ***/
select @sex = gender from name where uin = @huin
/*** Calculate if mailed in the last twelwe months ***/
if (@hmailingdate <> null) and (@hmailingdate > '19980524')
select @mail12m = @mail12m +1
/*** Get info for this uan from address_summary ***/
select @mailtot = (@mailtot+mailed_total), @mail12m = (@mail12m+mailed_12months), @lastday = last_date from address_summary where uan = @huan
/*** Insert a row into address_summary if doesn't exist ***/
IF @@rowcount = 0
Insert into address_summary ( uan, uin,mailed_total,Mailed_12months, last_date,last_gender)
values (@huan,@huin,1, @mail12m, @hmailingdate,@sex)
ELSE
/*** compare recency ***/
if (@hmailingdate < @lastday) or (@hmailingdate is null) /* history record is older */
update address_summary
set mailed_total = @mailtot,
mailed_12months = @mail12m
where uan = @huan
else
update address_summary
set uin = @huin,
mailed_total = @mailtot,
mailed_12months = @mail12m,
last_date = @hmailingdate,
last_gender = @sex
where uan = @huan
end
fetch next from minihist into @huin,@huan,@hmailingdate
end
deallocate minihist
Hi,
We are running SQL 6.5 in Produciton and I'm getting one blocking problem but mostly I kill the process and whenever I check the SQL Error Log I see this message :
Error : 17824, Severity: 10, State: 0
Unable to write to ListenOn connection '1433', loginname 'XXXY', hostname 'DT SA'.
OS Error : 64, The specified network name is no longer available.
Error 1608, Severity: 21, State: 0
Hi,
I'm trying to use the pessimistic row locking of SQL to get following result.
When a customer form is openend, the row should be locked for writing.
This lock should be left open until the user closes the customer form.
I cannot use transactions because there can be more then 1 customer form open in the same app. In ADO a connection is IN transaction or is NOT, nested transactions are not supported.
How can I keep this row locked on SQL and this until I unlock it or the connection is broken ( in case of problems on client machine )?
And how can I see on another machine of this row ( customer ) is already locked so I can open him in read-only?
For the moment I'm using extra fields that hold the info wether the customer is locked en by whom. But that's on application level, not on DB-level.
I hope this is clear enough.
Ok, this may be a brain dead question but I can't seem to figure out what it is I am doing wrong. I have a stored proc which has multiple inserts and updates and deletes. However, I do not want to commit until the end of the procedure. So near the end if no error has been return by a particular insert, update, delete I tell it to COMMIT TRAN. My problem is that it seems to run and run and run and run. I take out the Begin Tran and boom it runs fast and completes.
But if there is a problem near the end then those other statements will be committed. I wish to avoid that. I have an error routine at the end of the SP and I have if statement to GOTO sp_error: if @@error produces a non zero value. I am sure I am doing something goofy but can seem to see it. I know it has come down to the Begin Tran. Is it that I have too many uncommitted transactions? Or perhaps I am locking something up. I know its hard to tell without seeing what I am doing but is there something simple to remember about using explicit transactions that I am forgetting. Any help is appreciated.
Tom
Hello .
I am using SQL Server 2000 in order to create a multi user program that accesses data.
The problem is that multiple users will update and select data at the same time at the same table.
Is there a way to avoid deadlocks ?
I heard about two ways: using a temporary table to store data and then write the data only when the user finished the update.
and the other is using xml to write the database to a xml file that is stored locally. do the updates on the file and then after completion insert the xml file into the database.
does anybody know much about these ways? do you know where i can find code for this ?
is there a better way?
thanks !
and happy new year !
Hi all, firstly I would like to apologise because I don't actually use sql or know diddly squat about it. I am a network administrator and have a problem with a user's domain account getting locked out everytime he starts his sqlagent service (we are running a windows 2003 domain). I know this a vary vague post and I am sorry for that. I am just after some general ideas/information on why this keeps happening. Any help greatly appreciated.
Thanks
Rich.
deepak writes "how to lock the record while using a query
"select id,name from students"
i want to know various locks in sqlserver and and each of its use in insert ,update,delete and select etc. i am using it from visual basic 6.0
pls give reply fast"
Hi
On SQL i have created a query. Here is the code:
use DB1;
select * from [Jobs]
select resource_type, request_mode, request_status, request_session_id
from sys.dm_tran_locks
It produces the following results when run:
|resource_type | request_mode | request_status | request_session_id
|Database | S | Grant | 51
|Database | S | Grant | 54
What is "S"? what are the other possibilities and their meaning for this field.
And.. 51 and 54...what are they exactly? Are they individual people or user ids?
For example, could 51 be "Advanced users" and 54 be "Generic Users" under SQL security?
My next question is...
I suspect i have too many Indexes on my table "Jobs". I suspect it is causing page locks. Especially when someone is updating the records.
I will run this query when users complain to me about not being able to edit records.
Ok..Question is...if i have a PageLocking entrant..
Through SQL manager..is it possible to boot a user off temporarily..?
How do you do it?
Thanks
In Advance
Conor
Hi All, Please help me out how to implement the locking in below scenarioReq - There are two tables Table1 & Table2 If I will insert in table1 then related data fields will be auto updated in table2 , similarly based on the data in table2 table1 data needs to be updated. Now the sync of table1 & table2 is working fine.My prob is we are handling the updation/insertion from the UI screens . Two separate screen for each table. When we have multiple user accessing the screens say - User1 updates table1 and User2 updates table2 then we need to implement the locking so that at one time one screen will allow updation in the table1 and hence table2.The other screen shouldnt allow updation in table2 and hence in table1.This is very common locking functionality ...but am not getting any way to implement it , Please advise.Srain.
View 1 Replies View RelatedHi,
I need to secure an sqlserver database such that it can only be accessed from an application and to prevent anyone with full admin rights on their local machine and an sqlserver licence from getting in to the database.
I am struggling with controlling access to the database from the sa account. If I attach to the database from a second instance of sqlserver which is different than that where the database was created then I am able to gain full access no problems, which is of course The Problem.
From what I can work out.
1. sa is dbo (and this cannot be changed)
2. dbo has the role of db_owner (and this cannot be changed)
3. the permissions for the db_owner role cannot be changed.
4. the password for sa is set at the level of sqlserver and not per database
.....so any sa can access any database.
I don't believe this so have to be missing something significant, any light on the subject would be gratefully received.
KeithT
Hi!This is a very simple question and I'm sure you guys will help me a lot.I'm using Visual Basic 2005 for programming. I have one table on my MS SQL 2005 database that has an int column with a counter that needs to be incremented when a user registers.So when reading the value I use a simple SQL query like this:
SELECT counter FROM companies WHERE company=0
then I store the value in a local int variable and then I increment it. Then I update the incremented value.
UPDATE companies ...
I need every single customer to have an individual value. My question is how can I prevent an error, data corruption or whatever if two or more users want to register at the same time? I've been reading about lock update but I'm not sure how to implement it on Visual Basic 2005 and I don't want to store scripts on SQL Server.
I'll appreciate your comments and help on this situation.
I have a busy transactional table , I wanna use row level locking mechanism in msSQL.
SELECT * FROM PARTY WITH (UPDLOCK ROWLOCK)
where LastName ='Clinton'
is there any downsides of this approach?
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.
Hi all,
What I'm trying to do os this: have an application set a lock on a specific row in a table, so other applications can see it's busy.
So, I use "SELECT * FROM mytable WITH(ROWLOCK, HOLDLOCK) WHERE condition" to set the lock. That should lock a row until I close this recordset (me thinks anyway...)
Then to detect I use "SELECT * FROM sametable WITH(READPAST) WHERE samecondition". If the row I'm looking for is locked, this select will skip that row, so I get an empty selection.
That's what I want to happen anyway, but in the real world this doesn't seem to work. It doesn't lock, or it doesn't skip....
Any hints/tips welcome!
Thanx!
Duq
I have a table X with 61390 rows on it. IT is a crucial table which is being read and updated constantly. If I create a clustered index on the Primary Key (identity column) and run UPDATE x SET c1 = 1234 where PKCOL = 4321 the best lock I can get is a table Lock. If I create a compound non-clustered index on C1 and PKCOL the best lock I can get is a Page Lock. PAge Lock is OK but as this table is in High demand I want a row lock so others can carry on referenceing other parts of the table. WHy does it take a page lock and not a Row lock?
View 1 Replies View RelatedGurus,
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