Row level locking in MYSQL | MySQL Tutorial For Beginners

[@sarikagarg8185]

You did not show the method about syntex used to lock row

[@guardrover]

In his setup, the locks are enabled by default and until committed, other sessions can't read that row.

[@irshadahmad123456]

incomplete, never showed how lock was applied and how the behaviour is different from non locking mechanism

[@59sharmanalin]

why can other session read the data if its locked in another transaction by exclusive lock?

[@ExecuteonCommand]

Read operation is harmless so that’s allowed in most cases

[@gkrv6726]

Hi , Let me explain my scenario , I don't know why table lock is happening 1. There is only one user / One session 2. Stored Procedure is executed. With in the stored procedure , multiple insert happend in table T_TRANS. And finally one Delete is happening for the same table T_TRANS, here only am facing issue, This delete query taking too much time SOMETIME... Other time its working fine 3. Why some time only its taking too much time .? is there any lock will come within the same session? Please help

[@ExecuteonCommand]

You may want to check for existence of any foreign constraints where T_TRANS is a child table. Do you disable constraints while performing delete?

[@gkrv6726]

​@@ExecuteonCommand No. Its not child table and there is no foreign key constraints. After deep analysis i found the root cause, there is one query taking 20 minutes and next another query trying to delete the same table . But note that long running query successfully completed. So my question is , will long running query lock the tables even after successful completion?

[@ExecuteonCommand]

@@gkrv6726 It should release locks after it commits.

[@59sharmanalin]

I must say locking is so confusing in relational database

[@vivekmisal3545]

its feel boar lec. like professor teaching in college