How to make an insert table lock

Hi There

i Have some t-sql that basically does something like :

INSERT INTO TABLEA SELECT * FROM TABLEB

There are alot of rows involved and sp_lock tell me that a table lock is being put on TABLEA during this insert.

This is a big problem for us. I have often used locking hints on selects etc but never an insert, how can i get the insert to never use a table lock ?

Thanx

You always have a lock on insert on something and have no control over the escalation of locks. Hints are only a SUGGESTION of how the server should handle the query. The server determines the locks used based on the statement.

In your case, it is locking the table, because it has no idea how many inserts will be done or where they will be placed in the physical table.

You can set your read level to "READ UNCOMMITTED" and that will allow people to read the table during the insert process.
|||

Hi Tom

All good suggestions we have thought of but not possible.

Here is the situation, we are converting in new branch data into a central database, so we know that all rows inserted will be new data and at the end of the clustered index.

The other process doing the read is ETL which will never be interested in this new data, however the way it works combined with slowly changing dimensions we have to set it to read committed or the ETL will not work properly. The inserts are essentially locking an entire table and will never touch any of the rows that the ETL is trying to read (there are no updates) , however obviously the table lock pretty much stops all ETL.

So basically we are not sure what to do?

ALl i can do so far is commit as often as possible , but this only slightly helps the situation, bottom line is i wanted to know if there was anyway to control locking with an insert but you have answered that.

Thanx

|||With the more information you provided, I would change your INSERT into a BCP or BULK INSERT.

The INSERT...SELECT * is pretty slow. If you export the data and use BCP or BULK INSERT, you will be 100 times (or more) faster and will lock the target table for a much shorter time. Also set the batch size to a number like 10,000 or 50,000 which will insert that many records as 1 transaction and drop the lock. That might let the ETL lock the table, and delay the import, but the ETL will finish.

This might fix your problem.

|||

Hi Tom

Thanx i will try that.

However the process must be automated, so i guess i will have to write say an ssis package that first exports the data to file and then uses bcp to import it back into the other tables?

Thanx

|||

Hi, Tom.

Is it possible to perform a BULK INSERT from one table to another in the same SS05 database, where both tables have the same structure, and one has been TRUNCATED to accept the replacement data from the other?

I have been studying the T-SQL documentation and I keep noticing "glimmers of hope" that make it appear as if it is possible (without having to export the data and then import it), but I am unable to discern the syntax for the INSERT statement.

Does this look anywhere close to being correct?

INSERT INTO table_a (IGNORE_CONSTRAINTS)

SELECT *

