effects of aquiring exclusive lock on subsequent reads inside same TX?

Nikita Tovstoles · *nix forums beginner Joined: 20 Jul 2006 Posts: 1

Hi,

I'm running into lost of DB deadlocks would really appreciate answers to
the questions below which will help me diagnose the problem. I am
running MySQL 5 InnoDB, SERIALIZABLE mode.

Let's say we have:

1.TX1 START //assume autocommit is off
2.TX1 read on TableA
3.TX1 update on TableA
4.TX1 read on TableB
5.TX1 COMMIT

Is the following correct?
-In step2, TX1 obtains a SHARED lock. Is it applied to all rows in
TableA or only those returned by select statement?
-In step3, TX1 obtains an EXCLUSIVE lock. Does that lock out all of
TableA or only those rows that are being updated?
-In step4, does TX1 apply SHARED or EXCLUSIVE lock? is the lock applied
to all of TableB or only to rows returned by the select statement?

In general what is the effect (w/r/t locking) on selects performed after
a write operation within the same TX?

thanks a lot!

-nikita

Google