php - Multiple browser connections getting the same DB object -

running 2 concurrent scripts want access database sequentially each's autoincrement primary keys sequential. i.e. running them both (instance , instance b) @ time lead

1-a, 2-a, 3-a, 1-b, 2-b, 3-b 

however, get

1-a, 1-b, 2-a, 2-b ,3-a ,3-b 

after transactions , locking tables didn't work have expected did deeper looking , appears both scripts (even when run in different browsers) getting same connection. both in same session 1 not block other. there can force them different connections (changing mysqli or pdo not option existing system)?

chrome:

object(db_mysql)#10 (26) {   ["phptype"]=>   string(5) "mysql" ... 

firefox

object(db_mysql)#10 (26) {   ["phptype"]=>   string(5) "mysql" ... 

db creation done dsn string below. (there other logic in there)

mysql://root:@127.0.0.1/xxx?new_link=true 

queries run:

lock tables xxxx write set autocommit=0 transation start >>insert 1 >>insert 2 >>insert 3 commit unlock tables 

two tabs refreshed same time: tab 1:

  [0]=>   int(56335766)   [1]=>   int(56335768)   [2]=>   int(56335770) 

tab 2:

array(3) {   [0]=>   int(56335765)   [1]=>   int(56335767)   [2]=>   int(56335769) } 

as data shows, despite having tables locked data interwoven.

q: why isn't locking working?

a: based on update question, showing sequence of sql statements:

 lock tables xxx write    start transaction 

the problem start transaction releasing lock.

this behavior documented in mysql reference manua

reference: http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html

rules lock release

if session begins transaction (for example, start transaction), implicit unlock tables performed, causes existing locks released. (for additional information interaction between table locking , transactions, see section 13.3.5.1, “interaction of table locking , transactions”.

---

original answer original question below

q: there can force them different connections?

a: think may barking wrong tree there. it's each using distinct database connection.

---

the values show 2-a, 2-b, 3-a appear string values.

if select order by on string values, rows returned in sequence show them being returned in. without order by, mysql free return rows in order wants to. if mysql using index return rows, , not performing sort operation, (almost always) observe rows returned in same order rows appear in index.

if relying on auto_increment property of column, won't assign same value 2 different rows (unless using myisam , auto_increment column not leading column in index...)

aside that, there's no way 2 rows going same auto_increment value.

---

or, you're getting in database table more

  auto_id  byclient   -------  --------     14156      14157  b     14158      14159  b     14160      14161  b 

the auto_increment property on column on table, not "per database connection". 2 (or more) concurrent sessions inserting rows can perform operations "interleave" values. don't have use same database connection.

to prevent other database connections performing inserts concurrently table, you'd need implement mechanism accomplish that. @ database level, can use concurrency killing table locks. held until released, or duration of database connection. if you're disconnecting database, locks gone.

so, won't you, if 2 separate browsers intermittently calling same php page. , if did have way separate database connections each browser session.

i suspect you're churning database connections, establishing new database connection each execution of php script.

(i think may barking wrong tree using same database connection. you'd same behavior multiple database connections.)

followup

i think asking wrong question. why need have auto_increment id values "in order"? if that's actual requirement, auto_increment not going suitable solution problem. (seems may trying auto_increment perform task not suited to. in configurations, aren't guaranteed auto_increment values ascending in order rows inserted.)

rather requiring auto_increment values contiguous (is requirement), i'd backing up, , thinking instead real requirement is. perhaps identifying "session" inserted each row (having each session storing value in column in table, along ascending integer value assigned client, session. , don't have care auto_increment value assigned:

 auto_id  session_id  seq_     -------  ----------  ----     75322  3e45bf4        1     75323  3e45bf4        2     75327  3e45bf4        3     75325  3e45bf4        4     75324  f51113e        1     75326  f51113e        2     75322  f51113e        3