transaction and unreadble tables
-
I have 2 store procs: procA and procB, procA is in database dbA and proB is in dbB.
procB is called from procA after some sql stat are performed.
procB has sql statement (update and insert) for all table in dbB, and this sql statements are inside a Transaction.
in dbA there is a table (TLog) which log the table processed from procB (it has only a record with a col - named table_name). I mean every time procB runs and execute update and insert for every table, then its name is recovered into TLog:
I mean:
create procA
as
sqlcode
exec procB
sqlcode
go
--end of procA
procB is:
create procB
as
begin tran
update(T1)
insert(T1)
update(LogT) with name of T1
update(T2)
insert(T2)
update(LogT) with name of T2
........
update(Tn)
insert(Tn)
update(LogT) with name of Tn
commit tran
go
T1...Tn are dbB tables. LogT log table in dbA.
If I run the procB without Transaction, I can see and read LogT, If I run procB with Transaction I have a problem reading table from a vb.net client (datareader).
Do you how to get free LogT during progB runs
Thank for any suggestion
-
I forgot to write the that there is also a cursor that read what table must be processed on dbB procB:
I mean:
create procA
as
sqlcode
exec procB
sqlcode
go
--end of procA
procB is:
create procB
as
begin tran
declare cursor curTblesName
update(T1)
insert(T1)
update(LogT) with name of T1
update(T2)
insert(T2)
update(LogT) with name of T2
........
update(Tn)
insert(Tn)
update(LogT) with name of Tn
end cursor
commit tran
go
T1...Tn are dbB tables. LogT log table in dbA.
If I run the procB without Transaction, I can see and read LogT, If I run procB with Transaction I have a problem reading table from a vb.net client (datareader).
Do you how to get free LogT during progB runs
Thank for any suggestion
-
a solution may be:
variable or temp tables?
-
Let's simplify your problem with this test case. At the end, you will know how to determine blocks and locks that exists in the SQL Server instance.
Open SQL Query Analyzer and run these statements:
use tempdb
go
create table FOO
( FooId integer not null
, FooData varchar(255) not null
, constraint FOO_P primary key (FooId)
)
go
insert into Foo
(FooId ,FooData )
values (1 , 'Starting Data'
go
select 'TempDb DBid is', dbid()
, 'Foo ObjectId is', object_id
Record the ouptput which are 2 numbers.
Now open another QA window and run the below SQL. The output will include the sysprocess identifier (SpId) of your update statement. Write down the SpId.
use tempdb
go
select @@spid, ' is spid of update'
go
BEGIN TRANSACTION
update Foo
set FooData = 'My first update'
where FooId = 1
-- Do NOT perform a commit
Now open another QA window and run the below SQL. The output will include the SpId of the select, which you should also record.
use tempdb
go
select @@spid, ' is spid of select'
go
In the same QA window, run:
select * from Foo
The execution indicator will show but you will get no results (appears to hang)
Open another QA window, replace --SpidOfSelect-- with the previous spid of the select and run
exec sp_who2 --SpidOfSelect--
In the output, look at the column named 'BLK' which should contain the value
for the spid of the update.
Finally, open another QA window and run
sp_lock --SpidOfUpdate
You should see multiple rows that indicate the locks held by the spid, and the dbid and objectId that are being locked along with the lock types. The dbid and objectId should match the previous values. The lock types are:
DB = Database
FIL = File
IDX = Index
PG = PAGE
KEY = Key
TAB = Table
EXT = Extent
RID = Row identifier
Has this reproduced you problem ?
If so, open Book Online and under index enter "locking, blocks" (without the ")
Read the topic of "Understanding and Avoiding Blocking"
SQL = Scarcely Qualifies as a Language
-
Another thought:
Is table (TLog) supposed to be the status of the processing by the procedures ?
If so, try adding a isolation level hint to allows selecting of rows that are being updated but that have not yet been committed.
select
from Tlog WITH ( READUNCOMMITTED )
From Books OnLine:
READUNCOMMITTED
Specifies that dirty reads are allowed. This means that no shared locks are issued and no exclusive locks are honored. Allowing dirty reads can result in higher concurrency, but at the cost of lower consistency. If READUNCOMMITTED is specified, it is possible to read an uncommitted transaction or to read a set of pages rolled back in the middle of the read; therefore, error messages may result.
If you receive the error message 601 when READUNCOMMITTED is specified, resolve it as you would a deadlock error (1205), and retry your statement.
SQL = Scarcely Qualifies as a Language
-
Yes, I thought the same...
READUNCOMMITTED
It should resolve my problem. 'll try....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply