Locking Records
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
ADVERTISEMENT
Oct 18, 2006
Hello,
Can anyone help find a solution to a strange record locking problem in SQL 2000?
This stored procedure is run every two seconds by two workstations to auto-process specialized "jobs". This convoluted code began as a simple update statement, but as grown to this in an effort to prevent the two workstations from picking up the same record at the same time. Unfortunately, the machines are STILL managing to pickup the same job, which seems to be indicating some form of record locking issue.
We've tried a number of locking hints, single BEGIN/COMMIT transaction command sets, and now a granular pair of transactions complete with additional filtering in the update command's where clause, but the two client machines continue to get the same job row.
Any thoughts on how to ensure that once once machine "grabs" a job, the other will not somehow get the same record ??
Here's the current state of the procedure code:
create Procedure dbo.spGetWFAutoJob216
@strActionSelected as varchar(150)
--stractionselected have all the selected actionid, seperated by comma.
as
Declare @JobID as varchar(10)
Declare @FoundJobID as varchar(10)
Set @JobID = -100
Set @FoundJobID = -100
begin tran
Select top 1 @FoundJobID = JobID from WFJob J
Join WFAutoJobDetail AJD on (AJD.WFActionID = J.ActionID and CharIndex(cast(ajd.wfactionid AS VARCHAR), @strActionSelected) > 0)
where isnull(J.Status, 'P') = 'P'
Order By J.Priority Asc, J.enteredDate ASC
commit tran
Begin Tran
Update WFJob
Set Status = 'I',
@JobID = jobID
where isnull(Status, 'P') <> 'I' and JobID = @FoundJobID
insert into wfjoblogger values (host_name(), getdate(), @FoundJobID, @JobID)
Commit Tran
Select Coalesce(A.Name, 'No Action Record') as ActionName, *
from WFJob J
Join WFAutoJobDetail AJD on AJD.WFActionID = J.ActionID
Left Join WFAction A On A.ID = J.ActionID
Where JobID = @JobID and isnull(J.Status, 'P') = 'I'
go
And here's an example of the log table results showing a few instances of the two machines getting the same jobid records:
LogID
Host
datetime
foundjob
thejob
51
SVR2
52:05.110
1049419
1049419
52
SVR2
52:12.140
1042784
1042784
53
SVR2
52:20.843
1052719
1052719
54
SVR2
53:12.920
1043496
1043496
55
SVR2
53:23.250
1045710
1045710
56
SVR2
53:30.297
1055096
1055096
57
SVR2
54:21.000
1043511
1043511
58
SVR2
55:06.907
1055095
1055095
59
SVR2
56:08.237
1055092
1055092
60
IS-1004
56:11.407
1055092
1055092
61
SVR2
56:43.313
1045366
1045366
62
SVR2
56:52.703
1052723
1052723
63
IS-1004
57:00.190
1052723
1052723
64
IS-1004
57:23.300
1055085
1055085
65
SVR2
57:25.970
1055085
1055085
66
IS-1004
58:24.877
1052286
1052286
67
IS-1004
58:44.877
1043491
1043491
68
IS-1004
59:08.270
1052727
1052727
69
SVR2
59:13.207
1052727
1052727
View 2 Replies
View Related
Feb 28, 2006
Hi,
How can i handle record locking in sql server?
View 1 Replies
View Related
Jan 5, 2004
In the application I'm working on, I need to be able to use different locks on my records during certain transactions. I need to know how to do this programmatically from within VB.net. If someone can show me an example of how to do this or show me where I could find some, I'd appreciate that.
Thanks.
View 2 Replies
View Related
Dec 18, 2007
I have created a database and used visual basic 2005.net2.0 to build the application. I used the data wizard. it created datatables and then I bound it to the contols on the form. The program is published to the server and each instance loads from the server and executes. When it stops, the instance is destructed from the host pc. The pc's are large ram and very small hard drive. These were the specs of the job. now when each pc loads an instance of the program, they can all load and edit the same exact record. I tried to bust it and I got on the same record and changed first name on one and last name on the other. the second one overwrote the first name edit. All of the data handling is done inside of the dataset designer and it does not allow edits. ( actually you can edit, but the next time you save it, or run it, it will rewrite what you added and it will be gone.) is there anything I can add to the form vb to tell it to lock the current record?
View 3 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 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
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