FROM OPENROWSET(BULK

(And that's where I get stuck, since BULK seems to want a reference to an external data file, NOT a table within the same database on the same server, with only a different table name.)

Dan

P.S. Maybe it must be done outside the T-SQL environment, such as shown in VB and C# examples at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx ? I was hoping there was a T-SQL solution.

(I just noticed an interesting comment in the C# example:

// Open the destination connection. In the real world you would
// not use SqlBulkCopy to move data from one table to the other
// in the same database. This is for demonstration purposes only.

So what is the "real world" solution for bulk copying data from one table to another in the same database?)

|||

Tom

Any ideas ?

To uswe bulk insert i would first require a process to export to file and then back in again the time again on bulk insert would perhaps be lost on exporting to data to file and re-importing with bulk insert using SSIS or something ?

Is there a way to bulk insert data from 1 table to another using just tsql , and no exported data to file ?

Thanx

|||It is ALWAYS faster to export the file and bulk insert again, even with the added overhead of exporting it to file. The other methods require tons of writes to tempdb and log files, etc.

You should really try it as bulk insert. You wil be much happier.

The "real world" solution, as you would see from the MS copy data options in DTS and SSIS, are exporting the data and using BCP/BULK INSERT to reinsert them, because it is much faster.

|||

Tom,

Thanks.

Dan

|||

Hi Tom

Thanx, i was hoping to be able to do it in tsql and avoid ssis.

By the way is there now ay to bulk insert between tables ? Must bulk insert have a flat file source, seems that way from BOL.

Cheers

|||Look at the "bcp out" command. You can do that from tsql using EXEC.

How to MAKE @@LOCK_TIMEOUT effect to every session

Example,I want to let any lock be cancel if the lock keep 2 minute.
Or is there has any other parametes about user's lock and how to use?
thanks
I'm afraid that there is no such setting. you have to set it from the client applications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fx_man" <welcome_you@.sina.com> wrote in message news:ejbP7$KQEHA.3532@.TK2MSFTNGP12.phx.gbl...
> Example,I want to let any lock be cancel if the lock keep 2 minute.
> Or is there has any other parametes about user's lock and how to use?
> thanks
>

How to MAKE @@LOCK_TIMEOUT effect to every session

Example,I want to let any lock be cancel if the lock keep 2 minute.
Or is there has any other parametes about user's lock and how to use?
thanksI'm afraid that there is no such setting. you have to set it from the client applications.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fx_man" <welcome_you@.sina.com> wrote in message news:ejbP7$KQEHA.3532@.TK2MSFTNGP12.phx.gbl...
> Example,I want to let any lock be cancel if the lock keep 2 minute.
> Or is there has any other parametes about user's lock and how to use?
> thanks
>sql

How to MAKE @@LOCK_TIMEOUT effect to every session

Example,I want to let any lock be cancel if the lock keep 2 minute.
Or is there has any other parametes about user's lock and how to use?
thanksI'm afraid that there is no such setting. you have to set it from the client
applications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fx_man" <welcome_you@.sina.com> wrote in message news:ejbP7$KQEHA.3532@.TK2MSFTNGP12.phx.gbl.
.
> Example,I want to let any lock be cancel if the lock keep 2 minute.
> Or is there has any other parametes about user's lock and how to use?
> thanks
>

How to Log Locks within a Database

I was wondering if there was a simple script that could be run or a way to h
ave an entry created in a log file whenever a SQL Lock occurs. Specifically
if it could also give the username of the user running the query that creat
ed the lock. Thank you.You could have a profiler trace running. But be aware that the locking
activity in SQL Server can be *very* high! Do a test first so you don't
overload the system with the logging.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.|||Tibor, Thanks for the reply. I tried profiling but noticed the performance
decrease. All I really would like is basically something that alerts me via
either a log file I can check daily or a netsend message that tells me when
the database gets blocked
by a SPID and who it is that is causing the block. I don't actually need to
monitor every lock as I found out with the profiler. Thank you.|||INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/defaul...kb;EN-US;271509
Also have a look at
INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;EN-US;224587
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/defaul...kb;EN-US;243589
INF: Understanding and Resolving SQL Server 7.0
or 2000 Blocking Problems
http://support.microsoft.com/defaul...b;EN-US;Q224453
As well as these articles themselves, they contain links in them to lots
of other performace troubleshooting type articles. Lots of good stuff !
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.

how to lock the store procedure and allow one process to acces it at a time

Hello:

I run one process that calls the following the store procedure and
works fine.

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

END
GO

If I run two processes that call the above store procedure, I might
occasionally get the dirty data of int_parameter_dbt. I guess that is
caused by two processes accessing to the same resource simultaneously.
Is there any way to lock the store procedure call from MSSQL Server
and allow only one process to access it at a time?

Thanks for help.

Best Jin"Jin" <texlqj@.hotmail.com> wrote in message
news:82b49cd5.0401131104.7c12efc5@.posting.google.c om...
> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

Here is one possible approach, using an UPDATE syntax specific to MSSQL:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

declare @.val int

UPDATE system_parameter_dbt
SET @.val = int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

select @.val

END
GO

Alternatively, you can use a locking hint:

create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN

begin tran

SELECT int_parameter_dbf + 1
FROM system_parameter_dbt with (UPDLOCK)
WHERE parameter_name_dbf = 'seqNum'

UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'

commit

END
GO

Simon|||Sure. You could do it transactionally at serializable isolation level...
Joe

Jin wrote:

> Hello:
> I run one process that calls the following the store procedure and
> works fine.
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
> END
> GO
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
> Thanks for help.
> Best Jin

How to lock out everyone but me?

I'm working with SQL Server Express, and I want to configure a named instance so that only the 'sa' user and a specified SQL Server user with a specified password have access. In particular, I'm trying to lock out BUILTIN\Administrators. Furthermore, I need to be able to do this from a command-line, since I want to configure it in a script. Nothing I do seems to work.

I've attempted to use sqlcmd and the T-SQL call ALTER LOGIN [BUILTIN\Administrators] DISABLE, but that returns the error "Cannot alter the login 'BUILTIN\Administrators' because it does not exist or you do not have permission."

What I can (apparently) successfully do is run DENY CONTROL TO [BUILTIN\Administrators]. This runs without reporting an error. However, after running it against the 'master' database and the specific database in my named instance I care about, I can still run the following:

sqlcmd -S (local)\MyInstance -d MyDB -Q "select * from my_table"

and see the contents of my_table.

What do I need to do to restrict access exclusively to 'sa' and other SQL users I designate?

Have you tried simply dropping the builtin\administrators login?

"Drop login [builtin\administrators]"

You can check sys.server_principals for a list of all logins. This seemed to work for me, although I tested this on Developer Edition and not Express Edition.

Sung

|||

I think the right expression would be to deny connection to builtin administrator under normal circumstances.

I want to clarify that actually stopping a box administrator from accessing SQL Server instance (and any other service for that matter) is practically impossible as your adversary has control over the OS and most likely the hardware.

You need to trust the box admin to some extent, and a proper channel and policies to monitor unauthorized activities (i.e. company policies, box auditing, etc.) should be in place to keep a box administrator honest. If you are hosting any data on a machine where you don’t trust the box administrator, make sure you don’t store (and I would even say don’t manipulate) any valuable information in clear text on the system you don’t trust.

If your objective is to keep an honest box administrator honest and deter her from accessing SQL Server, revoking access as Sung mentioned should be enough. In SQL Express you additionally would have to drop the [Builtin\users] account. SQL Express is designed OOB to grant access to the instance (with low privileges) to any user in the system in order to take advantage of some SQL Express-specific features.

BTW. Keep in mind that in SQL Server 2005 it is sufficient to start the server in single user mode. The reason for this is to allow a box admin to recover the system in case of emergency.

I hope this information will be useful.

-Raul Garcia

SDE/T

SQL Server Engine

How to Lock out DBA from Salary tables

Is there a way to lock out DBAs from accessing certain tables e.g. Salary ta
bles.
If so what permissions need to be set?Peter,
No! The SysAdmin account is GOD! There is no way to prevent a SysAdmin
account from doing anything it wants with any of the tables or databases.
If you have sensitive information, then you should consider encrypting the
information using some mechanism unknown to your DBA.
Chief Tenaya
"Peter" <peter@.offtomexico.com> wrote in message
news:F58B95B8-640B-45B7-A90C-BCFDA4FBDA3E@.microsoft.com...
> Is there a way to lock out DBAs from accessing certain tables e.g. Salary
tables.
> If so what permissions need to be set?|||A DBO yes. If your DBA is a member of the Sysadmins group, then the answer
is no.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

How to lock only the lines in the result set ?

For a SELECT on a given table, how to lock only the lines in the result set,
and not all the lines used in the query (I use SET ROWCOUNT to limit the
number of results) ?
I tryed ROWLOCK but it locks all the lines matching the query, even the one
that are discarded by rowcount.
Any workaround for SQL2000 ?
Same problem in 2005 ?Why do you want to do that. You explain your problem so that we will be able
to help you out
--
Regards
R.D
--Knowledge gets doubled when shared
"SoftLion" wrote:

> For a SELECT on a given table, how to lock only the lines in the result se
t,
> and not all the lines used in the query (I use SET ROWCOUNT to limit the
> number of results) ?
> I tryed ROWLOCK but it locks all the lines matching the query, even the on
e
> that are discarded by rowcount.
> Any workaround for SQL2000 ?
> Same problem in 2005 ?
>
>|||> Why do you want to do that. You explain your problem so that we will be
> able
> to help you out
MyTable( id int, dateAdded datetime )
CREATE PROCEDURE test
as
SET ROWCOUNT 100
SELECT
id
FROM
MyTable WITH (UPDLOCK,ROWLOCK,READPAST)
ORDER BY
dateAdded asc
GO
** In a query:
begin tran
exec test
(100 results retreived)
** In another query
begin tran
exec test
(0 results retreived, because READPAST jumped over ALL rows of MyTable,
because ALL rows are locked, because of the ORDER BY)|||If you use a TOP 100 and have an index (preferably a clustered) on DateAdded
it should not block the entire table, just the first 100.
Andrew J. Kelly SQL MVP
"SoftLion" <nospam@.replyToGroup.com> wrote in message
news:%23Gw7w%2372FHA.3292@.tk2msftngp13.phx.gbl...
> MyTable( id int, dateAdded datetime )
> CREATE PROCEDURE test
> as
> SET ROWCOUNT 100
> SELECT
> id
> FROM
> MyTable WITH (UPDLOCK,ROWLOCK,READPAST)
> ORDER BY
> dateAdded asc
> GO
>
> ** In a query:
> begin tran
> exec test
> (100 results retreived)
> ** In another query
> begin tran
> exec test
> (0 results retreived, because READPAST jumped over ALL rows of MyTable,
> because ALL rows are locked, because of the ORDER BY)
>|||> If you use a TOP 100 and have an index (preferably a clustered) on
> DateAdded it should not block the entire table, just the first 100.
I can't use a top 100 because you SQL Server doesn't accept 'select top
@.maxLines' queries.
That's why I'm using a SET ROWCOUNT.
I will try adding a non-clustered index on dateAdded as you suggest (I
already have a clusted index on another more important column).|||> I can't use a top 100 because you SQL Server doesn't accept 'select top
> @.maxLines' queries.
> That's why I'm using a SET ROWCOUNT.
SQL2005 does support a variable on TOP by the way.

> I will try adding a non-clustered index on dateAdded as you suggest (I
> already have a clusted index on another more important column).
Having a clustered index does not render other nonclustered indexes useless.
They supplement each other.
Andrew J. Kelly SQL MVP
"SoftLion" <nospam@.replyToGroup.com> wrote in message
news:Ofy7WL43FHA.632@.TK2MSFTNGP10.phx.gbl...
>
>

How to lock only one row

Hello everyone,
I need to:
1. Get data from a table row
2. Lock a row for delete
3. Delete it
The problem is that the code that I use locks the whole table, not just one
row. Does anyone know how to limit the lock to one row for one table?
Thank you,
Helen
P.S.That is what I do:
EXEC SQL
DECLARE ACURS CURSOR FOR
SELECT
"MDC7_PROG_ID",
"MDC7_REV_DATE"
FROM
"MDC7"
WHERE
"MDC7_PROG_ID" = :FMDC7-PROG-ID
END-EXEC
EXEC SQL
OPEN ACURS
END-EXEC
IF SQLCODE <> 0
GO TO END-OF-PROGRAM.
EXEC SQL
FETCH ACURS
INTO
:FMDC7-PROG-ID
, :FMDC7-REV-DATE
END-EXEC
EXEC SQL
DELETE MDC7
WHERE CURRENT OF ACURS
END-EXEC.
IF SQLCODE <> 0
EXEC SQL
CLOSE ACURS
END-EXEC.Helen Stein wrote:
> Hello everyone,
> I need to:
> 1. Get data from a table row
> 2. Lock a row for delete
> 3. Delete it
> The problem is that the code that I use locks the whole table, not
> just one row. Does anyone know how to limit the lock to one row for
> one table?
>
Do you need to be using cursors for this? If not, you can just issue the
delete directly. Unless there are no indexes to help SQL Server locate
the row, you should not see any table locking for the operation.
For example:
Declare @.ID INT
Declare @.OtherKey INT
Set @.OtherKey = 1000
Select @.ID = MyID
From TableA
Where OtherKey = @.OtherKey
If @.ID IS NOT NULL
Delete From TableB Where ID = @.ID
David Gugick
Imceda Software
www.imceda.com|||Why do you need to use a cursor to do a DELETE like this? You are deleting
all rows that are in the current cursor. Wouldn't it be much easier and
faster to write
DELETE MDC7
WHERE MDC7_PROG_ID = <what ever value FMDC7-PROG-ID represents>
How many rows of how many is this operation deleting?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:e4aLcRQFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello everyone,
> I need to:
> 1. Get data from a table row
> 2. Lock a row for delete
> 3. Delete it
> The problem is that the code that I use locks the whole table, not just
> one
> row. Does anyone know how to limit the lock to one row for one table?
> Thank you,
> Helen
> P.S.That is what I do:
>
> EXEC SQL
> DECLARE ACURS CURSOR FOR
> SELECT
> "MDC7_PROG_ID",
> "MDC7_REV_DATE"
> FROM
> "MDC7"
> WHERE
> "MDC7_PROG_ID" = :FMDC7-PROG-ID
> END-EXEC
>
> EXEC SQL
> OPEN ACURS
> END-EXEC
> IF SQLCODE <> 0
> GO TO END-OF-PROGRAM.
>
> EXEC SQL
> FETCH ACURS
> INTO
> :FMDC7-PROG-ID
> , :FMDC7-REV-DATE
> END-EXEC
>
> EXEC SQL
> DELETE MDC7
> WHERE CURRENT OF ACURS
> END-EXEC.
>
> IF SQLCODE <> 0
> EXEC SQL
> CLOSE ACURS
> END-EXEC.
>|||The problem is your use of a cursor. It looks like you want something
like this (in TSQL):
SET @.mdc7_rev_date =
(SELECT mdc7_rev_date
FROM MDC7
WHERE mdc7_prog_id = @.mdc7_prog_id)
DELETE FROM MDC7
WHERE mdc7_prog_id = @.mdc7_prog_id
AND mdc7_rev_date = @.mdc7_rev_date
David Portas
SQL Server MVP
--|||The reason why I am using a cursor is because I need to make sure that in
between my select and delete the other user cannot access the row I just
read. Is there any other way of doing this?
Thanks for everybodys reponses.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OQdynVQFFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Helen Stein wrote:
> Do you need to be using cursors for this? If not, you can just issue the
> delete directly. Unless there are no indexes to help SQL Server locate
> the row, you should not see any table locking for the operation.
> For example:
> Declare @.ID INT
> Declare @.OtherKey INT
> Set @.OtherKey = 1000
> Select @.ID = MyID
> From TableA
> Where OtherKey = @.OtherKey
> If @.ID IS NOT NULL
> Delete From TableB Where ID = @.ID
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Isn't handled through transaction isolation?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:Oub2T3QFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> The reason why I am using a cursor is because I need to make sure that in
> between my select and delete the other user cannot access the row I just
> read. Is there any other way of doing this?
>
> Thanks for everybody's reponses.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OQdynVQFFHA.2676@.TK2MSFTNGP12.phx.gbl...
>

How to lock entire database?

Hi,
Is there a way to lock entire database? What I am supposed to do is kind of
replication:
- a number tables exist in database, these tables should be updated
- so, temporary tables are created and filled with data
- these temporary tables are renamed to final name (using sp_rename)
This is something like 'restore database', but should not terminate database
connections.
During renaming no process should access tables. How to ensure if no
transaction is running on destination tables? I can check sp_lock output for
exclusive locked tables, wait for transactions to finish, than run my
sp_rename script.
However the final question is: how to prevent NEW transactions? Or: how to
queue transactions to run AFTER sp_rename
Regards.
Piotr.Using "ALTER DATABASE" and setting it to SINGLE_USER state.
AMB
"Piotr Strycharz" wrote:

> Hi,
> Is there a way to lock entire database? What I am supposed to do is kind o
f
> replication:
> - a number tables exist in database, these tables should be updated
> - so, temporary tables are created and filled with data
> - these temporary tables are renamed to final name (using sp_rename)
> This is something like 'restore database', but should not terminate databa
se
> connections.
> During renaming no process should access tables. How to ensure if no
> transaction is running on destination tables? I can check sp_lock output f
or
> exclusive locked tables, wait for transactions to finish, than run my
> sp_rename script.
> However the final question is: how to prevent NEW transactions? Or: how to
> queue transactions to run AFTER sp_rename
> Regards.
> Piotr.
>|||U?ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> said:

> Using "ALTER DATABASE" and setting it to SINGLE_USER state.
This command, however, won't allow change when connections exists (or are
made) to database.
Regards.|||You can use the ROLLBACK option to force out existing users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piotr Strycharz" <Piotr.Strycharz@.antispam-account.com> wrote in message
news:d69ip4$m0$1@.nemesis.news.tpi.pl...
> Uzytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> said
:
>
> This command, however, won't allow change when connections exists (or are
> made) to database.
> Regards.
>

