Lock and unlock the table

Hi,
How would I lock a table so that the access calls from other applications are put in "wait" by sql server till I unlock ?

How would I do this ?

Thanks,
Fahad

TABLOCK table hint

See SQL Server 2005 Books Online topic Table Hint (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

Possibly SERIALIZABLE

See SQL Server 2005 Books Online topic

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms173763.aspx

|||Could you please explain your problem? Locking entire table hurts concurrency and performance. Maybe there are other ways to do this.|||MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.|||

Tom Phillips wrote:

MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

I need a synchronization between two processes which are accessing a table, both are initiated with a second of difference, one prepares data for other and other consumes it. I want 2nd one to wait till 1st one is done. I dont wanna spend hours to do mutexes and semaphores things, I wonder if I could utilize this cool and time-saving feature of MSSQL, Performance is not a problem. These processes will run at midnight.

Thankyou|||There is no "good" way to do what you are looking for. The best you could do would be to start a transaction on the first process and when it is done, COMMIT.

The 2nd process would have to be set to:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Also, you would have to make sure the 2nd process doesn't start before the 1st opens the transaction. I would schedule them 5 mins apart.

Lock and unlock the table

Hi,
How would I lock a table so that the access calls from other applications are put in "wait" by sql server till I unlock ?

How would I do this ?

Thanks,
Fahad

TABLOCK table hint

See SQL Server 2005 Books Online topic Table Hint (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

Possibly SERIALIZABLE

See SQL Server 2005 Books Online topic

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms173763.aspx

|||Could you please explain your problem? Locking entire table hurts concurrency and performance. Maybe there are other ways to do this.|||MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

|||

Tom Phillips wrote:

MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

I need a synchronization between two processes which are accessing a table, both are initiated with a second of difference, one prepares data for other and other consumes it. I want 2nd one to wait till 1st one is done. I dont wanna spend hours to do mutexes and semaphores things, I wonder if I could utilize this cool and time-saving feature of MSSQL, Performance is not a problem. These processes will run at midnight.

Thankyou|||There is no "good" way to do what you are looking for. The best you could do would be to start a transaction on the first process and when it is done, COMMIT.

The 2nd process would have to be set to:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Also, you would have to make sure the 2nd process doesn't start before the 1st opens the transaction. I would schedule them 5 mins apart.

Lock and unlock the table

Hi,
How would I lock a table so that the access calls from other applications are put in "wait" by sql server till I unlock ?

How would I do this ?

Thanks,
Fahad

TABLOCK table hint

See SQL Server 2005 Books Online topic Table Hint (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

Possibly SERIALIZABLE

See SQL Server 2005 Books Online topic

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

http://msdn2.microsoft.com/en-US/library/ms173763.aspx

|||Could you please explain your problem? Locking entire table hurts concurrency and performance. Maybe there are other ways to do this.|||MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

|||

Tom Phillips wrote:

MS SQL, and most "server" based databases, don't do that unless they absoulutely need too, and it is done by the engine, not the user.

What is it you are trying to do and why?

If you just want to make sure someone doesn't read partially updated data, use a transaction.

I need a synchronization between two processes which are accessing a table, both are initiated with a second of difference, one prepares data for other and other consumes it. I want 2nd one to wait till 1st one is done. I dont wanna spend hours to do mutexes and semaphores things, I wonder if I could utilize this cool and time-saving feature of MSSQL, Performance is not a problem. These processes will run at midnight.

Thankyou|||There is no "good" way to do what you are looking for. The best you could do would be to start a transaction on the first process and when it is done, COMMIT.

The 2nd process would have to be set to:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Also, you would have to make sure the 2nd process doesn't start before the 1st opens the transaction. I would schedule them 5 mins apart.

Lock a table until Package execution finishes

Hello,
I need to lock a table in startup of my package so that access calls from other applications are put on "wait" by sql server until I unlock.

Any idea how would I do it ?
Or is it possible or not ?

Thanks,
FahadIf you use MS Access database I think it will be locked automatically.|||You may want to jump over to the Transact-SQL forum to get help on this. I would imagine you can issue a table lock command in an Execute SQL task, and then when done processing release that lock in another Execute SQL task.

Local variables in stored procedures

Hi!
I'm using SQL Server 7.0 and it is a multiuser application.
My problem is that sometimes during parallel calls to a stored procedure it
produces wrong results. It takes between 5 to 20 sec to execute the stored
procedure.
To be able to know why the result sometimes is wrong I will log some values
from local variables.
My guess is that my part-result from some lookups are overwritten.
But until I get enough log-results to analyse I have a couple of questions:
- Are local variables overwritten by another call to the same procedure?
- Can I save part-result in a more secure way? I still want to have the
possibilty to call the procedure in a parallel way.
- As a last option. Is there a simple way to forbid parallel calls to a
procedure? (I have read some about "set transaction isolation level
serializable" but I'm afraid it has too large impact on other calls that
questions the same tables that are in use in the stored procedure)
Hope someone has a godd answer to give.
Best regards
SvenneSvenne
Can you show us your SP's call and how do you handle local variables within
SP?
If you have SELECT/UPDATE/DELETE/INSERT operations within a SP try to wrap
it into BEGIN TRAN ...COMMIT commands
"Svenne" <sasodergren@.hotmail.com> wrote in message
news:%23Y0MMBp7FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I'm using SQL Server 7.0 and it is a multiuser application.
> My problem is that sometimes during parallel calls to a stored procedure
> it produces wrong results. It takes between 5 to 20 sec to execute the
> stored procedure.
> To be able to know why the result sometimes is wrong I will log some
> values from local variables.
> My guess is that my part-result from some lookups are overwritten.
> But until I get enough log-results to analyse I have a couple of
> questions:
> - Are local variables overwritten by another call to the same procedure?
> - Can I save part-result in a more secure way? I still want to have the
> possibilty to call the procedure in a parallel way.
> - As a last option. Is there a simple way to forbid parallel calls to a
> procedure? (I have read some about "set transaction isolation level
> serializable" but I'm afraid it has too large impact on other calls that
> questions the same tables that are in use in the stored procedure)
> Hope someone has a godd answer to give.
> Best regards
> Svenne
>