transaction/lock problem 1

[abbath8]

Hi,

I have a problem with transactions. There is an SQL 2000 server machine and there is 2 clients. If one client do the following:
BEGIN TRANSACTION
INSERT INTO any_table (field1) values ('any value')

the another client cannot query the table until the transaction is committed (or rollbacked). Why? What kind of feature is this, or any settings?

(Sorry if it is a trivial thing, but I cannot understand...)

A8

 

[SQLDenis]

That is normal behavior to enforce ACID
What is your isolation level?
If you want the selects to have access you can add with (nolock) to your queries but be carefull because you will get 'dirty' reads
example
select * from publishers with (nolock) where id =1

you can also set the transaction isolation level to read uncommited

look up locking in Books On Line

http://sqlservercode.blogspot.com/

 

[abbath8]

Thank You.

 

[abbath8]

One more question. It is not possible in this case to select the committed records from the table? Why can't I select the committed records?

 

[SQLDenis]

I they are commited you can but if the transaction is not completed you can not

Also make your transactions as small as possible and use the same order

Example
Transaction 1
update Table A
Update Table B

Transaction 2
Update Table B
update Table A

In this case you might get a deadlock because tran 1 has a lock on TableA but needs Table B
Tran 2 has a lock on table B but needs Table A
None of these transactions can get the lock on the second table because it's locked by the other transaction and a deadlock will occur with one of the transaction being the victim

An excellen t book that talks a lot about locking is Inside SQL server 2000

http://www.amazon.com/exec/obidos/A...2032?creative=327641&camp=14573&link_code=as1

http://sqlservercode.blogspot.com/