how to lock a table?

hi, I have a problem on building an application that can be accessed by
multiple clients concurrently. I have a table called [sub_bg_id_allocation],
it has 2 int columns: bg_id and sub_bg_id. The business rule states that
sub_bg_id should be unique within a bg_id, e.g.
bg_id subbg_id
--
1 110
1 111
1 112
2 110
2 111
... ...
the subbg_id is started by 110 and incremented by 1 within a bg_id, actually
this [sub_bg_id_allocation] table is used for calculating new sub_bg_id. The
newly allocated sub_bg_id will be used for inserting a 'real' data record in
another table called [pro_child_subbg_info]. (don't ask me why I need the
[sub_bg_id_allocation] table, it's already there and used by existing system
)
Now I write a stored procedure like this:
CREATE PROCEDURE [dbo].[LiteCreateDDISubbg]
@.bg_id int,
@.subbg_id int output
@....
AS
BEGIN
BEGIN TRAN
-- calculate subbg_id
declare @.last_subbg_id int, @.subbg_id int
select @.last_subbg_id = max(sub_bg_id) from sub_bg_id_allocation where bgid
= @.bg_id
if @.last_subbg_id >= 110
set @.subbg_id = @.last_subbg_id + 1
else
set @.subbg_id = 110
-- subbg_id allocation
insert sub_bg_id_allocation (bg_id, subbg_id) values (@.bg_id, @.subbg_id)
-- check error immediately
if @.@.error <> 0
goto ErrorHandler
-- subbg info.
insert pro_child_subbg_info (
bg_id, sub_bg_id, ...) values (
@.bg_id, @.subbg_id, @....)
-- check error immediately
if @.@.error <> 0
goto ErrorHandler
-- end successfully
COMMIT TRAN
RETURN 0 -- ***** 0 means ok *****
ErrorHandler:
ROLLBACK TRAN
RETURN -1 -- ***** -1 means fail *****
Is there any problem in a concurrent client scenario? I wonder about the
MAX() function works properly in concurrent case, should I lock the
[sub_bg_id_allocation] table within this stored procedure? How can I lock it
?
Thanks a lot!Hi
You may want to add a hint such as UPDLOCK to the select statement.
http://msdn.microsoft.com/library/d...br />
4ox9.asp
This will potentially reduce concurrency such as
http://support.microsoft.com/defaul...kb;en-us;179362
so you may want to look at using an identity instead.
John
"nonno" wrote:

