MySQL Forums
Forum List  »  InnoDB

Re: Creating sort index cause shared lock
Posted by: Hansen Ng
Date: May 16, 2016 09:48PM

The following SQL statements does not have "for update" but S locks are found when the SQL is run.

TRANSACTION 125237232, ACTIVE 6 sec starting index read
mysql tables in use 5, locked 5
1715 lock struct(s), heap size 210472, 33507 row lock(s)
MySQL thread id 3374206, OS thread handle 0x7f699e75b700, query id 118652327 testdb x.x.x.x user1 Creating sort index
SET @var_tmp_start_date =
select CASE WHEN released.rid_sr IS NULL THEN p.period_end_date ELSE DATE_ADD(released.approval_date, INTERVAL 1 MINUTE) END as period_end_date
from sr p
left join ad_project pro on (p.rid_project = pro.id_project)
left join sr_details pd on (p.id_sr = pd.rid_sr)
inner join sr_server ser on (pd.rid_server = ser.id_server)
LEFT JOIN sr_approval_flow released on (p.id_sr = released.rid_sr and released.rid_workflow_detail IN (8258, 8368))
WHERE p.record_status = 'A' and pd.record_status = 'A' and pd.rid_server is not null
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2218 page no 783 n bits 144 index `PRIMARY` of table `pssap001`.`sr` trx id 125237232 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
0: len 8; hex 80000000000b9ddc; asc ;;
1: len 6; hex 00000775d810; asc u ;;
2: len 7; hex 4d000000052d14; asc M - ;;

Options: ReplyQuote

Written By
Re: Creating sort index cause shared lock
May 16, 2016 09:48PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.