[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sapdb-general
Subject:    RE: Multiple share lock one select
From:       "Zabach, Elke" <elke.zabach () sap ! com>
Date:       2004-04-29 12:32:25
Message-ID: 235EE6AC8AE9264CBF763FC6749E270105F632 () dewdfe22 ! wdf ! sap ! corp
[Download RAW message or body]

Philippe Chaléat wrote:
> 
> Ok. Sorry, my previous mails were not clear enough.
> 
> Here is a concrete case.
> 
> I 've two working sessions, one with transaction  27225 and 
> one with 27222.
> 
> If I disable dead lock detection and have a look at LOCKS-HOLDER and 
> LOCK-REQUESTOR, I can see something like this :
> 
> HOLDER
> 	TRANSCOUNT	TABLENAME	LOCKMODE	
>                27225	SV_FRACT	row_share	
>                27222	SV_COMMANDE	row_exclusive	
>                27222	SV_MOUVEMENT	row_exclusive	
> 
> 
> REQUESTOR	
> 
> 	TRANSCOUNT	TABLENAME	REQMODE	
>                27225	SV_COMMANDE	row_exclusive	
>                27222	SV_FRACT	row_exclusive	
> 
> 
> 
> I think I understand how row_exclusive are put, but it's less 
> clear for 
> shared lock. In which case do a statement need to put simultanatly 
> multiple share locks (that the reason of my deadlock). All exclusive 
> lock are put with simple statement (no jointure, no sub-select, only 
> "update mytable where my_primary_key=?"). So, I can't understand why 
> session 27225 still have a shared lock when requesting an 
> exclusive lock.
> 
> I also have some cases where the deadlock appears with only 
> share lock 
> for one of the two transaction (perhaps because of a subselect, or 
> jointures ?... ).
> 
> If you have explanation about "when" shared lock requested, put, and 
> release, I think it will help me a lot.
> 
> PS : where are working in a rather "higly current" environnement.
> 

Please tell us which isolation level you are using.
Please tell us something about your statements you are doing. That
update mentioned is not the only one you have.

Why don't you understand that transaction 27225
may have a share lock on a single row in table SV_FRACT if it
has an exclusive lock on another table (SV_COMMANDE) ?

           27225	SV_FRACT	row_share	
           27225	SV_COMMANDE	row_exclusive	

Locks are held until the end of transaction. Therefore 
two statements on different tables may cause this, or (with 
some isolation level) even an 
UPDATE SV_COMMANDE SET ... WHERE ... (SELECT .. FROM SV_FRACT..)
may cause this. Everything depends on the statement and on the isolation level.

Please read http://dev.mysql.com/doc/maxdb/en/5e/eba664440a11d3a98200a0c9449261/frameset.htm
and further down (isolation level for example) and check by your own first.

Elke
SAP Labs Berlin

> Philippe
> 
> Zabach, Elke a écrit :
> 
> >Philippe Chaléat wrote:
> >
> >  
> >
> >>Hello,
> >>
> >>I've some deadlock problem due to select statement. A 
> >>read-only session 
> >>causes a deadlock with a simple select. I guess it's because 
> >>my select 
> >>has sub-select and the share lock put when reading the 
> sub-select is 
> >>kept when putting the shared lock for the main select. Is 
> >>this possible 
> >>? Any solution to avoid this ?
> >>
> >>    
> >>
> >
> >One single task can never cause a deadlock.
> >Deadlock always needs at least two transactions reading/writing
> >the same tables/rows.
> >
> >Of course the share lock is held for the whole select/up to 
> the transaction end
> >(we do not know the isolation level you are working with).
> >
> >How do you know that it is a deadlock? You checked LOCKSTATISTICS or
> >is it just a guess?
> >Please check who is working with your database, too.
> >
> >Elke
> >SAP Labs Berlin
> >
> >  
> >
> >>Thanks
> >>
> >>Philippe
> >>
> >>-- 
> >>MaxDB Discussion Mailing List
> >>For list archives: http://lists.mysql.com/maxdb
> >>To unsubscribe:    
> >>http://lists.mysql.com/maxdb?unsub=elke.zabach@sap.com
> >>
> >>    
> >>
> >
> >  
> >
> 
> 

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=sapdb-general@progressive-comp.com


[prev in list] [next in list] [prev in thread] [next in thread]