Re: database collision?

> hi there. i am developing a database app to manage dynamic sites. in a > nutshell, i have an item table (to store all the content) and a > permission > table (to register who's allowed to edit/view specific items). > > now, when creating a new item, i do the following things: > > - determine a new permission id (which is the permission table primary > key, > kinda "SELECT MAX(id) FROM permission_table" and then increase the > result by > one. i don't use AUTO_INCREMENT columns on purpose.) > - create an entry in the permission table > - create an entry in the item table, including the permission id as > relational attribute > > now, my question is: since there may be multiple php processes running, > if > two users simultaneously create an item and post it at the same moment - > couldn't it happen that the process of user#1 has already determined the > permission id, while user#2 determines the SAME id, creates the entry > and > user#1 will get an error because the item id was already taken in the > meantime? what can i do to avoid such security/integrity holes? > this is db-dependend - I think you use mysql (because of the AUTO_INCREMENT) from the mysql-docu: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#LOCK_TABLES ---cut--- MySQL doesn't support a transaction environment, so you must use LOCK TABES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown below requires LOCK TABLES in order to execute safely. ---cut--- so why not use AUTO_INCREMENT? afaik in ORACLE there's a "select for update" witty -- Sent through GMX FreeMail - http://www.gmx.net