Help: Table Lock Confirmation

Mar 12, 2004

I need confirmation from you SQL Server experts out there. Please let me know if the following works. Thanks!

This stored procedure gets a value and increments by 1, but while it does this, I want to lock the table so no other processes can read the same value between the UPDATE and SELECT (of course, this may only happen in a fraction of a second, but I anticipate that we will have thousands of concurrent users). I need to manually increment this column because an identity column is not appropriate in this case.

BEGIN TRANSACTION

UPDATE forum WITH (TABLOCKX)
SET forum_last_used_msg_id = forum_last_used_msg_id + 1
WHERE forum_id = @forum_id

SELECT @new_id = forum_last_used_msg_id
FROM forum
WHERE forum_id = @forum_id

COMMIT TRANSACTION

View 10 Replies


ADVERTISEMENT

Request For Confirmation Of SQL 2005 Optimiser Bug With Derived Table And Convert()

Jul 23, 2007

I have discovered what looks like a bug in the optimiser. I've posted it at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288243 but I wonder if any of you with SQL 2005 RTM, 2005 SP1 or 2008 CTP could confirm when this was introduced and whether it is still an issue?






Code Snippet

-- Bug report

-- 2007/07/19

-- Alasdair Cunningham-Smith

-- alasdair at acs-solutions dot co dot uk

set nocount on

go

-- example date in in British date format

set dateformat dmy

go



use tempdb

go



create table foo( bar varchar( 30 ) not null )

go



insert into foo( bar ) values ( 'fishy' )

insert into foo( bar ) values ( '19/07/2007' )

go



-- this works fine in all versions - only valid dates are passed to the convert function

select

convert( smalldatetime, bar, 103 ) as bardate

from

foo

where

bar like '__/__/____'

go



-- this works on SQL 2000, but fails on SQL 2005 SP2 (I've not tried other SPs of SQL 2005):

-- Msg 295, Level 16, State 3, Line 2

-- Conversion failed when converting character string to smalldatetime data type.

--

-- I believe the query is rewritten as if the derived table query contained

-- "and convert( smalldatetime, bar, 103 ) < getdate()"

-- which would expose the convert to the invalid data

select

*

from

(

select

convert( smalldatetime, bar, 103 ) as bardate

from

foo

where

bar like '__/__/____'

) as derived

where

bardate < getdate()

go



-- Workaround:

-- Use a case statement to protect the convert operator from the invalid data

select

*

from

(

select

case when bar like '__/__/____' then

convert( smalldatetime, bar, 103 )

else

null

end as bardate

from

foo

where

bar like '__/__/____'

) as derived

where

bardate < getdate()

go



drop table foo

go



The workaround I discovered is simple but ugly. I invite your comments...



alasdair.

View 5 Replies View Related

Transact SQL :: Confirmation Of UNION ALL Query For INSERT INTO Temp Table

Jul 21, 2015

I have the following UNION ALL query with SELECT INTO @tblData temp table. I would like to confirm if my query is correct.

In my first SELECT statement, I have INSERT INTO @tblData.

Do I need another INSERT INTO @tblData again in my second SELECT statement after UNION ALL?

DECLARE @BeginDate as Datetime
DECLARE @EndDate as Datetime
SET @BeginDate = '7/1/2015'
SET @EndDate = '7/13/2015'
DECLARE @tblData table

[Code] ....

View 3 Replies View Related

How To Lock A Table So Others Cannot Lock It

May 23, 2001

Hi,

I want to lock a table so others cannot lock it but able to read it inside transactions.

The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction

I have tried the coding above, it won't prevent others from locking table1.

So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?

Thank you for any help

View 1 Replies View Related

Error: A Deadlock Was Detected While Trying To Lock Variable X For Read Access. A Lock Could Not Be Acquired After 16 Attempts

Feb 2, 2007

I simply made my script task (or any other task) fail

In my package error handler i have a Exec SQL task - for Stored Proc

SP statement is set in following expression (works fine in design time):

"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"

From progress:

Error: The Script returned a failure result.
Task SCR REIL Data failed

OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.

Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

And how did I get 4 errors? - I only set my script task result to failure

View 11 Replies View Related

Confirmation Please

Apr 17, 2007

I have a task to perform a new SQL Server 2005 installation at a client.
They have a system with 10 external SCSI drives, each of 72GB. They only have one database of 80GB in use at this system.

1) Userdata: I think that I will put 5 disks at SCSI channel 1, with RAID 3 or 6, with formatted space of 140GB.
2) Log: I think that I will put 3 disks at SCSI channel 2, with RAID 5, with formatted space of 70GB.
3) TempDB: I think that I will put 2 disks at SCSI channel 3, with RAID 0, with formatted space of 140GB.

System already has two built-in drives (RAID 1) for operating system, where I think I will put system databases.

I am open for suggestions and improvements!


Peter Larsson
Helsingborg, Sweden

View 19 Replies View Related

Need Confirmation

May 22, 2007

I am working with a client who has all databases, logs, tempdb and user/system databases on a single raid set.
In order to make some speed improvement, we have decided to move log files to a second raid controller card.

To accomplish this, we are thinking about taking these steps:

1) Detach database XYZ.
2) Stop database service.
3) Create a new partition on new controller card.
4) Copy old log files from I: to new K: drive.
5) Remove drive letter I: from system.
6) Change drive letter K: to I:
7) Attach database

My question is, does SQL Server recognize the log files automatically, since they are placed at same logical position (i: drive)?


Peter Larsson
Helsingborg, Sweden

View 5 Replies View Related

Lock SQL Table Using Asp.net

Jul 23, 2004

Hi,all:
This problem almost drives me crazy, hope I can get some hints from you guyz!!!
Ok, here is the situation:
I wanna only one users 2 modify the data(update) from my page each time, and if at the same time, there are some other users connecting my database through .aspx page, they can only browse the data until the first users finish updating.
It seems I need to implement locking the database, but I am not sure how I am gonna do that using asp.net!!!
Thanx in advance!

View 5 Replies View Related

How To Lock A Table Or Row In Sql?

Nov 1, 2005

hi, i have an application that updates some records in sql tables, and i want to do a  web application that updates records in the some database-table(sql) so, my question is how can i lock the row or table  so i dont have concurrency problems.tnx in advance.

View 2 Replies View Related

Table Lock...

Nov 5, 2007

Hello Friends,
I am having a VB application running for the SQL SERVER DB. The VB application is installed on the multiple of PCs in the network. Now when I am trying to fetch the same from all the different PCs simultaneously, its amazingly fast. But the issue comes when I am trying to update the same table (but different rows) from the different PCs simultaneously. The time taken is directly proportaional to the number of users. I am not getting what could be the problem? Can any one suggest me the approch? Is it some related to table / row / page locking? As all the connections are trying to update on the same table. I checked the isolation level. Its default, "READ COMMITTED". Kindly suggest...




Thanks in advance........
Rahul Jha

View 14 Replies View Related

Table Lock

Jun 5, 2008

Hi Gurus,

How can we know that particular table is locked.



Thanks,
ServerTeam

View 1 Replies View Related

How To Lock A Table

Mar 21, 2006

Hai to all,

I want to lock a patricular table during my transaction is this possible, for Example

Create sp() as
BEGIN
Lock Table
Do Some work
Relaese the Lock
END

like this.

View 3 Replies View Related

Table Lock

Feb 10, 2008

Is there any way to lock tables in SQL Server?

View 2 Replies View Related

Table Lock

Jul 23, 2005

Hi,If I run an insert statement from the query analyzer and then try toopen the table from enterprise manager then it takes long time to openthe table. But this problem dissapears when i put the statement insideBegin/End Transaction statement.Any idea why this is happening?Thank in advance.Taw.

View 1 Replies View Related

Table Lock

Jul 23, 2005

Which lock type or isolation level should I use to be sure that no onewill read or write or do anything with the table I'm using?Code block should look something like this:lock tableread value from tablechange value to new_valueupdate table set value = new_valuerelease lockWhile I'm changing the value absolutly no one should be able to readfrom the table.

View 7 Replies View Related

Table Lock

Nov 21, 2007

How do I do a simple table lock?

Thanks,

MeTitus

View 4 Replies View Related

Table Lock

May 6, 2008

Hi Friends

