Lock table until transaction finish.

HOw do I write a statement to lock a table until my transactions are finish?

e.g...

declare @.LatestRecordid int
declare @.Value varchar(10)
set @.Value='just a value'

'Lock Syantax here

insert into table_to_be_lock (tablecolumn) values (@.value)
set @.LatestRecordid=(select ident_current('table_to_be_lock')+1
Update summarytable set LatestId=@.LatestRecordid, ItsValue=@.value

'Lock Syantax end

The reason I need to lock it is because at the second line of code, I am afraid another person has run the same query and inserted another record b4 my second line of code is runned, thus I'll get the wrong Recordid pointing to the wrong "ItsValue".

I am pretty sure there is a syantax for it...but I just can't seem to remember...can anybody help?Not that it will happen because SQl Server Default isolation level is "Read Commited". In other words , other user will see only written records and SQL will handle locks internally (shared, update,Row level, Table level ..etc.)|||Use SCOPE_IDENTITY instead of ident_current - you will have your id.

Anyway it is good idea to open transaction before your insert, check for erros during insert and updates (rollback if it needs) and commit transaction.|||okay....wil try to read on scope....

err..btw..this is abit out of the topic...

I was wondering,which is much faster and less taxing on cpu processing?

select ident_current('tablename') vs select max(tableidentitycolumn)

as both gets the same value. I can't test it out as I only have a small table here, can't tell the difference.

can someone actually really test it out at their environment ?

I'm guessing ident_current will be much faster, correct?|||Patrick, ur guess is correct, max will parse through each row in the table. I guess ident_current,@.@.identity is like a global variable which stores the last ident value.|||So many problems would be so much easier if developers used UniqueIdentifiers instead of GUIDs.

Sigh. :(|||hmm..what are the difference??
isit the ones that is being generated usually during replication??
read somewhere that those datatype are bigger in size and may cause slower indexes...|||They may be bigger in size, but you can use them in very creative SQL schemas and avoid much of the overhead of tracking and looking up identity values.

blindman

Lock Resource

I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?
It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:

>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?

Lock Resource

I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:

>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?

Lock Resource

I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:
>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?

Lock escalation

MSSQL seems to be escalating locks to table locks while we do some write lock testing. What's the best way to handle MSSQL taking table locks? Does mssql support partitioning a table into different file groups?
thanksRE:
MSSQL seems to be escalating locks to table locks while we do some write lock testing.

Q1 What's the best way to handle MSSQL taking table locks?

Q2 Does mssql support partitioning a table into different file groups? Thanks

A1 The "best" way to handle MSSQL taking table locks depends on the specifics of the issue and the requirements of the design / situation.

For example (only) simply exceeding the number of available locks due to high levels of concurrent use would be very different from, say a situation in which inappropriately high isolation levels have been implemented. In the former one might increase memory available for locks, in the latter one might reduce the implemented isolation levels to the lowest possible levels that will not cause corruption issues.

A2 That type of requirement may be indirectly implemented in versions >=7.0.|||Originally posted by ryanveach
MSSQL seems to be escalating locks to table locks while we do some write lock testing. What's the best way to handle MSSQL taking table locks? Does mssql support partitioning a table into different file groups?

thanks

Hi!
Some facts.
If SQLServer cant lock a suitable row or range it is forced to lock the whole table. When sqlserver is forced to take so many row locks that it is more economic to take a table lock it "converts" the row locks to a table lock.

A1:Try to add suitable indexes to the tables in order to help sqlserver just to take row or range locks. If your aplication uses MTS or COM+ the isolation level is Serializable = expensive, but might be required for your app.

Local SQL connection String

How do I write a connection string in VB to use the local database. I have
something like this, but it does not work. I do not want to use the local
computer name. I want to make it generic so that the application can run on
any computer.
ThanksTry this:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False
Note there is no reference to a server so it uses the local server with
integrated security.
Bryce|||found the solution. I just have to use localhost.
Thanks
"Emma" wrote:

> How do I write a connection string in VB to use the local database. I have
> something like this, but it does not work. I do not want to use the local
> computer name. I want to make it generic so that the application can run o
n
> any computer.
> Thanks|||Try,
-- WA
...
oConn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);Initial
Catalog=Northwind;Integrated Security=SSPI"
...
-- MA
...
oConn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);Initial
Catalog=Northwind; User ID=MyUsername; Password=MyPassword;"
...
AMB
"Emma" wrote:

> How do I write a connection string in VB to use the local database. I have
> something like this, but it does not work. I do not want to use the local
> computer name. I want to make it generic so that the application can run o
n
> any computer.
> Thanks