Revisiting The Locking Issues With Sql2000
Jul 12, 2004
ok, about a few months ago I brought up the issues we were having with deadlocks. My company is running a .net data entry application hitting against a sql 2000 box. Forget hardware since we have more then enough hardware to handle the load.
The issue we are seeing is we get a process that blocks a table, and the bulding up of block process that cause timeouts/deadlocks etc. A couple of things we see when this happens is that .net on the web server seems to open up more and more connections(it seems to double or triple the connections as the blocking begins), we start to see deadlocks and we see alot of timeouts in the app due to the blocking.
The blocking aways happen on an insert of an order or a customer. One thing I keep wondering about is how we handle the unique id for our tables. Our DBA had us put in the following:
Begin Trans
select max(id) + 1
insert....
commit trans
I've heard from a few here and in other forums this is the wrong way to go. I'm still get a gut feeling that this could start causing issues like what we are seeing. My problem is I'm not knowledgable enough to make a strong enough case with it.
What I need help is getting enough information to either point to how we manage ID's or at the very least rule them out. I've done searchs in google and some other search engines but there doesnt seem to be any good articles on how best to handle unique ids and show a good case for it. I do know I can use sql identity, or even guid with the newid() function but I need solid proof before my boss will move to one of these methods. Any links you guys can give or even your own write up will really help my case and hopefully help my headache.
Thanks Ahead of time
View 7 Replies
ADVERTISEMENT
Jul 12, 2004
ok, about a few months ago I brought up the issues we were having with deadlocks. My company is running a .net data entry application hitting against a sql 2000 box. Forget hardware since we have more then enough hardware to handle the load.
The issue we are seeing is we get a process that blocks a table, and the bulding up of block process that cause timeouts/deadlocks etc. A couple of things we see when this happens is that .net on the web server seems to open up more and more connections(it seems to double or triple the connections as the blocking begins), we start to see deadlocks and we see alot of timeouts in the app due to the blocking.
The blocking aways happen on an insert of an order or a customer. One thing I keep wondering about is how we handle the unique id for our tables. Our DBA had us put in the following:
Begin Trans
select max(id) + 1
insert....
commit trans
I've heard from a few here and in other forums this is the wrong way to go. I'm still get a gut feeling that this could start causing issues like what we are seeing. My problem is I'm not knowledgable enough to make a strong enough case with it.
What I need help is getting enough information to either point to how we manage ID's or at the very least rule them out. I've done searchs in google and some other search engines but there doesnt seem to be any good articles on how best to handle unique ids and show a good case for it. I do know I can use sql identity, or even guid with the newid() function but I need solid proof before my boss will move to one of these methods. Any links you guys can give or even your own write up will really help my case and hopefully help my headache.
Thanks Ahead of time
View 3 Replies
View Related
Sep 17, 2006
i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
View 1 Replies
View Related
May 13, 2008
i am in the process of Migrating SQL 2000 to my new SQL2000 server i want to know the what would the best way for me to migrate one SQL server to another SQL server on the same network and rename the new server to the old server and bring it up for use in our ecommerce website.
View 10 Replies
View Related
May 28, 2008
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
View 5 Replies
View Related
Jul 9, 2001
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?
View 1 Replies
View Related
Dec 29, 2000
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
View 1 Replies
View Related
Sep 25, 2000
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
View 1 Replies
View Related
Dec 4, 2000
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
View 1 Replies
View Related
Oct 30, 2002
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 Related
Oct 27, 1999
Hi
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
View 1 Replies
View Related
Jul 12, 2001
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
View 1 Replies
View Related
Jul 23, 2004
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.
View 4 Replies
View Related
Nov 16, 2004
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
View 12 Replies
View Related
Jan 9, 2004
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 !
View 5 Replies
View Related
Nov 21, 2005
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.
View 5 Replies
View Related
Mar 20, 2006
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"
View 2 Replies
View Related
Jan 23, 2008
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
View 3 Replies
View Related
May 7, 2008
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 Related
Nov 17, 2004
Hi,
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
View 5 Replies
View Related
Dec 23, 2005
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.
View 15 Replies
View Related
Feb 24, 2006
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?
View 1 Replies
View Related
Jun 7, 2006
I'm using Sql Server 2005...
I'm creating a transaction and enlisting the commands inside vb.net code as well as surrounding the t-sql in an "Begin Trans --- Commit Trans" block. I also have the Isolation level set to the highest (Serializable) in the vb.net code and the sprocs.
I'm running 4 instances of the app on 1 server and 4 instances of the app on another server. I am handling the lockouts just fine and writing them to an error table within the db. The app keeps spinning and producing data just fine.
There are 3 places where the locking may occur within the app. Two of them are just fine (which is a select and and insert). The app will eventually cycle around and pick up the records taht may have been locked out. My concern is the Update portion which updates stats based off the Insert done previously. If the records never get updated, the only way I would know if they were processed would be to check in our Error table to see if the record exists.
I would like to know if there is any way possible to cut down on the number of lockouts (which may be perfectly normal) and to get a way to update that table I just talked about. Should I be using different isolation levels, etc. --- anything of importance might be useful.
View 1 Replies
View Related
Jun 19, 2001
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
View 3 Replies
View Related
Jun 28, 2001
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 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
Feb 1, 2000
Hi
I've got table JOBS with JOB_ID, ORIGINATOR_ID etc. I prepare stored procedure that creates job, that mean,
inserts new record in table JOBS, and return JOB_ID as a result.
return (select MAX (JOB_ID) from JOBS where ORIGINATOR__ID = PARTICULAR_USER_ID)
The problem is when user is logged in two stations, runs two application and create job at the same time.
It is possible that both application receive the same MAX (JOB_ID)
Any suggestions how to lock records, or do it in a different way.
Michal
View 2 Replies
View Related
Oct 3, 2000
I have a stored-procedure which insert's records. In it I have a Begin Tran so if it fails I can run a rollback. When I'm inserting big number of records it creates X locks and it start's blocking other users.
View 3 Replies
View Related
Oct 5, 2000
Hello,
I need help in record locking. As soon as user retrieve records I want to lock those records in database. When other user try to retrieve records from the same table i want to retrieve records those are not locked by other user. How do I do this? Please help...
Thanks in a millian.
Sarika
View 1 Replies
View Related
Jun 15, 2000
In Query Analyzer I have 2 connections active. In the first connection I have the following SQL Insert statement:
Begin Tran
Update Account
Set SomeField = 0
Where CustomerId >= 1000 And CustomerId <= 1101
-----------------
In the second connection I have the following SQL Insert statement
Begin Tran
Insert Into Account (Account,AccountName,AccountTypeRowId,CustomerId,U serId)
Values('MMM-989', 'Test Account', 1, 1098,207 )
-----------------
The query in the second connection will hang waiting for the query in the first connection to finish. The query in the first connection touches 767 records (out of a total of 9174).
If I change the Where statement in the first connection to "... And CustomerId <= 1100", the query in the second connection will execute immediately. The query in the first connection is now only touching 645 records instead of 767.
Could someone explain to me why this is happening?
View 3 Replies
View Related
Sep 15, 1999
I have a table Jobs in my database.
I open Query Analyser and then connect to my database twice (now I have two windows).
In first window I run the following query:
begin tran
insert into Jobs ......
Then I run the following query in the second query:
select * from Jobs
This query hangs !!! The only way to get a result is commit transaction in the first window !
So, while some transaction is in progress I cannot see all (other) records in the table (transaction isolation level=read committed) !
System Info: Win95, MS SQL 7.0,
HELP ME, PLEASE !
Note: do not ask me to set transaction isolation level=read uncommitted !
View 5 Replies
View Related
Jul 7, 1999
Hi,
We are becoming increasingly more involved with SQL Server 7.0 development.
Can someone show me a good example of ROW LOCKING with exclusive and share
modes?
If I am starting a transaction with a SELECT and later an UPDATE, what is
the best way to handle locking?
Thank you for any help on this issue.
Steve
View 1 Replies
View Related
Nov 18, 1998
Hi all,
We had implemented a customized locking mechanism on our apps in SQL.6.5 which worked fine. But, we couldnt implement row-level locking, and hence had some performance problems. Can someone suggest how I can implement the row-level locking in SQL.7.0? Should I go for cutomised set-up again, or should I leave to the SQL server to take care of locking ? How has the experience been with other users?
Thanks
Rnathan
View 2 Replies
View Related
