[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.axapta.programming

Database locks

frango

2/8/2006 10:38:00 PM

Hi,

Suppose user 1 executes the following job :

MyTable1 myTable1;
;

ttsbegin;
select firstonly forupdate myTable1 where myTable1.Field1 == 1;
pause;
ttscommit;

While user 1 is paused, user 2 starts the following job :

MyTable1 myTable1;
;

ttsbegin;
select firstonly forupdate myTable1 where myTable1.Field1 == 2;
pause;
ttscommit;

User 2's session hangs (before reaching the pause), while he tries to access
to ANOTHER record than user 1 !

On a third session (with SysSQLBlockingMSSQL form), we can see indeed that
user 1 is locking the table (index key lock), and user 2 is waiting.

I had expected that only the record selected by user 1 would be locked
instead of the whole table. Is there a way to prevent this?

Thank you in advance,

PS : I know it is forbidden to ask user intervention within a transaction -
I used pause for testing purposes.
4 Answers

Mike Frank

2/9/2006 7:49:00 AM

0

Just a suggestion, I'm not a SQL guru.

Since an index key lock was used, try to select the record by the index field.

I once experimented with this and found, that when I did selects on an unique index in the described
situation, only this one record was locked. To get the record by the Field1 value, find first the
index value for this record and select the record forupdate by this index value.

As I said above, this is just a suggestion.

Mike

Necmi Göcek

2/9/2006 8:28:00 AM

0

I try your sample on custTable,

Session 1
static void N_LockTest1(Args _args)
{
CustTable ct;
;
ttsbegin;
select firstonly forupdate ct where ct.AccountNum == "00001";
pause;
ttscommit;

}

Session 2
static void LockTest2(Args _args)
{
CustTable ct;
;
ttsbegin;
select firstonly forupdate ct where ct.AccountNum == "00002";
pause;
ttscommit;

}

Session2 runs properly(without hang). While Session1 are still running I
look at SQL Servers Locks / Objects.

ProcessId LockType Mode
54 PAG IU
54 KEY U
54 TAB IX

As I understand from the Locks/Objest , Session1 has Update Lock on
AccountIdx on CustTable(FirstJob: AccountId = 0001) So Session2 will complete
its job without blocking by Session1.

--
_MIB_


"frango" wrote:

> Hi,
>
> Suppose user 1 executes the following job :
>
> MyTable1 myTable1;
> ;
>
> ttsbegin;
> select firstonly forupdate myTable1 where myTable1.Field1 == 1;
> pause;
> ttscommit;
>
> While user 1 is paused, user 2 starts the following job :
>
> MyTable1 myTable1;
> ;
>
> ttsbegin;
> select firstonly forupdate myTable1 where myTable1.Field1 == 2;
> pause;
> ttscommit;
>
> User 2's session hangs (before reaching the pause), while he tries to access
> to ANOTHER record than user 1 !
>
> On a third session (with SysSQLBlockingMSSQL form), we can see indeed that
> user 1 is locking the table (index key lock), and user 2 is waiting.
>
> I had expected that only the record selected by user 1 would be locked
> instead of the whole table. Is there a way to prevent this?
>
> Thank you in advance,
>
> PS : I know it is forbidden to ask user intervention within a transaction -
> I used pause for testing purposes.

Mike Frank

2/9/2006 12:57:00 PM

0

> select firstonly forupdate ct where ct.AccountNum == "00002";

That's exactly, what I was proposing in my previous post. You do the selection on the primary key
field for this table. So only this one record gets locked.

Mike

Luegisdorf

2/9/2006 3:50:00 PM

0

a further hint:

If you really need to select forupdate on a 'simple' field you should
activate RecId index. That way SQL-Server locks only the selected record too.

But of course if you can select with a/some existing unique Key field(s)
that would make more sense ;-)

Best regards
Patrick


"Mike Frank" wrote:

> > select firstonly forupdate ct where ct.AccountNum == "00002";
>
> That's exactly, what I was proposing in my previous post. You do the selection on the primary key
> field for this table. So only this one record gets locked.
>
> Mike
>