> hi, I have a problem on building an application that can be accessed by
> multiple clients concurrently. I have a table called [sub_bg_id_allocation
],
> it has 2 int columns: bg_id and sub_bg_id. The business rule states that
> sub_bg_id should be unique within a bg_id, e.g.
> bg_id subbg_id
> --
> 1 110
> 1 111
> 1 112
> 2 110
> 2 111
> ... ...
> the subbg_id is started by 110 and incremented by 1 within a bg_id, actual
ly
> this [sub_bg_id_allocation] table is used for calculating new sub_bg_id. T
he
> newly allocated sub_bg_id will be used for inserting a 'real' data record
in
> another table called [pro_child_subbg_info]. (don't ask me why I need the
> [sub_bg_id_allocation] table, it's already there and used by existing syst
em)
> Now I write a stored procedure like this:
> CREATE PROCEDURE [dbo].[LiteCreateDDISubbg]
> @.bg_id int,
> @.subbg_id int output
> @....
> AS
> BEGIN
> BEGIN TRAN
> -- calculate subbg_id
> declare @.last_subbg_id int, @.subbg_id int
> select @.last_subbg_id = max(sub_bg_id) from sub_bg_id_allocation where bgi
d
> = @.bg_id
> if @.last_subbg_id >= 110
> set @.subbg_id = @.last_subbg_id + 1
> else
> set @.subbg_id = 110
> -- subbg_id allocation
> insert sub_bg_id_allocation (bg_id, subbg_id) values (@.bg_id, @.subbg_id)
> -- check error immediately
> if @.@.error <> 0
> goto ErrorHandler
> -- subbg info.
> insert pro_child_subbg_info (
> bg_id, sub_bg_id, ...) values (
> @.bg_id, @.subbg_id, @....)
> -- check error immediately
> if @.@.error <> 0
> goto ErrorHandler
> -- end successfully
> COMMIT TRAN
> RETURN 0 -- ***** 0 means ok *****
> ErrorHandler:
> ROLLBACK TRAN
> RETURN -1 -- ***** -1 means fail *****
> Is there any problem in a concurrent client scenario? I wonder about the
> MAX() function works properly in concurrent case, should I lock the
> [sub_bg_id_allocation] table within this stored procedure? How can I lock
it?
> Thanks a lot!|||Thanks John. How can I use identity in this case?
"John Bell" wrote:
> Hi
> You may want to add a hint such as UPDLOCK to the select statement.
> http://msdn.microsoft.com/library/d... />
z_4ox9.asp
> This will potentially reduce concurrency such as
> http://support.microsoft.com/defaul...kb;en-us;179362
> so you may want to look at using an identity instead.
>
> John
>
> "nonno" wrote:
>|||Hi
That would be a change in functionaility in that the subbg_id will be
continually increasing. Does the subbg_id really need to do what you are
trying to build?
When needed you can still rank the entries by using something
select a.bg_id, subbg_id as original_subbg_id, (select count(*) from
sub_bg_id_allocation b where a.subbg_id < b.subbg_id ) + 110 as
ranked_subbg_id FROM sub_bg_id_allocation a
John
"nonno" wrote:
> Thanks John. How can I use identity in this case?
> "John Bell" wrote:
>

how to lock a table or row in sql?

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.Have you tried google? Search fordatabase concurrency.
It's not a simple answer. There are different types of concerrency and different levels of locks. You need to undestand what is out there. Once you get the difference, you'll probably know what to use and how to do it.
Now go. Google it.|||

ok , i will try doing something like in this article..

http://www.15seconds.com/issue/030604.htm

tnx Alex.

How to lock a set of tables??

Hi all,

Let's image I have a set of tables (for ex. 3 tables) which are logically connected with each other (i.e. every time I'm changing something in one table I necessarily will make some changes to other 2 tables).
Well, now I have two application - one is changing these tables (synchronising tables' content with application's environment) and another one which time by time reading these tables and makes some operations base on tables' content.
The problem is - if the first application started changes but not commited transaction yet and the second application running SELECT clause, then I could get a "wrong" data in the second app, i.e. content of some tables will be updated but content of other tables are old.
The ways to solve this... I think that it's possible to lock these tables before making changes in the first application and unlock tables after changes are complete. Then the second application will be secured with up to date content. But I can't find out how to lock a set of tables. If anyone knows how to do it plase help! Any kind of help would be appreciated!

thnxWhy not begin a transaction before changing the first table and then commit or rollback after changing the last table?|||You can do it by 2 ways

1) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2)By Giving Lock hints on target tables (HOLDLOCK,TABLOCKX) --which is equivalent to SERIALIZABLE