In my server i am getting this error how can i slove this error
could u people any body please help me

error:
Table Lock Wait Time Per Second is 867.3330
milliseconds per second, which exceeds threshold of 10
milliseconds per second."



Thanks
subu

View 1 Replies View Related

Confirmation Of Success

Feb 13, 2004

Just a thought.

If an issue is resolved please note that the problem has been resolved. Because there are "many ways to skin a cat" it would be helpful to anyone else with a similar problem (or someone trying to learn) what the solution was.

View 7 Replies View Related

Table Lock-urgent

Oct 3, 2000

Hi,

How can I see which table is locked up by some particular process?
I know that I can view paricular spid from 'current activity'. But is there any way I can see which table is the center of problem?
I really appreciate your help..

View 1 Replies View Related

How To Lock The Row Or Table When Update?

Nov 4, 2004

Hi,
I am working on a project which need to produce a sequential certificate number, Everytime I need a new certificate number, I need to find out what is the max number in the database and then the new certificate number just max+1.
But how can I block another
transaction to check what is max certi. number while this transaction is in the middle of writing the new certificate number(max+1) into database . Does ADLockOptimistic work in this case? Here is the code:
My database sql 2000.

cmdTemp.CommandText="Select max(certificateNumber) from product_table where certificateNumber<> 8888888"
set cert_info=Server.CreateObject("ADODB.RecordSet")
cert_info.Open cmdTemp, , AdOpenKeySet, adLockOptimistic
If Not cert_info.EOF then

certnumber=cert_info(0)+1

End if
cert_info.close
set cert_info=nothing

View 10 Replies View Related

To Give A Table Lock

Oct 14, 2005

How can i give a table wise exclusive lock in MSSQL Server ?

I got the description but, How can I apply this ?
The sql :
LOCK TABLE <tablename> IN EXCLUSIVE MODE
is not working.

Is there any query/method to do this ?
Please help ...

thanks


About Exclusive locks
--------------------
Exclusive (X) locks are used for data modification operations, such as UPDATE, INSERT, or DELETE.

Other transactions cannot read or modify data locked with an Exclusive (X) lock. If a Shared (S) exists, other transactions cannot acquire an Exclusive (X) lock.
--------------------

View 2 Replies View Related

Keeping A Lock On Table Or Row

Feb 5, 2004

I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?

View 4 Replies View Related

How To Remove Lock Associated With Table

Aug 7, 2013

I have a query like this

SELECT TOP 1 * FROM ITAM_RAMS_STAGING

not getting value...seems like lock associated with that table

how to remove the lock if exists

View 2 Replies View Related

Explicitly Lock A Table?

Apr 23, 2007

I have multiple applications which can potentially update the same trigger simultaneously. Each application:

(1) Reads the contents of the current trigger
(2) Creates a new trigger based on the current contents
(3) Drops the trigger
(4) Creates the new trigger

I need to insure that once one application begins step (1), then no other application can start step (1) until step (4) has completed.

Any ideas on how this can be done? Some databases have a concept of locking tables explicitly, so for them, I can do:

(0) lock table foo;
(1) - (4)
(5) unlock table foo;

Is there such a concept in mysql or is there another way of accomplishing this?

Thanks,
Eric

View 6 Replies View Related

Table Lock - Issue

Jul 27, 2007

Folks,

I have an update statement and it looks like it is holding exclusive lock on the table and does not release it until it completes. a PAGLOCK hint has been specified on the update statement and i think it is being ignored. It is a transaction database.so, other queries accessing that table has to wait for it to complete and thus causing timeouts.The Update statement is also causing high IO and CPU utilization. How do I

1) reduce the granularity level to prevent the locking
2)any ideas on optimizing the query?

Here is the query:

UPDATE Customers
SET IndexStart = TMP.IndexStart,
IndexPosition = TMP.IndexStart,
IndexStop = TMP.IndexStop,
IndexLevel = TMP.IndexLevel
FROM Customers AS C WITH (PAGLOCK)
INNER JOIN #tmp_IndexBCs AS TMP WITH (TABLOCKX) ON C.ID= TMP.ID

Thx
Sri

View 7 Replies View Related

Table Lock Issue?

Oct 6, 2007

Hi All

