What will be the order of statement execution in this use case? Say I execute select * from users at t1 which gets finised at t3. At t2(in between t1 and t3) update comes for some of the records in users table say 
update users set address='xyz' where name = 'abc'. Will select statement acquire shared lock on all rows in advance or it will acquire for each row 
independently when it starts to read that row ?

My understanding is whosoever(among select or update) acquires the shared or exclusive lock earlier it will execute it other will wait ?

I am using Mysql innodb .
It depends on the transaction_isolation.

It depends on whether the SELECT includes FOR UPDATE or IN SHARE MODE.

The SELECT is inside a transaction.  Normally, the SELECT will see the table as of the instant the transaction started.  Other queries may update/insert/delete rows that the SELECT is reaching for, but SELECT won't see them.  This puts a burden on InnoDB to keep old/new copies of row around for cases like this.

If you say FOR UPDATE, the other transaction will probably be delayed, awaiting the "intention to change" lock to be released.

Or it might lead to a deadlock.  InnoDB is good at seeing if the situation can't be resolved by waiting.

Perhaps you would like to discuss a specific case?