more information on BOL for "lock hints"

vish

Originally posted by armen_gg
Hi all,

Let's image I have a set of tables (for ex. 3 tables) which are logically connected with each other (i.e. every time I'm changing something in one table I necessarily will make some changes to other 2 tables).
Well, now I have two application - one is changing these tables (synchronising tables' content with application's environment) and another one which time by time reading these tables and makes some operations base on tables' content.
The problem is - if the first application started changes but not commited transaction yet and the second application running SELECT clause, then I could get a "wrong" data in the second app, i.e. content of some tables will be updated but content of other tables are old.
The ways to solve this... I think that it's possible to lock these tables before making changes in the first application and unlock tables after changes are complete. Then the second application will be secured with up to date content. But I can't find out how to lock a set of tables. If anyone knows how to do it plase help! Any kind of help would be appreciated!

thnx

how to lock a row when perform SELECT statement

Hi,
i would like to know how to lock a table from other to view when select statement..can anyone help me with that?
i tried

SELECT * FROM IMIS_FRAMEWORK_RUNNING_NUMBER with (HOLDLOCK,ROWLOCK)
WHERE running_type=@.type;

but i m still able to see my row when i perform my SELECT STATEMENT from other query analyser..

Please help me with this..thank youCheck out this page, it shows you how to do what you're asking:

http://www.sql-server-performance.com/at_sql_locking.asp|||What you are asking to do is a sort of pessimistic lock, and this can be very dangerous in a web application. What happens e.g. when a user SELECTs the record and then their browser crashes?

You can read about concurrency control here:ASP.NET Optimistic Concurrency Control

Maybe if you explain more about what you are trying to do we can offer some suggestions.

Terri|||Thanks for helping ,

First of all i have a table to stores running number

tblRunningNumber
ID Desc LatestNumber
1 ModuleA 01
2 ModuleB 12

Then i wanna gat the latest runningnumber from tblRunningNumber n stores it in to my other table. For example i wanan get the latestNumber for ModuleA and stores it into ModuleA table.

To i need to have a stored procedure to lock the ModuleA rows n then update the latestNumber from 01 to 02 and at the same time update the 01 in my ModuleA database Table.

That y i need to lock the row..my issues is that when i do the select * statement it just hang there...until i close the connection or transactin is commited...

that the whole senarios that i m doing..can u give me any good suggestion on that coz i cannot use asp.net to control the running number so i decided to use storedprocedure .

Thanks alot.|||"can be very dangerous in a web application"

That's an understatement! Especially with a relatively unguarded select statement!|||But to ensure consistency, 'sometimes' you will have to enforce locking for the duration of a transcation. Can you tell us what would happen when a user SELECTs the record and then their browser crashes? The SELECT statement uses a HOLDLOCK. Isn't there a way to handle this crash scenario?|||

Holdlocks works on tables, pages, or rows it won't lock an entire database. If the commit and/or rollback is in the stored procedure, or contained in the batch sent to the SQL Server, then no. The batch and/or stored procedure will run to completion.

If you do something like issue this to the SQL Server: BEGIN TRANSACTION (Or start a transaction using the transaction object), then issue a SELECT ... (WITH HOLDLOCK) then crash before your ASP.NET application rollsback or commits the transaction, and the database server is remote, then yes, it'll be locked until the SQL Server realizes the connection is dead, and I'm not sure how long that would take. If it's a local SQL Server, then it realizes it immediate, and rollsback the transaction.

Also, the client crashing, disconnecting, going off-line, getting hit by lightning, etc means nothing. The server will continue executing it's page to completion no matter if the client is there or not, unless of course the page crashes itself, in which case the connection will be closed, and SQL Server will rollback it's transactions for those connections. Of course if the web server machine takes a complete dump (power loss, blue screen of death, etc), then SQL Server (if remote from the web server), will hold the lock until it realizes the connection is dead, and I'm not sure how quickly SQL Server detects a dead connection, but I'm guessing it'll be longer than you'd want in a web farm type environment.

How to lock a particular Row in a sql server

Hi! Group,
this is shiva shanker,
i have a problem , while insert a record in a table , i want to lock
the particular row. so that no one can insert the record during that
period of time.
plz help me,
thanks in adv.
shiva shanker.insert by definition is adding new data into a table. Your insert should not
affect others from inserting into the same table if the table is not
exclusive locked. Take a look at 'tablock or tablockx' in book online.
-oj
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1141793533.750309.243310@.i39g2000cwa.googlegroups.com...
> Hi! Group,
>
> this is shiva shanker,
> i have a problem , while insert a record in a table , i want to lock
> the particular row. so that no one can insert the record during that
> period of time.
>
> plz help me,
>
>
> thanks in adv.
> shiva shanker.
>|||Yes oj is right,
Shiva your requirement is not clear, is it osmething like when you insert no
other person should insert as you need a sequence of identity number specifi
c
to your insert. In that case go for table lock.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"oj" wrote:

> insert by definition is adding new data into a table. Your insert should n
ot
> affect others from inserting into the same table if the table is not
> exclusive locked. Take a look at 'tablock or tablockx' in book online.
>
> --
> -oj
>
> "shiva" <bany.shanker@.gmail.com> wrote in message
> news:1141793533.750309.243310@.i39g2000cwa.googlegroups.com...
>
>