We are facing atable lock issue , while running a SSIS package
We have two flows . one for insert and other for update into the same target table.
The update is done using a procedure.

We have disabled the lock table option in the target( in the insert flow)
The first set of records which come for updates flow fine.
But when the records start coming into the insert flow, the data flow stops.

Is this a table lock issue?
How do i set the commit interval at the update flow?
can someone lpease help me out of this situation

Thanka and Regards
Preethe

View 1 Replies View Related

How Do You Lock An Entire Table?

Jul 23, 2005

Hi,I need to lock a table so that Inserts are prevented as well as deleted andupdates. At present I'm thinking this might do it:SELECT * FROM myTable WITH(UPLOCK)but then again I'm not sure whether this will cover the insert case.Thanks,Robin

View 5 Replies View Related

Lock Table Problem

Dec 6, 2005

HiI want to lock a table using JDBC as I want to perform some query's (readand write) in exclusive mode. Different threads will be executing the samecode simultaneously. I am using the following statementsStatement stmt = connection.createStatement();stmt.executeUpdate("lock table STATUS_TBL in EXCLUSIVE mode");but am getting the following error[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrectsyntax near 'STATUS_TBL'.can somebody tell the correct syntax for the lock table statement.Thanks

View 1 Replies View Related

Alter Table Lock?

Sep 11, 2007

Hi!
I use proc handling special business logic (I also use constraints, indexes for that ;-)
Now I have a situation where I should check multiple rows with an proc.
Preventing multi-user issues I want to lock the table (yes, yes potential performance issue, but in this case there are few simultaneous jobs) - in Oracle I could lock the table, but what to do in SQL Server?

Maybe you have an better alternative, then let me hear ;-)

Or should I use "begin transaction"...

Thanks for help

View 1 Replies View Related

Lock 'Childreen' Table

Oct 26, 2006

Hi!

I'm new in SQL and I´ve been the problem bellow:

I lock a record in one foreign table and automaticaly SQL lock the record that matches on primary table.

But this occours just in some primaries tables and not in all. I need that just the table that are of SELECT are lock. How can I do this?

Example:

** At open of the invoice:

SET ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION

SELECT * FROM INVOICE WITH (ROWLOCK UPLOCK) WHERE ( ID = 15 )

******* at the end of invoice:

INSERT INTO INVOICE ........

COMMIT TRANSACTION

END



******* The relationship are:

INVOICE <>> ITENS_INVOICE

ITENS_INVOICE <>> PRODUCTS

CUSTOMER <>> INVOICE

VENDORS <>> INVOICE

Just the INVOICE and PRODUCTS record´s involved in Transaction are lock. ( The CUSTOMER and VENDORS are not locked for example )

But I need that just INVOICE record be locked.

Thank´s for all and sorry my English.



Igor Sane

São Paulo - Brazil

View 6 Replies View Related

Row Lock Versus Page Lock In SQL 2000.

Apr 7, 2004

Hi
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.

Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.

At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.

So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.

Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :

Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.

How can I resolve this issue? How can I get lock on wanted rows instead of entire page?

Please advice. Thanks a bunch.

Pankaj

View 1 Replies View Related

DTS Packages- Seeking Confirmation

Apr 8, 2008

Hello all,
I have 2 primary key fields the ssn and refnum... if the data in the file is duplicated it will not import to my table rights even though i am using DTS to do my import, correct? or do I need to add an extra validator in there?

View 2 Replies View Related

MSDE Confirmation Question

Jun 7, 2005

We have a project at work that requires us to run an msde database from cd. My sup. and I disagree on if this can be done or not. We currently have an application written in asp that uses an access db. We copy the *.mdb file and all the asp files to cd and use the app. to query the db. Now, we have a client who wants the same capability only using msde. They will not allow us to install the full version of sql on there machines. No one in the dept wants to re-write this app. so we're trying to figure out if we can make it work with msde. So, My first question is, does anyone think that it is possible to just copy the *.mdf file to cd (mind you WITHOUT the *.ldf file... can't write to a cd)andMy second question is, can a sql db run without both files in any situation(mdf,ldf)? (I believe this is impossible, but would like to here it from someone with more experience)Any comments are greatly appreciatedthanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved